From b3f8401205afdaf63cb20dc316d44644c933d5a1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 22 Jan 2018 14:09:09 -0500 Subject: Move handling of database properties from pg_dumpall into pg_dump. This patch rearranges the division of labor between pg_dump and pg_dumpall so that pg_dump itself handles all properties attached to a single database. Notably, a database's ACL (GRANT/REVOKE status) and local GUC settings established by ALTER DATABASE SET and ALTER ROLE IN DATABASE SET can be dumped and restored by pg_dump. This is a long-requested improvement. "pg_dumpall -g" will now produce only role- and tablespace-related output, nothing about individual databases. The total output of a regular pg_dumpall run remains the same. pg_dump (or pg_restore) will restore database-level properties only when creating the target database with --create. This applies not only to ACLs and GUCs but to the other database properties it already handled, that is database comments and security labels. This is more consistent and useful, but does represent an incompatibility in the behavior seen without --create. (This change makes the proposed patch to have pg_dump use "COMMENT ON DATABASE CURRENT_DATABASE" unnecessary, since there is no case where the command is issued that we won't know the true name of the database. We might still want that patch as a feature in its own right, but pg_dump no longer needs it.) pg_dumpall with --clean will now drop and recreate the "postgres" and "template1" databases in the target cluster, allowing their locale and encoding settings to be changed if necessary, and providing a cleaner way to set nondefault tablespaces for them than we had before. This means that such a script must now always be started in the "postgres" database; the order of drops and reconnects will not work otherwise. Without --clean, the script will not adjust any database-level properties of those two databases (including their comments, ACLs, and security labels, which it formerly would try to set). Another minor incompatibility is that the CREATE DATABASE commands in a pg_dumpall script will now always specify locale and encoding settings. Formerly those would be omitted if they matched the cluster's default. While that behavior had some usefulness in some migration scenarios, it also posed a significant hazard of unwanted locale/encoding changes. To migrate to another locale/encoding, it's now necessary to use pg_dump without --create to restore into a database with the desired settings. Commit 4bd371f6f's hack to emit "SET default_transaction_read_only = off" is gone: we now dodge that problem by the expedient of not issuing ALTER DATABASE SET commands until after reconnecting to the target database. Therefore, such settings won't apply during the restore session. In passing, improve some shaky grammar in the docs, and add a note pointing out that pg_dumpall's output can't be expected to load without any errors. (Someday we might want to fix that, but this is not that patch.) Haribabu Kommi, reviewed at various times by Andreas Karlsson, Vaishnavi Prabakaran, and Robert Haas; further hacking by me. Discussion: https://postgr.es/m/CAJrrPGcUurV0eWTeXODwsOYFN=Ekq36t1s0YnFYUNzsmRfdAyA@mail.gmail.com --- doc/src/sgml/ref/pg_dump.sgml | 34 ++++++++++++++++++++------- doc/src/sgml/ref/pg_dumpall.sgml | 50 +++++++++++++++++++++++++++++----------- doc/src/sgml/ref/pg_restore.sgml | 11 +++++++++ 3 files changed, 74 insertions(+), 21 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 08cad68199f..11582dd1c82 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -46,9 +46,10 @@ PostgreSQL documentation - pg_dump only dumps a single database. To backup - global objects that are common to all databases in a cluster, such as roles - and tablespaces, use . + pg_dump only dumps a single database. + To back up an entire cluster, or to back up global objects that are + common to all databases in a cluster (such as roles and tablespaces), + use . @@ -142,7 +143,8 @@ PostgreSQL documentation switch is therefore only useful to add large objects to dumps where a specific schema or table has been requested. Note that blobs are considered data and therefore will be included when - --data-only is used, but not when --schema-only is. + is used, but not + when is. @@ -196,6 +198,17 @@ PostgreSQL documentation recreates the target database before reconnecting to it. + + With , the output also includes the + database's comment if any, and any configuration variable settings + that are specific to this database, that is, + any ALTER DATABASE ... SET ... + and ALTER ROLE ... IN DATABASE ... SET ... + commands that mention this database. + Access privileges for the database itself are also dumped, + unless is specified. + + This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you @@ -1231,10 +1244,6 @@ CREATE DATABASE foo WITH TEMPLATE template0; ANALYZE after restoring from a dump file to ensure optimal performance; see and for more information. - The dump file also does not - contain any ALTER DATABASE ... SET commands; - these settings are dumped by , - along with database users and other installation-wide settings. @@ -1325,6 +1334,15 @@ CREATE DATABASE foo WITH TEMPLATE template0; + + To reload an archive file into the same database it was dumped from, + discarding the current contents of that database: + + +$ pg_restore -d postgres --clean --create db.dump + + + To dump a single table named mytab: diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index 5196a211b1e..4a639f2d41e 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -36,13 +36,10 @@ PostgreSQL documentation of a cluster into one script file. The script file contains SQL commands that can be used as input to to restore the databases. It does this by - calling for each database in a cluster. + calling for each database in the cluster. pg_dumpall also dumps global objects - that are common to all databases. + that are common to all databases, that is, database roles and tablespaces. (pg_dump does not save these objects.) - This currently includes information about database users and - groups, tablespaces, and properties such as access permissions - that apply to databases as a whole. @@ -50,7 +47,7 @@ PostgreSQL documentation databases you will most likely have to connect as a database superuser in order to produce a complete dump. Also you will need superuser privileges to execute the saved script in order to be - allowed to add users and groups, and to create databases. + allowed to add roles and create databases. @@ -308,7 +305,7 @@ PostgreSQL documentation Use conditional commands (i.e. add an IF EXISTS - clause) to clean databases and other objects. This option is not valid + clause) to drop databases and other objects. This option is not valid unless is also specified. @@ -500,10 +497,11 @@ PostgreSQL documentation The option is called --dbname for consistency with other client applications, but because pg_dumpall - needs to connect to many databases, database name in the connection - string will be ignored. Use -l option to specify - the name of the database used to dump global objects and to discover - what other databases should be dumped. + needs to connect to many databases, the database name in the + connection string will be ignored. Use the -l + option to specify the name of the database used for the initial + connection, which will dump global objects and discover what other + databases should be dumped. @@ -657,6 +655,17 @@ PostgreSQL documentation messages will refer to pg_dump. + + The option can be useful even when your + intention is to restore the dump script into a fresh cluster. Use of + authorizes the script to drop and re-create the + built-in postgres and template1 + databases, ensuring that those databases will retain the same properties + (for instance, locale and encoding) that they had in the source cluster. + Without the option, those databases will retain their existing + database-level properties, as well as any pre-existing contents. + + Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You @@ -664,6 +673,18 @@ PostgreSQL documentation databases. + + The dump script should not be expected to run completely without errors. + In particular, because the script will issue CREATE ROLE + for every role existing in the source cluster, it is certain to get a + role already exists error for the bootstrap superuser, + unless the destination cluster was initialized with a different bootstrap + superuser name. This error is harmless and should be ignored. Use of + the option is likely to produce additional + harmless error messages about non-existent objects, although you can + minimize those by adding . + + pg_dumpall requires all needed tablespace directories to exist before the restore; otherwise, @@ -688,10 +709,13 @@ PostgreSQL documentation $ psql -f db.out postgres - (It is not important to which database you connect here since the + It is not important to which database you connect here since the script file created by pg_dumpall will contain the appropriate commands to create and connect to the saved - databases.) + databases. An exception is that if you specified , + you must connect to the postgres database initially; + the script will attempt to drop other databases immediately, and that + will fail for the database you are connected to. diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 9946b94e84d..a2ebf75ebb5 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -126,6 +126,17 @@ recreate the target database before connecting to it. + + With , pg_restore + also restores the database's comment if any, and any configuration + variable settings that are specific to this database, that is, + any ALTER DATABASE ... SET ... + and ALTER ROLE ... IN DATABASE ... SET ... + commands that mention this database. + Access privileges for the database itself are also restored, + unless is specified. + + When this option is used, the database named with is used only to issue the initial DROP DATABASE and -- cgit v1.2.3