PostgreSQL – Moving Large PostgreSQL/PostGIS Database

migrationpostgispostgresql

I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1).

The upgrade process is well-documented. The problem is I don't have enough space on server1 to dump the file there, checksum it, then copy it to server2 and verify sums. I tried:

  • Piping the dump from server1 to server2 using nc.
  • Writing a dump file directly to a server2 filesystem which is mounted on server1 using sshfs.

Both times the dump file appears to have been corrupted. pg_restore broke in different places with errors like this:

pg_restore: [compress_io] could not uncompress data: incorrect data check

Can anyone suggest a better way to get this move and upgrade done?

UPDATE: Tried NFS (and gave SSHFS another try). It's clear that these remote filesystems can't reliably transfer this much data. Blocks are visibly missing from the resulting SQL file, causing syntax errors like this during import:

ERROR:  invalid input syntax for integer: "8266UPDATE spatial_ref_sys o set auth_name = n.auth_name, auth_srid = n.auth_srid, srtext = n.srtext, proj4text = n.proj4text FROM _pgis_restore_spatial_ref_sys n WHERE o.srid = n.srid;"

Best Answer

I would recommend dumping the 9.1 database from your new 9.3 server like this:

pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump

I recommend using the 9.3 pg_dump as pg_dump is always backwards compatible, but not forward compatible. In other words, the newer pg_dump will take care of any syntax changes that the new server requires that the older utility doesn't know about.

Be sure to make sure your pg_hba.conf and listen_addresses in postgresql.conf are set up to allow you to remotely connect and dump appropriately as well.

If you wanted to try a dump and restore in one step, you could try this as well:

pg_dump -h remotehost -U remoteuser remotedbname | psql -U localuser localdbname

Hope that helps. =)

Related Question