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

Commit4599521

Browse files
committed
Here is another patch that fixes a stack of pg_dump bugs:
* Fix help text ordering* Add back --set-session-authorization to pg_dumpall. Updated the docsfor that. Updated help for that.* Dump ALTER USER commands for the cluster owner ("pgsql"). These aredumped AFTER the create user and create database commands in case thepermissions to do these have been revoked.* Dump ALTER OWNER for public schema (because it's possible to changeit). This was done by adding TOC entries for the public schema, andfiltering them out at archiver time. I also save the owner in the TOCentry just for the public schema.* Suppress dumping single quotes around schema_path and DateStyleoptions when they are set using ALTER USER or ALTER DATABASE. Added acomment to the steps in guc.c to remind people to update that list.* Fix dumping in --clean mode against a pre-7.3 server. It just setsall drop statements to assume the public schema, allowing it to restorewithout error.* Cleaned up text output. eg. Don't output -- Tablespaces comment ifthere are none. Same for groups and users.* Make the commands to DELETE FROM pg_shadow and DELETE FROM pg_grouponly be output when -c mode is enabled. I'm not sure why that hasn'tbeen done before?!?!This should be good for application asap, after which I will start onregression dumping 7.0-7.4 databases.Christopher Kings-Lynne
1 parent465edca commit4599521

File tree

5 files changed

+104
-77
lines changed

5 files changed

+104
-77
lines changed

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

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.45 2004/07/12 14:35:43 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v 1.46 2004/07/19 21:39:46 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -280,11 +280,14 @@ PostgreSQL documentation
280280
<term><option>--use-set-session-authorization</></term>
281281
<listitem>
282282
<para>
283-
This option is obsolete but still accepted for backwards
284-
compatibility with <application>pg_dump</application>.
283+
Output SQL standard SET SESSION AUTHORIZATION commands instead
284+
of OWNER TO commands. This makes the dump more standards compatible,
285+
but depending on the history of the objects in the dump, may not
286+
restore properly.
285287
</para>
286288
</listitem>
287289
</varlistentry>
290+
288291
</variablelist>
289292
</para>
290293

‎src/backend/utils/misc/guc.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.220 2004/07/1902:47:10 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.221 2004/07/1921:39:47 momjian Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -335,6 +335,9 @@ const char *const config_type_names[] =
335335
* 6. Add it to src/bin/psql/tab-complete.c, if it's a USERSET option.
336336
*
337337
* 7. Don't forget to document the option.
338+
*
339+
* 8. If it's a new GUC_LIST option you must edit pg_dumpall.c to ensure
340+
* it is not single quoted at dump time.
338341
*/
339342

340343

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 14 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
*$PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.89 2004/07/19 21:02:17 tgl Exp $
18+
*$PostgreSQL: pgsql/src/bin/pg_dump/pg_backup_archiver.c,v 1.90 2004/07/19 21:39:47 momjian Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2356,7 +2356,8 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
23562356
strcmp(te->desc,"TABLE")==0||
23572357
strcmp(te->desc,"TYPE")==0||
23582358
strcmp(te->desc,"VIEW")==0||
2359-
strcmp(te->desc,"SEQUENCE")==0
2359+
strcmp(te->desc,"SEQUENCE")==0||
2360+
(strcmp(te->desc,"SCHEMA")==0&&strcmp(te->tag,"public")==0)/* Only public schema */
23602361
))
23612362
{
23622363
char*temp=_getObjectFromDropStmt(te->dropStmt,te->desc);
@@ -2376,15 +2377,18 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
23762377
/*
23772378
* Really crude hack for suppressing AUTHORIZATION clause of CREATE SCHEMA
23782379
* when --no-owner mode is selected. This is ugly, but I see no other
2379-
* good way ...
2380+
* good way ... Also, avoid dumping the public schema as it will already be
2381+
* created.
23802382
*/
2381-
if (AH->ropt&&AH->ropt->noOwner&&strcmp(te->desc,"SCHEMA")==0)
2382-
{
2383-
ahprintf(AH,"CREATE SCHEMA %s;\n\n\n",te->tag);
2384-
}
2385-
else
2386-
{
2387-
ahprintf(AH,"%s\n\n",te->defn);
2383+
if (strcmp(te->tag,"public")!=0) {
2384+
if (AH->ropt&&AH->ropt->noOwner&&strcmp(te->desc,"SCHEMA")==0)
2385+
{
2386+
ahprintf(AH,"CREATE SCHEMA %s;\n\n\n",te->tag);
2387+
}
2388+
else
2389+
{
2390+
ahprintf(AH,"%s\n\n",te->defn);
2391+
}
23882392
}
23892393
}
23902394
elseif (isData) {

‎src/bin/pg_dump/pg_dump.c

Lines changed: 16 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
*by PostgreSQL
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.379 2004/07/13 03:00:17 momjian Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.380 2004/07/19 21:39:48 momjian Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -1492,7 +1492,7 @@ getNamespaces(int *numNamespaces)
14921492
nsinfo[0].dobj.catId.tableoid=0;
14931493
nsinfo[0].dobj.catId.oid=0;
14941494
AssignDumpId(&nsinfo[0].dobj);
1495-
nsinfo[0].dobj.name=strdup("");
1495+
nsinfo[0].dobj.name=strdup("public");
14961496
nsinfo[0].usename=strdup("");
14971497
nsinfo[0].nspacl=strdup("");
14981498
nsinfo[0].nsptablespace=strdup("");
@@ -4381,39 +4381,31 @@ dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
43814381
qnspname=strdup(fmtId(nspinfo->dobj.name));
43824382

43834383
/*
4384-
* If it's the PUBLIC namespace, suppress the CREATE SCHEMA record
4385-
* for it, since we expect PUBLIC to exist already in the
4386-
* destination database. But do emit ACL in case it's not standard,
4387-
* likewise comment.
4388-
*
43894384
* Note that ownership is shown in the AUTHORIZATION clause,
43904385
* while the archive entry is listed with empty owner (causing
43914386
* it to be emitted with SET SESSION AUTHORIZATION DEFAULT).
43924387
* This seems the best way of dealing with schemas owned by
43934388
* users without CREATE SCHEMA privilege. Further hacking has
43944389
* to be applied for --no-owner mode, though!
43954390
*/
4396-
if (strcmp(nspinfo->dobj.name,"public")!=0)
4397-
{
4398-
appendPQExpBuffer(delq,"DROP SCHEMA %s;\n",qnspname);
4391+
appendPQExpBuffer(delq,"DROP SCHEMA %s;\n",qnspname);
43994392

4400-
appendPQExpBuffer(q,"CREATE SCHEMA %s AUTHORIZATION %s",
4401-
qnspname,fmtId(nspinfo->usename));
4393+
appendPQExpBuffer(q,"CREATE SCHEMA %s AUTHORIZATION %s",
4394+
qnspname,fmtId(nspinfo->usename));
44024395

4403-
/* Add tablespace qualifier, if not default */
4404-
if (strlen(nspinfo->nsptablespace)!=0)
4405-
appendPQExpBuffer(q," TABLESPACE %s",
4406-
fmtId(nspinfo->nsptablespace));
4396+
/* Add tablespace qualifier, if not default */
4397+
if (strlen(nspinfo->nsptablespace)!=0)
4398+
appendPQExpBuffer(q," TABLESPACE %s",
4399+
fmtId(nspinfo->nsptablespace));
44074400

4408-
appendPQExpBuffer(q,";\n");
4401+
appendPQExpBuffer(q,";\n");
44094402

4410-
ArchiveEntry(fout,nspinfo->dobj.catId,nspinfo->dobj.dumpId,
4411-
nspinfo->dobj.name,
4412-
NULL,"",
4413-
false,"SCHEMA",q->data,delq->data,NULL,
4414-
nspinfo->dobj.dependencies,nspinfo->dobj.nDeps,
4415-
NULL,NULL);
4416-
}
4403+
ArchiveEntry(fout,nspinfo->dobj.catId,nspinfo->dobj.dumpId,
4404+
nspinfo->dobj.name,
4405+
NULL,strcmp(nspinfo->dobj.name,"public")==0 ?nspinfo->usename :"",
4406+
false,"SCHEMA",q->data,delq->data,NULL,
4407+
nspinfo->dobj.dependencies,nspinfo->dobj.nDeps,
4408+
NULL,NULL);
44174409

44184410
/* Dump Schema Comments */
44194411
resetPQExpBuffer(q);

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 64 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1994, Regents of the University of California
77
*
88
*
9-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.44 2004/07/12 14:35:45 momjian Exp $
9+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.45 2004/07/19 21:39:48 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -44,7 +44,7 @@ static const char *progname;
4444

4545
staticvoidhelp(void);
4646

47-
staticvoiddumpUsers(PGconn*conn);
47+
staticvoiddumpUsers(PGconn*conn,boolinitdbonly);
4848
staticvoiddumpGroups(PGconn*conn);
4949
staticvoiddumpTablespaces(PGconn*conn);
5050
staticvoiddumpCreateDB(PGconn*conn);
@@ -257,7 +257,7 @@ main(int argc, char *argv[])
257257
if (disable_triggers)
258258
appendPQExpBuffer(pgdumpopts," -X disable-triggers");
259259
if (use_setsessauth)
260-
/* no-op, still allowed for compatibility */;
260+
appendPQExpBuffer(pgdumpopts," -X use-set-session-authorization");
261261

262262
if (optind<argc)
263263
{
@@ -279,18 +279,19 @@ main(int argc, char *argv[])
279279

280280
if (!data_only)
281281
{
282-
dumpUsers(conn);
282+
/* Dump all users excluding the initdb user */
283+
dumpUsers(conn, false);
283284
dumpGroups(conn);
284285
if (server_version >=70500)
285286
dumpTablespaces(conn);
287+
if (!globals_only)
288+
dumpCreateDB(conn);
289+
/* Dump alter command for initdb user */
290+
dumpUsers(conn, true);
286291
}
287292

288293
if (!globals_only)
289-
{
290-
if (!data_only)
291-
dumpCreateDB(conn);
292294
dumpDatabases(conn);
293-
}
294295

295296
PQfinish(conn);
296297

@@ -310,26 +311,29 @@ help(void)
310311
printf(_("Usage:\n"));
311312
printf(_(" %s [OPTION]...\n"),progname);
312313

313-
printf(_("\nOptions:\n"));
314+
printf(_("\nGeneral options:\n"));
315+
printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
316+
" pg_dumpall version\n"));
317+
printf(_(" --help show this help, then exit\n"));
318+
printf(_(" --version output version information, then exit\n"));
319+
printf(_("\nOptions controlling the output content:\n"));
314320
printf(_(" -a, --data-only dump only the data, not the schema\n"));
315321
printf(_(" -c, --clean clean (drop) databases prior to create\n"));
316322
printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
317323
printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
318324
printf(_(" -g, --globals-only dump only global objects, no databases\n"));
319-
printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
320-
" pg_dumpall version\n"));
321-
printf(_(" -s, --schema-only dump only the schema, no data\n"));
322-
printf(_(" -S, --superuser=NAME specify the superuser user name to use in the dump\n"));
323325
printf(_(" -o, --oids include OIDs in dump\n"));
324326
printf(_(" -O, --no-owner do not output commands to set object ownership\n"));
325-
printf(_(" -v, --verbose verbose mode\n"));
327+
printf(_(" -s, --schema-only dump only the schema, no data\n"));
328+
printf(_(" -S, --superuser=NAME specify the superuser user name to use in the dump\n"));
326329
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
327330
printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n"
328331
" disable dollar quoting, use SQL standard quoting\n"));
329332
printf(_(" -X disable-triggers, --disable-triggers\n"
330-
" disable triggers during data-only restore\n"));
331-
printf(_(" --help show this help, then exit\n"));
332-
printf(_(" --version output version information, then exit\n"));
333+
" disable triggers during data-only restore\n"));
334+
printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n"
335+
" use SESSION AUTHORIZATION commands instead of\n"
336+
" OWNER TO commands\n"));
333337

334338
printf(_("\nConnection options:\n"));
335339
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -344,39 +348,52 @@ help(void)
344348

345349

346350
/*
347-
* Dump users (but not the user created by initdb).
351+
* Dump users
352+
* Is able to dump all non initdb users or just the initdb user.
348353
*/
349354
staticvoid
350-
dumpUsers(PGconn*conn)
355+
dumpUsers(PGconn*conn,boolinitdbonly)
351356
{
352357
PGresult*res;
353358
inti;
354359

355-
printf("--\n-- Users\n--\n\n");
356-
printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
357-
358360
if (server_version >=70100)
359361
res=executeQuery(conn,
360362
"SELECT usename, usesysid, passwd, usecreatedb, "
361-
"usesuper, valuntil "
362-
"FROMpg_shadow "
363-
"WHERE usesysid <> (SELECT datdbaFROMpg_database WHERE datname = 'template0')");
363+
"usesuper, valuntil, "
364+
"(usesysid = (SELECT datdbaFROMpg_database WHERE datname = 'template0')) AS clusterowner "
365+
"FROMpg_shadow");
364366
else
365367
res=executeQuery(conn,
366368
"SELECT usename, usesysid, passwd, usecreatedb, "
367-
"usesuper, valuntil "
368-
"FROM pg_shadow "
369-
"WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template1')");
369+
"usesuper, valuntil, "
370+
"(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner "
371+
"FROM pg_shadow");
372+
373+
if (PQntuples(res)>0|| (!initdbonly&&output_clean))
374+
printf("--\n-- Users\n--\n\n");
375+
if (!initdbonly&&output_clean)
376+
printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
370377

371378
for (i=0;i<PQntuples(res);i++)
372379
{
373-
PQExpBufferbuf=createPQExpBuffer();
374380
constchar*username;
375-
381+
boolclusterowner;
382+
PQExpBufferbuf=createPQExpBuffer();
376383
username=PQgetvalue(res,i,0);
377-
appendPQExpBuffer(buf,"CREATE USER %s WITH SYSID %s",
378-
fmtId(username),
379-
PQgetvalue(res,i,1));
384+
clusterowner= (strcmp(PQgetvalue(res,i,6),"t")==0);
385+
386+
/* Check which pass we're on */
387+
if ((initdbonly&& !clusterowner)|| (!initdbonly&&clusterowner))continue;
388+
389+
/* Dump ALTER USER for the cluster owner and CREATE USER for all other users */
390+
if (!clusterowner)
391+
appendPQExpBuffer(buf,"CREATE USER %s WITH SYSID %s",
392+
fmtId(username),
393+
PQgetvalue(res,i,1));
394+
else
395+
appendPQExpBuffer(buf,"ALTER USER %s WITH",
396+
fmtId(username));
380397

381398
if (!PQgetisnull(res,i,2))
382399
{
@@ -422,11 +439,13 @@ dumpGroups(PGconn *conn)
422439
PGresult*res;
423440
inti;
424441

425-
printf("--\n-- Groups\n--\n\n");
426-
printf("DELETE FROM pg_group;\n\n");
427-
428442
res=executeQuery(conn,"SELECT groname, grosysid, grolist FROM pg_group");
429443

444+
if (PQntuples(res)>0||output_clean)
445+
printf("--\n-- Groups\n--\n\n");
446+
if (output_clean)
447+
printf("DELETE FROM pg_group;\n\n");
448+
430449
for (i=0;i<PQntuples(res);i++)
431450
{
432451
PQExpBufferbuf=createPQExpBuffer();
@@ -478,8 +497,6 @@ dumpTablespaces(PGconn *conn)
478497
PGresult*res;
479498
inti;
480499

481-
printf("--\n-- Tablespaces\n--\n\n");
482-
483500
/*
484501
* Get all tablespaces except built-in ones (which we assume are named
485502
* pg_xxx)
@@ -489,6 +506,9 @@ dumpTablespaces(PGconn *conn)
489506
"spclocation, spcacl "
490507
"FROM pg_catalog.pg_tablespace "
491508
"WHERE spcname NOT LIKE 'pg\\_%'");
509+
510+
if (PQntuples(res)>0)
511+
printf("--\n-- Tablespaces\n--\n\n");
492512

493513
for (i=0;i<PQntuples(res);i++)
494514
{
@@ -758,7 +778,12 @@ makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name
758778
*pos=0;
759779
appendPQExpBuffer(buf,"ALTER %s %s ",type,fmtId(name));
760780
appendPQExpBuffer(buf,"SET %s TO ",fmtId(mine));
761-
appendStringLiteral(buf,pos+1, false);
781+
/* Some GUC variable names are 'LIST' type and hence must not be quoted. */
782+
if (strcasecmp(mine,"DateStyle")==0
783+
||strcasecmp(mine,"search_path")==0)
784+
appendPQExpBuffer(buf,"%s",pos+1);
785+
else
786+
appendStringLiteral(buf,pos+1, false);
762787
appendPQExpBuffer(buf,";\n");
763788

764789
printf("%s",buf->data);

0 commit comments

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