26 June 2013

This is not to be the best procedure for backup and restore, but my way how I do this.

Overview

On the postgresql site there is one chapter about backup and restore. Maybe it will be better to read the chapter before reading this post.

Backup

For backup there is pg_dump utility. There is at least one command line parameters that meed to be mentioned.

-F format, --format=format

See the manual page your current postgresql version for available formats. For 9.3 they are

  • p (plain): Output a plain-text SQL script file (the default)
  • c (custom): Output a custom-format archive
  • d (directory): Output a directory-format archive
  • t (tar): Output a tar-format archive

My preffered since verzio 7.2 is custom. To be honest I’m not sure about the compatibility between the postgresql version, but within the same version it works great.

So when dumping the database add -Fc to your command line. Why? Read restore.

pg_dump -Fc <other parameters> > database_backup.sql.Fc

Restore

When trying to restore custom format of the postgresql dump you need the pg_restore utility. As one of the command line parameters there need to be -Fc.

So what is so amazing on the custom format?

Image, that you do not want to restore full database but

  • only the subset of the tables.
  • full schema but data for only some tables

I use these 2 scenarios when restoring production several gigabytes big database onto test or development servers.

pg_restore has 2 addition parameters:

  • -l: list the contents of the archive.
  • -L list-file: Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file

Editing the list-file you can remove or comment any line you do not need to be restored. So my restore procedure looks like

pg_restore -l database_backup.Fc > database_backup.Fc.list
vim  database_backup.Fc.list
pg_restore <other parameters> -L database_backup.Fc.list -Fc database_backup.Fc