Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb3f8401

Browse files
committed
Move handling of database properties from pg_dumpall into pg_dump.
This patch rearranges the division of labor between pg_dump and pg_dumpallso that pg_dump itself handles all properties attached to a singledatabase. Notably, a database's ACL (GRANT/REVOKE status) and local GUCsettings established by ALTER DATABASE SET and ALTER ROLE IN DATABASE SETcan be dumped and restored by pg_dump. This is a long-requestedimprovement."pg_dumpall -g" will now produce only role- and tablespace-related output,nothing about individual databases. The total output of a regularpg_dumpall run remains the same.pg_dump (or pg_restore) will restore database-level properties only whencreating the target database with --create. This applies not only toACLs and GUCs but to the other database properties it already handled,that is database comments and security labels. This is more consistentand useful, but does represent an incompatibility in the behavior seenwithout --create.(This change makes the proposed patch to have pg_dump use "COMMENT ONDATABASE CURRENT_DATABASE" unnecessary, since there is no case wherethe 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_dumpno longer needs it.)pg_dumpall with --clean will now drop and recreate the "postgres" and"template1" databases in the target cluster, allowing their locale andencoding settings to be changed if necessary, and providing a cleanerway to set nondefault tablespaces for them than we had before. Thismeans 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 propertiesof those two databases (including their comments, ACLs, and securitylabels, which it formerly would try to set).Another minor incompatibility is that the CREATE DATABASE commands in apg_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_dumpwithout --create to restore into a database with the desired settings.Commit4bd371f's hack to emit "SET default_transaction_read_only = off"is gone: we now dodge that problem by the expedient of not issuing ALTERDATABASE 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 pointingout 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
1 parentd6c8466 commitb3f8401

File tree

10 files changed

+470
-537
lines changed

10 files changed

+470
-537
lines changed

‎doc/src/sgml/ref/pg_dump.sgml

Lines changed: 26 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -46,9 +46,10 @@ PostgreSQL documentation
4646
</para>
4747

4848
<para>
49-
<application>pg_dump</application> only dumps a single database. To backup
50-
global objects that are common to all databases in a cluster, such as roles
51-
and tablespaces, use <xref linkend="app-pg-dumpall"/>.
49+
<application>pg_dump</application> only dumps a single database.
50+
To back up an entire cluster, or to back up global objects that are
51+
common to all databases in a cluster (such as roles and tablespaces),
52+
use <xref linkend="app-pg-dumpall"/>.
5253
</para>
5354

5455
<para>
@@ -142,7 +143,8 @@ PostgreSQL documentation
142143
switch is therefore only useful to add large objects to dumps
143144
where a specific schema or table has been requested. Note that
144145
blobs are considered data and therefore will be included when
145-
--data-only is used, but not when --schema-only is.
146+
<option>--data-only</option> is used, but not
147+
when <option>--schema-only</option> is.
146148
</para>
147149
</listitem>
148150
</varlistentry>
@@ -196,6 +198,17 @@ PostgreSQL documentation
196198
recreates the target database before reconnecting to it.
197199
</para>
198200

201+
<para>
202+
With <option>--create</option>, the output also includes the
203+
database's comment if any, and any configuration variable settings
204+
that are specific to this database, that is,
205+
any <command>ALTER DATABASE ... SET ...</command>
206+
and <command>ALTER ROLE ... IN DATABASE ... SET ...</command>
207+
commands that mention this database.
208+
Access privileges for the database itself are also dumped,
209+
unless <option>--no-acl</option> is specified.
210+
</para>
211+
199212
<para>
200213
This option is only meaningful for the plain-text format. For
201214
the archive formats, you can specify the option when you
@@ -1231,10 +1244,6 @@ CREATE DATABASE foo WITH TEMPLATE template0;
12311244
<command>ANALYZE</command> after restoring from a dump file
12321245
to ensure optimal performance; see <xref linkend="vacuum-for-statistics"/>
12331246
and <xref linkend="autovacuum"/> for more information.
1234-
The dump file also does not
1235-
contain any <command>ALTER DATABASE ... SET</command> commands;
1236-
these settings are dumped by <xref linkend="app-pg-dumpall"/>,
1237-
along with database users and other installation-wide settings.
12381247
</para>
12391248

12401249
<para>
@@ -1325,6 +1334,15 @@ CREATE DATABASE foo WITH TEMPLATE template0;
13251334
</screen>
13261335
</para>
13271336

1337+
<para>
1338+
To reload an archive file into the same database it was dumped from,
1339+
discarding the current contents of that database:
1340+
1341+
<screen>
1342+
<prompt>$</prompt> <userinput>pg_restore -d postgres --clean --create db.dump</userinput>
1343+
</screen>
1344+
</para>
1345+
13281346
<para>
13291347
To dump a single table named <literal>mytab</literal>:
13301348

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 37 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -36,21 +36,18 @@ PostgreSQL documentation
3636
of a cluster into one script file. The script file contains
3737
<acronym>SQL</acronym> commands that can be used as input to <xref
3838
linkend="app-psql"/> to restore the databases. It does this by
39-
calling <xref linkend="app-pgdump"/> for each database ina cluster.
39+
calling <xref linkend="app-pgdump"/> for each database inthe cluster.
4040
<application>pg_dumpall</application> also dumps global objects
41-
that are common to all databases.
41+
that are common to all databases, that is, database roles and tablespaces.
4242
(<application>pg_dump</application> does not save these objects.)
43-
This currently includes information about database users and
44-
groups, tablespaces, and properties such as access permissions
45-
that apply to databases as a whole.
4643
</para>
4744

4845
<para>
4946
Since <application>pg_dumpall</application> reads tables from all
5047
databases you will most likely have to connect as a database
5148
superuser in order to produce a complete dump. Also you will need
5249
superuser privileges to execute the saved script in order to be
53-
allowed to addusers and groups, and to create databases.
50+
allowed to addroles and create databases.
5451
</para>
5552

5653
<para>
@@ -308,7 +305,7 @@ PostgreSQL documentation
308305
<listitem>
309306
<para>
310307
Use conditional commands (i.e. add an <literal>IF EXISTS</literal>
311-
clause) toclean databases and other objects. This option is not valid
308+
clause) todrop databases and other objects. This option is not valid
312309
unless <option>--clean</option> is also specified.
313310
</para>
314311
</listitem>
@@ -500,10 +497,11 @@ PostgreSQL documentation
500497
<para>
501498
The option is called <literal>--dbname</literal> for consistency with other
502499
client applications, but because <application>pg_dumpall</application>
503-
needs to connect to many databases, database name in the connection
504-
string will be ignored. Use <literal>-l</literal> option to specify
505-
the name of the database used to dump global objects and to discover
506-
what other databases should be dumped.
500+
needs to connect to many databases, the database name in the
501+
connection string will be ignored. Use the <literal>-l</literal>
502+
option to specify the name of the database used for the initial
503+
connection, which will dump global objects and discover what other
504+
databases should be dumped.
507505
</para>
508506
</listitem>
509507
</varlistentry>
@@ -657,13 +655,36 @@ PostgreSQL documentation
657655
messages will refer to <application>pg_dump</application>.
658656
</para>
659657

658+
<para>
659+
The <option>--clean</option> option can be useful even when your
660+
intention is to restore the dump script into a fresh cluster. Use of
661+
<option>--clean</option> authorizes the script to drop and re-create the
662+
built-in <literal>postgres</literal> and <literal>template1</literal>
663+
databases, ensuring that those databases will retain the same properties
664+
(for instance, locale and encoding) that they had in the source cluster.
665+
Without the option, those databases will retain their existing
666+
database-level properties, as well as any pre-existing contents.
667+
</para>
668+
660669
<para>
661670
Once restored, it is wise to run <command>ANALYZE</command> on each
662671
database so the optimizer has useful statistics. You
663672
can also run <command>vacuumdb -a -z</command> to analyze all
664673
databases.
665674
</para>
666675

