38 cards
Posted about 2 years ago. Visible to the public.

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. 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). 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

/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:

# restore the whole database and drop existing objects before restoring /usr/bin/pg_restore --clean -d $dbname $dbname.pgdump # restore only one table /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 rerun puppet to create it)

pg_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

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

# 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.

# 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.

# 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

pg_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.

pg_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

Owner of this card:

Claus-Theodor Riegg
Last edit:
about 1 month ago
by Claus-Theodor Riegg
This website uses cookies to improve usability and analyze traffic.
Accept or learn more