dumping and restoring PostgreSQL databases

This card is just about creating simple PostgreSQL dumps. This is no instruction for a backup strategy nor a guide for SQL dump performance optimization.

Read before starting

• I will assume that all commands will be executed as local postgres user on a database server master. Please mind that you should stop the replication on a slave PostgreSQL server before creating dumps
• Dumps can get huge, be careful so that the system running a production PostgreSQL won't run out of disk space
• dumps should always be gzipped to reduce the size. If you don't want to gzip replace the pipe to gzip with a redirection to a .dump file (for e.g. > psql1-databasename-foo.dump) or use pg_dump with the custom format

Dump formats

PostgreSQL dumps can be created in multiple formats (see option -F in pg_dump documentation Show archive.org snapshot . Benefits of the custom format are more configurable restores (for e.g. you can omit setting permissions when restoring the dumps or configure afterwards if you want to drop existing tables when restoring). It's also possible to use multiple threads when restoring the custom format (which speeds up restores). The custom dump is compressed by default too. To get a better idea check the parameters in the mentioned documentation. You can use a custom format dump to create a textdump from it: convert PostgreSQL custom dump format to textdump

Using custom format dumps

Dumping the database

Copy/usr/bin/pg_dump -F c $dbname > $dbname.pgdump

Restoring the database dump or parts of it

A simple backup restore in the same database cluster with the same user etc could look like this:

Copy# restore the whole database and drop existing objects before restoring
/usr/bin/pg_restore --clean -d $dbname $dbname.pgdump

# restore only one table
# Do NOT use pg_restore -t $tablename in combination with -c
# -c will drop all indexes/sequences for the given table and -t restores the table only
/usr/bin/pg_restore -d $dbname -t $tablename $dbname.pgdump

# showing the contents of a dump
pg_restore -l $dbname.pgdump

There are several scenarios where configurable dumps are helpful. For e.g.:

dump and restore a db from production in staging (after you did dropdb staging and recreated it)

Copypg_dump production_db -Fc > production_db.pgdump
pg_restore -d staging_db --no-owner --role=staging_db production_db.pgdump

Using text format dumps

Dumping the whole database cluster

This will dump all databases, users, relations, extensions, ... in form of a script (text) file.
Delete statements will be included to remove existing databases when sourcing this dump. This is useful for moving the content of a database to an other server. WARNING: Sourcing this dump will drop the existing databases in the cluster if they exist in the dump too! If you don't want to drop the databases when importing omit the -c parameter

Copy# dump
pgdumpall -c | gzip > dumpall.dump.gz
# restore
zcat dumpall.dump.gz | psql

Dumping a single database

A dump of the given database will be created. This does not include the user. When restoring this dump you must create the user and the database before sourcing the dump.

Copy# dump
pg_dump $dbname | gzip > ${dbname}.dump.gz
# restore
createuser ${dbname} --pwprompt
createdb -O $dbname $dbname
zcat ${dbname}.dump.gz | psql $dbname

Dumping a single table

In some cases you just need one table to be dumped an restored. Only the content of the table is stored in the dump. On restore an exisiting table with the name of the dumped table will not be overwritten. You need to delete the table first.

Copy# dump
pg_dump $dbname --table $tablename | gzip > ${dbname}-${table}.dump.gz
# restore
psql $dbname
drop table $tablename;
CTRL+D
zcat ${dbname}-${table}.dump.gz | psql $dbname

Possible issues when restoring dumps

Copypg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2653; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

If you restore the dump and are not the postgres user you are not allowed to alter extensions. This includes comments on the extensions too. Usually you can ignore this error.

Copypg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges could be revoked for "public"
pg_restore: WARNING:  no privileges were granted for "public"
pg_restore: WARNING:  no privileges were granted for "public"

Also fine