PostgreSQL: Backup and Restore


PostgreSQL: Backup and Restore

Sun, 13 October 2013 15:10

System Administration

Backup


pg_dump is the utility for backing up your database. There are a few key knobs you have when dumping your database. These include:

Plaintext format (readable and large) vs. binary format (unreadable and small) vs. tarball (ideal for restore)
All of your database or specific schemas/tables
So lets get started with some backing up - if you need a reminder of your databases you can list them with:

psql -l
Then carry out the dump with:

pg_dump database_name_here > database.sql
The above will create the plaintext dump of your database. To create a form more suitable you a persistent backup and storage you can use either of the below:

pg_dump -Fc database_name_here > database.bak # compressed binary format
pg_dump -Ft database_name_here > database.tar # compressed tarball

Restore


When restoring, there are a few more options that you’ll want to consider:

If the database already exists
The format of your backup
pg_restore -Fc database.bak # restore compressed binary format
pg_restore -Ft database.tar # restore tarball
If your database already exists you only need to run the above. However, if you’re creating your database new from the restore you’ll want to run a command similar to the following:

pg_restore -Fc -C database.bak # restore compressed binary format
pg_restore -Ft -C database.tar # restore tarball