Postgres backups for applications
Postgres provides two tools for backing up a single database, pg_dump and pg_restore.
pg_dump is used for backing up a single databases and
pg_restore is used for restoring backups made by
pg_dump. There are many options for these tools, but this post is focused on backing up and restoring the database exactly as it was, which is often the case when restoring an application database.
Because the default options require admin rights you will most likely always want to connect as a superuser (e.g postgres). It goes without saying, but do not run these commands without first testing them on test databases.
pg_dump and pg_restore
There are two main output formats for
pg_dump, sql-scripts or archive file formats. This post will focus on the archive formats since the sql-scripts require a lot of options upfront. For example
--create, will need to specified when you create the dump, which makes the dump a lot less flexible.
PGUSER=postgres PGPASSWORD=<pwd> pg_dump -Fc mydb > backup.dump
-Fc means we use pg_dump’s custom format which is also compressed by default.
Then to restore the backup file:
PGUSER=postgres PGPASSWORD=xxx pg_restore -C -d postgres backup.dump
pg_restore to create the database in the dump. It requires that there is no existing database with the same name. If you want to restore into a different database than the one in the dump, rename the db after
pg_restore has run.
-d postgres is the database where pg_restore will apply commands. If you don’t use
pg_restore will restore into this database (bad).
If you are restoring but there’s already an existing database, then you can rename the db with:
ALTER DATABASE <db> RENAME TO <new_db>;
Example of using
pg_dump with sql-script format
PGUSER=postgres pg_dump old > db.sql PGUSER=postgres psql -d new -f db.sql
pg_dumpall is similar to
pg_dump but will backup all databases at once. However it does not support the archive file format which means options need to be specified up-front and you cannot use the output with
PGUSER=postgres pg_dumpall > db.out PGUSER=postgres psql -f db.out postgres