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...
  • And what do you get? Do you see any error messages? – Dan Andreatta Apr 24 '10 at 20:14
  • I get way too many errors to repost here.. they wiz past at high speed... – Ian Apr 24 '10 at 20:44

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:

   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
  • Well don't I feel stupid... – Ian Apr 25 '10 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.