Postgresql – the key to pg_restore

pg-dumppg-restorepostgresql

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?

Best Answer

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

Related Solutions

Postgresql – pg_restore: [archiver (db)] could not execute query: ERROR: schema “public” already exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Postgresql – pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

I've found it !

In Windows Service, i found that it was "adminpostgres" who was running the pgAgent service.

I just had to edit the pgpass.conf in c:/user/adminpostgres/AppData/postgresql with my old config to make it works !

Best Answer

Related Solutions

Postgresql – pg_restore: [archiver (db)] could not execute query: ERROR: schema “public” already exists

Postgresql – pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

Related Question