5

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?

Mohamed Taher Alrefaie
  • 15,698
  • 9
  • 48
  • 66
  • Possible duplicate: http://stackoverflow.com/questions/9627095/exclude-function-definitions-when-dumping-a-postgresql-database – Houari Mar 04 '14 at 18:34

1 Answers1

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
Mike T
  • 41,085
  • 18
  • 152
  • 203
  • 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: https://www.apt-browse.org/browse/debian/wheezy/main/i386/postgresql-9.1-postgis/1.5.3-2/file/usr/share/postgresql-9.1-postgis/utils/postgis_restore.pl – modulitos Nov 13 '17 at 15:47
  • Ah, it looks like I found it here: https://github.com/postgis/postgis/blob/svn-2.1/utils/postgis_restore.pl.in But it looks like that some variables would have to be injected before making that script executable – modulitos Nov 13 '17 at 16:09
  • @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. – Mike T Nov 13 '17 at 21:46
  • 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. – modulitos Nov 14 '17 at 11:07
  • 2
    @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. – Mike T Nov 14 '17 at 21:00