Workaround for postgis bug #2485 (often noticed as ST_BandMetaData error during pg_dump)

2485_workaround.txt

- -   PSQL script - - run as postgres - - 

drop table if exists bug2485move;
drop sequence if exists bug2485move_seq;

create sequence bug2485move_seq;

select  r_table_schema as schema, r_table_name as name, 'bug2485___'||nextval('bug2485move_seq')::text as bug2485move into public.bug2485move from raster_columns;

COPY (SELECT 'ALTER TABLE '||schema||'.'||name||' RENAME TO '||bug2485move||';' FROM bug2485move) TO '/var/tmp/before_dump1.sql';

COPY (SELECT 'ALTER TABLE '||schema||'.'||bug2485move||' SET SCHEMA public;' FROM bug2485move) TO '/var/tmp/before_dump2.sql'; 

COPY (SELECT 'ALTER TABLE public.'||bug2485move||' SET SCHEMA '||schema||';' FROM bug2485move) TO '/var/tmp/after_restore1.sql'; 

COPY (SELECT 'ALTER TABLE '||schema||'.'||bug2485move||' RENAME TO '||name||';' FROM bug2485move) TO '/var/tmp/after_restore2.sql';

- - end of PSQL script - - 

# BASH commands to run and instructions

# Workaround for bug 2485 (http://trac.osgeo.org/postgis/ticket/2485)  30/7/2014
# Allows a pg_dump/pg_restore with postgis_raster, e.g. for upgrade  to postgres 9.3/9.4.
# Hopefully useful for anyone experiencing ST_BandMetaData problems during pg_dump. 
# No warranty provided, use this at your own risk. GPL/MIT licensed as you need.
# Graeme Bell <[email protected]> (Norsk Instittut for Skog og Landskap). 

# http://en.wiktionary.org/wiki/if_the_mountain_won't_come_to_Muhammad
# Since we can’t easily change the search path made by pg_dump to include the public schema, 
# Let’s move all our rasters into the public schema before we dump and move back after.
# Then the pg_dump will conveniently include ‘public’ in the search path for raster tables.
# This avoids the st_bandmetadata problems and probably constraint problems too. 
# It only moves rasters that are registered in raster_columns. 

# Instructions for use

# 1. rm /var/tmp/before_dump* /var/tmp/after_restore* before you start.
# 2. Run the SQL script above.
# 3. Run these commands, edit as needed to include your DB settings:

psql dbname —1 -f /var/tmp/before_dump1.sql  . . .
psql dbname —1 -f /var/tmp/before_dump2.sql  . . . 

# Dump only the raster tables, presumably everything else restored ok from your dump?
pg_dump dbname -Fc -b . . . -f dumpfile -t bug2485*

pg_restore -d dbname . . . -f dumpfile

psql dbname —1 -f /var/tmp/after_restore1.sql . . . 
psql dbname —1 -f /var/tmp/after_restore2.sql . . . 

# Done! Once you’re happy, drop table bug2485move.
# It’s possible you may need to re-register the tables in raster_columns afterwards.

# p.s. if you have fast disks + big rasters, try the -Z0 or -Z1 options and -j for pgdump/restore.
#  And if you’re scared, check the script using ‘limit 1’ on the first select statement.