diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index ccb76d8..d6d82f0 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -38,7 +38,7 @@ pg_dump dbname > outfile As you see, pg_dump writes its result to the - standard output. We will see below how this can be useful. + standard output. @@ -47,8 +47,12 @@ pg_dump dbname > pg_dump does not operate with special permissions. In particular, it must - have read access to all tables that you want to back up, so in - practice you almost always have to run it as a database superuser. + have read access to all tables that you want to back up. Whether + it is going to have proper access permissions is determined by + the privileges granted to the role used to connect to the database server. + Although a superuser role will always have the necessary permissions + as long as the role used has read access to tables, sequences, etc. + it need not to be a superuser role. @@ -95,13 +99,53 @@ pg_dump dbname > - If your database schema relies on OIDs (for instance, as foreign - keys) you must instruct pg_dump to dump the OIDs - as well. To do this, use the command-line - option. + The use of OIDs on user objects is deprecated. If your database schema + relies on OIDs (for instance, as foreign keys) you must instruct + pg_dump to dump the OIDs as well. To do this, use + the command-line option. + + The above example creates a plain text file. This type of dump can be used + to restore a database in full. However there are more sophisticated + PostgreSQL backup formats which allow for far greater + control when working with backups. One of these is + the custom format, which the following more elaborate + example creates: + + +pg_dump -U username --format=c --file=mydatabase.sqlc dbname + + where -U instructs pg_dump to connect as specified database user, + --format sets output file format + as custom (other supported options are: directory, tar, plain text) and + --file specifies output file name. + + The most interesting of these is + --format. By default + pg_dump creates a plaintext backup. You may be + better off creating a custom format backup, since the custom format is much + more flexible. + + + + Using the custom format you are able to restore single objects from a + backup. For example to restore only a specified index from a backup + file: + +pg_restore -U username --dbname=dbname --index=indexname + + To restore only a single function: + +pg_restore -U username --dbname=dbname --function=functionname(args) + + To restore only a single table: + +pg_restore -U username --dbname=dbname --table=tablename + + + Restoring the Dump @@ -112,8 +156,8 @@ pg_dump dbname > psql dbname < infile - where infile is the - file output by the pg_dump command. The database infile is a plaintext + backup output by the pg_dump command. The database dbname will not be created by this command, so you must create it yourself from template0 before executing psql (e.g., with @@ -176,6 +220,28 @@ pg_dump -h host1 dbname | psql -h h + The psql command is a way, as shown above, to restore + plaintext backups. To restore a custom format backup + the pg_restore command must be used. It has options + similar to those of pg_dump. A simple use + of pg_restore is: + +pg_restore -U username --dbname=databasename filename + + Where filename is the name of the backup file. + + + + + Do not confuse + + + After restoring a backup, it is wise to run on each database so the query optimizer has useful statistics; @@ -222,6 +288,22 @@ psql -f infile postgres each database will be internally consistent, the snapshots of different databases might not be exactly in-sync. + + + Unfortunately, pg_dumpall can only create plaintext + backups. However, it is currently the only way to backup the globals in your + cluster. So, a reasonable backup strategy to backup your globals and + produce a flexible backup of every database in the cluster would + look like this: + +pg_dumpall -g -Uusername --file=globals.sql; +psql -AtU postgres + -c "SELECT datname FROM pg_database WHERE NOT datistemplate" \ + | while read f; + do pg_dump -Upostgres --format=c --file=$f.sqlc $f; + done; + + @@ -239,12 +321,21 @@ psql -f infile postgres Use compressed dumps. You can use your favorite compression program, for example - gzip: + gzip or pigz + (a parallel implementation of gzip for modern + multi-processor, multi-core machines): pg_dump dbname | gzip > filename.gz + or: + + +pg_dump dbname | pigz > filename.gz + + + Reload with: @@ -254,7 +345,7 @@ gunzip -c filename.gz | psql -cat filename.gz | gunzip | psql dbname +pigz -dc filename.gz | psql dbname