What is the key to pg_restore?

Question

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 > nohup.out 2> 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?

Answer 1

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:

1. do a pgdump_all

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

pg_dumpall > databackup.out

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

psql -f databackup.out postgres

3. 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.

Answer 2

We figured it out.

First thing - we abandoned the .backup file method and dumped the DB as a .sql file. Then we used the psql CL tool to restore the database.

Our final hangup had to do with encoding - exporting from a Windows install of PGsql (possibly without specifying the encoding - we can't remember) put out a .sql file with Little-endian UTF-16 Unicode text.... (We checked it on our Linux command line with file x.sql.)

We converted the encoding with the command:

iconv -f utf-16 -t utf-8 x.sql > x8.sql; 

then the psql -U postgres db_name -f x8.sql command then worked. - https://dba.stackexchange.com/users/112237