676+
<para>
677+
The dump script should not be expected to run completely without errors.
678+
In particular, because the script will issue <command>CREATE ROLE</command>
679+
for every role existing in the source cluster, it is certain to get a
680+
<quote>role already exists</quote> error for the bootstrap superuser,
681+
unless the destination cluster was initialized with a different bootstrap
682+
superuser name. This error is harmless and should be ignored. Use of
683+
the <option>--clean</option> option is likely to produce additional
684+
harmless error messages about non-existent objects, although you can
685+
minimize those by adding <option>--if-exists</option>.
686+
</para>
687+
667688
<para>
668689
<application>pg_dumpall</application> requires all needed
669690
tablespace directories to exist before the restore; otherwise,
@@ -688,10 +709,13 @@ PostgreSQL documentation
688709
<screen>
689710
<prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
690711
</screen>
691-
(It is not important to which database you connect here since the
712+
It is not important to which database you connect here since the
692713
script file created by <application>pg_dumpall</application> will
693714
contain the appropriate commands to create and connect to the saved
694-
databases.)
715+
databases. An exception is that if you specified <option>--clean</option>,
716+
you must connect to the <literal>postgres</literal> database initially;
717+
the script will attempt to drop other databases immediately, and that
718+
will fail for the database you are connected to.
695719
</para>
696720
</refsect1>
697721

‎doc/src/sgml/ref/pg_restore.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,17 @@
126126
recreate the target database before connecting to it.
127127
</para>
128128

129+
<para>
130+
With <option>--create</option>, <application>pg_restore</application>
131+
also restores the database's comment if any, and any configuration
132+
variable settings that are specific to this database, that is,
133+
any <command>ALTER DATABASE ... SET ...</command>
134+
and <command>ALTER ROLE ... IN DATABASE ... SET ...</command>
135+
commands that mention this database.
136+
Access privileges for the database itself are also restored,
137+
unless <option>--no-acl</option> is specified.
138+
</para>
139+
129140
<para>
130141
When this option is used, the database named with <option>-d</option>
131142
is used only to issue the initial <command>DROP DATABASE</command> and

‎src/bin/pg_dump/dumputils.c

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -807,3 +807,54 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
807807
printfPQExpBuffer(init_racl_subquery,"NULL");
808808
}
809809
}
810+
811+
/*
812+
* Helper function for dumping "ALTER DATABASE/ROLE SET ..." commands.
813+
*
814+
* Parse the contents of configitem (a "name=value" string), wrap it in
815+
* a complete ALTER command, and append it to buf.
816+
*
817+
* type is DATABASE or ROLE, and name is the name of the database or role.
818+
* If we need an "IN" clause, type2 and name2 similarly define what to put
819+
* there; otherwise they should be NULL.
820+
* conn is used only to determine string-literal quoting conventions.
821+
*/
822+
void
823+
makeAlterConfigCommand(PGconn*conn,constchar*configitem,
824+
constchar*type,constchar*name,
825+
constchar*type2,constchar*name2,
826+
PQExpBufferbuf)
827+
{
828+
char*mine;
829+
char*pos;
830+
831+
/* Parse the configitem. If we can't find an "=", silently do nothing. */
832+
mine=pg_strdup(configitem);
833+
pos=strchr(mine,'=');
834+
if (pos==NULL)
835+
{
836+
pg_free(mine);
837+
return;
838+
}
839+
*pos++='\0';
840+
841+
/* Build the command, with suitable quoting for everything. */
842+
appendPQExpBuffer(buf,"ALTER %s %s ",type,fmtId(name));
843+
if (type2!=NULL&&name2!=NULL)
844+
appendPQExpBuffer(buf,"IN %s %s ",type2,fmtId(name2));
845+
appendPQExpBuffer(buf,"SET %s TO ",fmtId(mine));
846+
847+
/*
848+
* Some GUC variable names are 'LIST' type and hence must not be quoted.
849+
* XXX this list is incomplete ...
850+
*/
851+
if (pg_strcasecmp(mine,"DateStyle")==0
852+
||pg_strcasecmp(mine,"search_path")==0)
853+
appendPQExpBufferStr(buf,pos);
854+
else
855+
appendStringLiteralConn(buf,pos,conn);
856+
857+
appendPQExpBufferStr(buf,";\n");
858+
859+
pg_free(mine);
860+
}

‎src/bin/pg_dump/dumputils.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,4 +56,9 @@ extern void buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
5656
constchar*acl_column,constchar*acl_owner,
5757
constchar*obj_kind,boolbinary_upgrade);
5858

59+
externvoidmakeAlterConfigCommand(PGconn*conn,constchar*configitem,
60+
constchar*type,constchar*name,
61+
constchar*type2,constchar*name2,
62+
PQExpBufferbuf);
63+
5964
#endif/* DUMPUTILS_H */

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 24 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -489,16 +489,19 @@ RestoreArchive(Archive *AHX)
489489
* whole. Issuing drops against anything else would be wrong,
490490
* because at this point we're connected to the wrong database.
491491
* Conversely, if we're not in createDB mode, we'd better not
492-
* issue a DROP against the database at all.
492+
* issue a DROP against the database at all. (The DATABASE
493+
* PROPERTIES entry, if any, works like the DATABASE entry.)
493494
*/
494495
if (ropt->createDB)
495496
{
496-
if (strcmp(te->desc,"DATABASE")!=0)
497+
if (strcmp(te->desc,"DATABASE")!=0&&
498+
strcmp(te->desc,"DATABASE PROPERTIES")!=0)
497499
continue;
498500
}
499501
else
500502
{
501-
if (strcmp(te->desc,"DATABASE")==0)
503+
if (strcmp(te->desc,"DATABASE")==0||
504+
strcmp(te->desc,"DATABASE PROPERTIES")==0)
502505
continue;
503506
}
504507

@@ -558,6 +561,8 @@ RestoreArchive(Archive *AHX)
558561
* we simply emit the original command for DEFAULT
559562
* objects (modulo the adjustment made above).
560563
*
564+
* Likewise, don't mess with DATABASE PROPERTIES.
565+
*
561566
* If we used CREATE OR REPLACE VIEW as a means of
562567
* quasi-dropping an ON SELECT rule, that should
563568
* be emitted unchanged as well.
@@ -570,6 +575,7 @@ RestoreArchive(Archive *AHX)
570575
* search for hardcoded "DROP CONSTRAINT" instead.
571576
*/
572577
if (strcmp(te->desc,"DEFAULT")==0||
578+
strcmp(te->desc,"DATABASE PROPERTIES")==0||
573579
strncmp(dropStmt,"CREATE OR REPLACE VIEW",22)==0)
574580
appendPQExpBufferStr(ftStmt,dropStmt);
575581
else
@@ -750,11 +756,19 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
750756
reqs=te->reqs;
751757

752758
/*
753-
* Ignore DATABASEentry unless we should create it. We must check this
754-
* here, not in _tocEntryRequired, becausethecreateDB option should not
755-
*affect emitting a DATABASE entry to an archive file.
759+
* Ignore DATABASEand related entries unless createDB is specified. We
760+
*must check thishere, not in _tocEntryRequired, because!createDB
761+
*should not prevent emitting these entries to an archive file.
756762
*/
757-
if (!ropt->createDB&&strcmp(te->desc,"DATABASE")==0)
763+
if (!ropt->createDB&&
764+
(strcmp(te->desc,"DATABASE")==0||
765+
strcmp(te->desc,"DATABASE PROPERTIES")==0||
766+
(strcmp(te->desc,"ACL")==0&&
767+
strncmp(te->tag,"DATABASE ",9)==0)||
768+
(strcmp(te->desc,"COMMENT")==0&&
769+
strncmp(te->tag,"DATABASE ",9)==0)||
770+
(strcmp(te->desc,"SECURITY LABEL")==0&&
771+
strncmp(te->tag,"DATABASE ",9)==0)))
758772
reqs=0;
759773

760774
/* Dump any relevant dump warnings to stderr */
@@ -2917,8 +2931,8 @@ _tocEntryRequired(TocEntry *te, teSection curSection, RestoreOptions *ropt)
29172931
* Special Case: If 'SEQUENCE SET' or anything to do with BLOBs, then
29182932
* it is considered a data entry. We don't need to check for the
29192933
* BLOBS entry or old-style BLOB COMMENTS, because they will have
2920-
* hadDumper = true ... but we do need to check new-style BLOB
2921-
* comments.
2934+
* hadDumper = true ... but we do need to check new-style BLOB ACLs,
2935+
* comments, etc.
29222936
*/
29232937
if (strcmp(te->desc,"SEQUENCE SET")==0||
29242938
strcmp(te->desc,"BLOB")==0||
@@ -3598,6 +3612,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
35983612
elseif (strcmp(te->desc,"CAST")==0||
35993613
strcmp(te->desc,"CHECK CONSTRAINT")==0||
36003614
strcmp(te->desc,"CONSTRAINT")==0||
3615+
strcmp(te->desc,"DATABASE PROPERTIES")==0||
36013616
strcmp(te->desc,"DEFAULT")==0||
36023617
strcmp(te->desc,"FK CONSTRAINT")==0||
36033618
strcmp(te->desc,"INDEX")==0||

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp