What is the key to pg_restore? We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore.

We are trying to restore from a 17GB .backup file. We have tried several permutations of the pg_restore command, including:


No existing target database, include the --create flag.
Connect to existing target database, include --clean and --create flags (per these Stackexchange answers).
Specifying --host=localhost (per 68 here).


Several attempts resulted in the rows of the restore being written to the nohup.out file. 

A few permutations we've tried:

nohup pg_restore --host=localhost --username=postgres --password --role=postgres --dbname=dbname_to_restore path/to/data.backup &gt; nohup.out 2&gt; err.out

pg_restore --username "postgres" --password --role "postgres" --create --clean "/path/to/data.backup"


When we nohup we are getting a few issues in our err.out file, such as 

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 187; 1259 17762 TABLE ping arcgis
pg_restore: [archiver (db)] could not execute query: ERROR:  type "geography" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "arcgis" does not exist


What are we missing? What are the bare minimum flags etc. to load the data into the PostgreSQL database? Are there some environmental considerations we need to address? Why isn't the data being loaded into the db?
In cases like this and the size of the DB (>17 Gb) I suggest based on the official documentation for version 9.2 the following:


do a pgdump_all


pg_dumpall [connection-option...] [option...]

pg_dumpall &gt; databackup.out


Restore with psql. I have found out a better execution and stability


psql -f databackup.out postgres


Take extreme precaution with the file encoding. Specially if you are doing migrations among different OS and specially if you are uploading to a cloud RDBMS provider.