Dump a complete PostgreSQL database as SQL

Objective

To dump the complete content of a PostgreSQL database as a series of SQL statements

Scenario

You have a database called finance for which you wish to create a backup copy. The database is hosted on the local machine and accepts UNIX domain connections. The backup is to be performed by a PostgreSQL account called ‘backup’ using ident authentication.

Method

Grant the necessary privileges

If this has not already been done, first ensure that database account has permission to read everything in the relevant database. Unfortunately the PostgreSQL security model does not make it easy to provide exactly the required level of access. Prior to version 9.0 the options were:

• grant access to each table (and other database objects) individually, or
• perform the backup as the owner of the database (if that account also owns all of the objects within the database), or
• perform the backup as a superuser.

Version 9.0 adds a fourth option:

• grant access to ALL TABLES IN SCHEMA finance, and similarly for all sequences and all functions.

For simplicity we will assume that granting superuser access is an acceptable solution in this case:

ALTER USER backup WITH SUPERUSER

Although this is not ideal from a security perspective, it has the virtue of minimising the risk of the backup failing (either now or in the future) due to incorrect permissions.

Note that the following command would not have granted the necessary access:

GRANT ALL ON DATABASE finance TO backup;

This is because ALL in this context means everything that you can do to a database, whereas SELECT is an operation on a table.

Dump the database

Create the backup using the pg_dump command:

pg_dump finance > finance.sql

By default pg_dump sends its output to stdout, hence the redirection to record it in the file finance.sql.

Also by default, pg_dump produces output that is highly PostgreSQL-specific due to the use of COPY statements for importing data into tables. You can produce a dump that uses INSERT statements instead by means of the -d option (but this is significantly less efficient if you are importing back into PostgreSQL).

Errors

Aborting because of version mismatch

An error of the form:

pg_dump: server version: 8.3.12; pg_dump version: 8.3.9
pg_dump: aborting because of version mismatch  (Use the -i option to proceed anyway.)

indicates that the version of pg_dump you are using derives from a different version of PostgreSQL to the server with which you are attempting to connect. This does not necessarily mean that the dump would fail, but nor is there any assurance that it would succeed. The risk of failure depends on how the versions differ:

• Differences in the minor version (for example, between 8.3.9 and 8.3.12) are probably safe.
• Small differences in the major version (for example, between 8.3 and 8.4) are probably safe if the client is more recent than the server, but probably unsafe if the server is more recent than the client.

If the reason for dumping the database is to make a backup copy then there is a very good chance that you will not discover any problems until it is too late. In that case the recommendation must be to install a matching version of the client unless there is a very good reason not to (building from source if necessary).

As indicated in the error message, if you are willing to take the risk then use of the -i option will instruct pg_dump to disregard any version mismatch:

pg_dump -i finance > finance.sql

Permission denied

An error of the form:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for relation employees
pg_dump: The command was: LOCK TABLE public.employees IN ACCESS SHARE MODE

indicates that the database account does not have permission to SELECT the specified table. (Unlike MySQL, it is neither necessary nor possible to give separate permission to lock the table.)

Variations

Dump all databases

To create a backup of all databases use the pg_dumpall command in place of pg_dump:

pg_dumpall > backup.sql

If you have chosen to grant specific permissions (as opposed to performing the backup as a superuser) then you will need to do that for each database in turn. If pg_dumpall encounters an object it does not have permission to read then it will fail with an error message.

• Conventions
• Privacy
• Legal