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;"
Improve this question
3
  • 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, 2015 at 12:36
  • 1
    Buy a bigger disk? 1Tb costs next to nothing these days.
    – Colin 't Hart
    Aug 12, 2015 at 17:05
  • Turns out NFS is just as unreliable as SSHFS for transferring this much data. Updating question.
    – kontextify
    Aug 13, 2015 at 11:05

2 Answers 2

Reset to default
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. =)

Improve this answer
2
  • 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, 2015 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, 2018 at 13:56
0

If you need to move huge Geodatabase, I suggest you another approach:

  1. from the new database recreate the original schema from a DDL.
  2. create a remote database wrapper and import remote tables into a temporary schema (eg. old_schema_temp).
  3. move data from remote tables, following your data model structure, into your schema
  4. delete remote database wrapper
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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