1

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

1 Answer 1

3

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .