diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index ccb76d8..7e748b5 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,38 @@ 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 plaintext backup -- a file that + contains SQL commands which, when executed, restore the database. 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. + + Restoring the Dump @@ -112,8 +141,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 +205,46 @@ 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 to restore an entire backup is: + +pg_restore -U username --dbname=databasename filename + + Where filename is the name of + the backup file. + + + + + Do not confuse + + + + 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 + + + + After restoring a backup, it is wise to run on each database so the query optimizer has useful statistics; @@ -222,6 +291,21 @@ 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 mig: + +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 +323,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 +347,7 @@ gunzip -c filename.gz | psql -cat filename.gz | gunzip | psql dbname +pigz -dc filename.gz | psql dbname @@ -293,8 +386,8 @@ cat filename* | psql dbname > filename - A custom-format dump is not a script for psql, but - instead must be restored with pg_restore, for example: + A custom-format dump must be restored with pg_restore, + for example: pg_restore -d dbname filename