Dump or restore Postgresql databases without Postg

问题:

I want to dump 4 databases from postgresql 8.4 to migrate to Postgresql 9.1.

I use PostGis on the old Postgresql 8.4 with PgRouting so each database has around 1000 functions. Each time I export all databases, all functions are written in the dump. When I restore the backup file, I get some conflicts when I create an extension of postgis or pgrouting on Postgresql 9.1

Is there anyway to dump databases on 8.4 (create dbs, create schemas, create tables and data) without exporting fucntions as well? Or is there anyway to restore the databases on 9.1 without creating the functions on the backup file?

回答1:

Databases with PostGIS need to follow a specific update procedure. You will need to follow a "hard upgrade" procedure documented here.

On your 8.4 database:

pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

And on the 9.1 database, maybe start with these (if you haven't already done so):

createdb [yourdatabase]
psql -d [yourdatabase] -c "CREATE EXTENSION postgis;"

Then restore the data using a special postgis_restore.pl script:

perl utils/postgis_restore.pl "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt