8

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;"
  • The second option seems good but why don't you use NFS? Perhaps there is some small interruption that is not well handled by sshfs. 320Gb is quite a big file. – Marco Aug 12 '15 at 12:36
  • 1
    Buy a bigger disk? 1Tb costs next to nothing these days. – Colin 't Hart Aug 12 '15 at 17:05
  • Turns out NFS is just as unreliable as SSHFS for transferring this much data. Updating question. – kontextify Aug 13 '15 at 11:05
7

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. =)

  • The "dump and restore in one step" probably won't work because of differences between the PostGIS versions, but running the dump remotely from the receiving server and restoring from it worked! This is clearly better than using network filesystems. Thanks, I now have a functioning database! – kontextify Aug 14 '15 at 8:47
  • Is that a performative process? It looks to me that will take some days to complete. Then will pgdump create a dump file with ~300GB if I don't use a server to server remote backup? – deFreitas Mar 8 '18 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.