Dump a complete PostgreSQL database as SQL
|Debian (Etch, Lenny, Squeeze)|
|Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty, Precise, Trusty)|
To dump the complete content of a PostgreSQL database as a series of SQL statements
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.
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.
Create the backup using the
pg_dump finance > finance.sql
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).
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
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.)
To create a backup of all databases use the
pg_dumpall command in place of
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.