Will & Skill Developers

Will & Skill Developers


Thoughts, snippets and ideas from the team at Will & Skill AB, Stockholm.

Faisal Mahmud
Author

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch

Share


Backing up and restoring a Postgres database

Faisal MahmudFaisal Mahmud

Backup

Simple example as postgres user

The example below will create plaintext dump of your database.

$ sudo su postgres
$ pg_dump [database_name] > database.sql

If you want to save it as snapshots it is smarter to compress the output

$ pg_dump -Fc [database_name] > database.bak # compressed binary format
$ pg_dump -Ft [database_name] > database.tar # tarball

Call pg_dump as any user

Note: You need to know the password of the database

pg_dump -Fc -U {user} -h {host} -p {port} {name} --no-owner --no-acl -f {path}

Example

pg_dump -Fc -U django -h localhost django --no-owner --no-acl -f ~/django.bak

List of all pg_dump commands

To list all of the available options of pg_dump, please use the -? flag.

$ pg_dump -?

In the example above we use some flags that makes it easier for us to take snapshots of our database in our test environment.

Note: You might have to make some tweaks on your own.

-F { c | t | p } or --format { c | t | p }
Determines the file format of the output:

c (gzip compressed)
A format of c creates a gzip-compressed tar file (i.e., a .tar.gz file).

t (tar)
A value of t creates a tar file (i.e., a .tar file).

p (plain text)
The default value of p causes plain text output.

Note that pg_restore is typically used to handle files created with the c or t (gzip-compressed or tar) formats.

-h HOSTNAME or --host=HOSTNAME
Specifies that HOSTNAME should be connected to, rather than the localhost. Use this when the target database is on another server.

-O or --no-owner
Causes ownership to not be taken into account in the dump. A restore with suppressed ownership will cause all re-created objects to belong to the user performing the restore.

-x or --no-acl
Suppresses any GRANT or REVOKE statements, which are usually used to preserve the rights set at the time of the dump. Use this flag if you do not wish to enforce any existing rights or restrictions when re-creating a database from this dump.

-f FILENAME or --file=FILENAME
Directs the output of pgdump to a file named FILENAME, rather than to stdout. The user executing pgdump must have system permissions to write to this file.

Restore

Simple example as postgres user

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

pg_restore -Fc -c -U {user} -h {host} -d {name} --no-owner --no-acl -v {path}

Example

pg_restore -Fc -c -U django -h localhost -d django --no-owner --no-acl -v ~/django.bak

List of all pg_restore commands

To list all of the available options of pg_restore, please use the -? flag.

$ pg_restore -?

In the example above we use the -c flag in order to re-initialize an existing database. This is not necessary if the database has been newly created.

-c or --clean
Specifies that SQL statements to drop all existing objects will precede the SQL statements to create those objects. This option is useful in re-initializing an existing database, rather than dropping it and recreating it from scratch.

-v or --verbose
Specifies verbose mode. This will cause pgdump/pgrestore to output detailed object comments and start/stop times to the dump file, and progress messages to standard error.

You can read more about these commands in the official docs

Faisal Mahmud
Author

Faisal Mahmud

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch

Comments