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
- Writing a dump file directly to a server2 filesystem which is mounted on server1 using
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;"