PostgreSQL Backup/Restore

backupdatabasepostgresqlrestore

What's the best way to backup a postgresql database?

I've tried using the documentation at www.postgresql.org, but I always get integrity errors when restoring.

Right now I'm using this for backup:

pg_dump -U myuser -d mydatabase db.pg.dump

for restore:

pg_restore -c -r -U myuser -d mydatabase db.pg.dump

But I'm not getting the desired results..

Edit: I should note, my db has a lot of foreign keys..

Some of the errors I see are:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

ERROR:  zero-length delimited identifier at or near """"
LINE 1: ..._text_id_fkey" FOREIGN KEY ("text_id") REFERENCES ""."wiki_t...

Best Answer

from the pg_restore man page:

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump(1) in one of the non-plain-text formats.

from the pg_dump man page:

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored.

You are not telling it to dump in a non plain-text format. You are telling it to restore a non plain-text format. obviously this is not going to work.

From the pg_dump man page:

EXAMPLES
   To dump a database called mydb into a SQL-script file:

   $ pg_dump mydb > db.sql

   To reload such a script into a (freshly created) database named newdb:

   $ psql -d newdb -f db.sql

   To dump a database into a custom-format archive file:

   $ pg_dump -Fc mydb > db.dump

   To reload an archive file into a (freshly created) database named newdb:

   $ pg_restore -d newdb db.dump
Related Topic