PGTS PGTS Pty. Ltd.   ACN: 007 008 568

point Site Navigation

point Other Blog Threads

  Valid HTML 4.01 Transitional

   Stop Spam! Stop Viruses!
   Secure And Reliable Ubuntu Desktop!


   If you own a netbook/laptop~
   Download Ubuntu Netbook!

PGTS Humble Blog

Thread: Tips/Tricks For Programming etc

Mission Accomplished -- George Bush, 2003.

Restoring postgres databases

Chronogical Blog Entries:

Date: Wed, 12 Aug 2015 22:55:10 +1000

The pg_dump utility is the obvious choice for backing up postgres data.
However the pg_restore utility is less than remarkable when it comes to restoring data.

The pg_dump utility offers the following formats:

-p, --plain

Output a plain-text SQL script file (the default).

-c, --custom

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

-d, --directory

Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default and also supports parallel dumps.

-t, --tar

Output a tar-format archive suitable for input into pg_restore. The tar-format is compatible with the directory-format; extracting a tar-format archive produces a valid directory-format archive. However, the tar-format does not support compression and has a limit of 8 GB on the size of individual tables. Also, the relative order of table data items cannot be changed during restore.

The method can be specified by invoking with the short or long options as above (e.g. -t or --tar for tar-format). All of them perform very well.

However the performance of the converse utility (pg_restore) leaves much to be desired. Running the pg_restore utility with the -C or -c options can take several hours in Ubuntu 14.04.

So after experimenting will all of them, I have reverted to the old, tried and true default (SQL) method. You can do this by specifying nothing or using the -p or --plain options. This may not be the fastest pg_dump method, but it is still quite quick. And being able to back-up your data quickly is not very useful if you have to take the database offline for four hours in order to restore the data.

The plain text method will produce SQL that, provided you drop the DB first (with dropdb) and re-create it (with createdb), will restore an entire database in a few minutes at most.

An example would be as follows (for the database foo):

# Create a backup of foo
pg_dump -d foo | gzip -c > $PG_DUMP_DEST/foo_db.sql.gz

# Restore from the backup
gunzip -f $PG_DUMP_DEST/foo_db.sql.gz
dropdb foo
createdb foo
psql -d foo -f $PG_DUMP_DEST/foo_db.sql

Other Blog Posts In This Thread:

Copyright     2015, Gerry Patterson. All Rights Reserved.