Information on the Postgres Backup implementation

There is a plethora of information on backup and restore of Postgres databases online. The problem is that a lot of it is old and out of date. This means it can hard to figure out how you should do something as simple as backup. So here is list of the various backup file formats. Hope this helps you.

There are 3 output formats for a backup of a postgres database. I am going to describe the pros and cons of each below

Script or plain-text format

• This is the default format for backup
• This is simply a text file which contains the commands necessary to rebuild the database.
• Can be restored using psql or pgrestore
  • As of Postgres 8.2, you can backup “large objects” using this format by specifying the -b option. (See section titled E.57.3.12. pgdump Changes in http://www.postgresql.org/docs/8.4/static/release-8-1.html)
• Individual tables CANNOT be restored using the format. In order to restore an individual table, you will need to restore the entire table first. Then copy the table contents from the restored database to the working database.

Custom archive format

• Most flexible format.
• By default this format is compressed, but compression can be disabled using the —compress option
• Must use pg_restore to restore the database
• The backup file can be opened and the contents reviewed using pg_restore
• Individual tables CAN be restored using the format.
• Backup file size(uncompressed) can be roughly 2x the size of the database. This is due overhead necessary to allow pg_restore the ability to restore individual tables, etc

tar archive format

• Has the same features as Custom format except
• Tables cannot be larger than 8GB in size or the restore will fail.
• Output file can be opened using the tar command.

(Much of this information was found at http://www.postgresql.org/docs/8.4/static/app-pgdump.html and http://www.postgresql.org/docs/8.4/static/app-pgrestore.html ]