Sul tema dump/restore di un database PostGIS si è espresso anche Paul Ramsey (uno dei padri di PostGIS) in questo interessante articolo. Si sostiene che l'unico modo di garantire facili upgrade è mantenere i propri dati in uno schema differente da public.

Dentro un database PostGIS oltre alle tabelle create dall'utente ci sono alcuni oggetti che hanno bisogno di essere trattati in modo particolare durante un dump/restore, soprattutto se il restore viene effettuato su una versione diversa di PostgreSQL/PostGIS.

Linguaggio PL/pgSQL

Deve essere creato dentro il database, per fare questa operazione sono necessari i privilegi di superutente Postgres. In caso di restore su una versione diversa di PostgreSQL non conviene recuperare il linguaggio dal vecchio dump, ma ripetere la procedura di creazione linguaggio.

Funzioni PostGIS

Le funzioni PostGIS devono essere create all'interno del database, anche in questo caso sono necessari i privilegi di superutente Postgres. In caso di restore su una versione diversa di PostGIS non conviene recuperare le funzioni dal dump, ma ricrearle dal file lwpostgis.sql fornito con PostGIS stesso.

Tabella spatial_ref_sys

Contiene la descrizione dei sistemi di riferimento utilizzabili in PostGIS, viene creata vuota durante l'esecuzione di lwpostgis.sql e poi popolata eseguendo spatial_ref_sys.sql (entrambi gli script vengono forniti con PostGIS). In caso di upgrade di PostGIS conviene creare questa tabella ex-novo.

Tabella geometry_columns

Questa tabella viene creata vuota durante l'esecuzione di lwpostgis.sql, pertanto dal dump conviene recuperare solo il contenuto della tabella e non la sua struttura.

Quindi, in sintesi, questi sono gli oggetti che vanno ricreati e/o recuperati dal dump:

La procedura di dump deve interessare solo i dati utente e deve essere effettuata nell'ordine giusto, in modo che l'integrità referenziale venga rispettata durante il restore, dalla shell di sistema:

pg_dump -U strade -W -h localhost -t users                        strade >  strade.dump
pg_dump -U strade -W -h localhost -t gpxfiles                     strade >> strade.dump
pg_dump -U strade -W -h localhost -t tracks                       strade >> strade.dump
pg_dump -U strade -W -h localhost -t tracksegments                strade >> strade.dump
pg_dump -U strade -W -h localhost -t trackpoints                  strade >> strade.dump
...
pg_dump -U strade -W -h localhost -t geometry_columns --data-only strade >> strade.dump

Per il restore collegarsi al DB come utente privilegiato per ricreare il database, l'utente e le strutture PostGIS:

CREATE USER "strade";
ALTER USER "strade" PASSWORD '*****';
CREATE DATABASE strade;
SELECT usename, usesysid FROM pg_user WHERE usename = 'strade';
--  usename | usesysid
--  ---------+----------
--  strade  |    12345
UPDATE pg_database SET datdba=12345 WHERE datname='strade';
\CONNECT strade
CREATE LANGUAGE plpgsql;
\i /usr/share/postgresql-8.1-postgis/lwpostgis.sql
\i /usr/share/postgresql-8.1-postgis/spatial_ref_sys.sql
ALTER TABLE geometry_columns OWNER TO strade;
ALTER TABLE spatial_ref_sys OWNER TO strade;

Quindi come utente non privilegiato effettuare il restore dalla shell di sistema:

psql -U strade -W strade < strade.dump