Dump or restore Postgresql databases without Postgis functions

Question

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?

Possible duplicate: stackoverflow.com/questions/9627095/…

Houari
– Houari

2014-03-04 18:34:43 +00:00
Commented Mar 4, 2014 at 18:34 — Houari
– Houari, Commented Mar 4, 2014 at 18:34

Mike T · Accepted Answer · 2014-03-05 23:25:53Z

8

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

answered Mar 5, 2014 at 23:25

Mike T

44.4k18 gold badges166 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

modulitos Over a year ago

Where can one find the postgis_restore.pl script? I think I found it here, but it would be nice to see it coming from a more official source: apt-browse.org/browse/debian/wheezy/main/i386/…

modulitos Over a year ago

Ah, it looks like I found it here: github.com/postgis/postgis/blob/svn-2.1/utils/… But it looks like that some variables would have to be injected before making that script executable

Mike T Over a year ago

@Lucas the correct script is usually packaged and installed on your system. While you could download an external script, you'd need to be careful in getting the correct version.

modulitos Over a year ago

Thanks for the info, but it would be useful to document where that script is located on the machine. Also some information about how to run that script when using a tool like RDS or Docker would be helpful.

Mike T Over a year ago

@Lucas it depends on how PostgreSQL was configured/packaged/installed. For instance, with the apt-browse link you provided above, it is in /usr/share/postgresql-9.1-postgis/utils, and on a Windows computer I see this in C:\Program Files\PostgreSQL\9.6\utils. You'll need to investigate where this is yourself.

Collectives™ on Stack Overflow

Dump or restore Postgresql databases without Postgis functions

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related