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

Commite1e17e2

Browse files
committed
Fix pg_dumpall so that when --clean is specified, it drops roles and
tablespaces in an order that has some chance of working.Per a complaint from Kevin Bailey.This is a pre-existing bug, but given the lack of prior complaints I'mnot sure it's worth back-patching. In most cases failure of the DROPcommands wouldn't be that important anyway.In passing, fix syntax errors in dumpCreateDB()'s queries for old servers;these were apparently introduced in recent binary_upgrade patch.
1 parent088ac58 commite1e17e2

File tree

1 file changed

+168
-28
lines changed

1 file changed

+168
-28
lines changed

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 168 additions & 28 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.123 2009/04/08 19:02:37 heikki Exp $
9+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.124 2009/04/11 20:23:05 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -33,10 +33,13 @@ static const char *progname;
3333

3434
staticvoidhelp(void);
3535

36+
staticvoiddropRoles(PGconn*conn);
3637
staticvoiddumpRoles(PGconn*conn);
3738
staticvoiddumpRoleMembership(PGconn*conn);
3839
staticvoiddumpGroups(PGconn*conn);
40+
staticvoiddropTablespaces(PGconn*conn);
3941
staticvoiddumpTablespaces(PGconn*conn);
42+
staticvoiddropDBs(PGconn*conn);
4043
staticvoiddumpCreateDB(PGconn*conn);
4144
staticvoiddumpDatabaseConfig(PGconn*conn,constchar*dbname);
4245
staticvoiddumpUserConfig(PGconn*conn,constchar*username);
@@ -54,7 +57,6 @@ static void executeCommand(PGconn *conn, const char *query);
5457

5558
staticcharpg_dump_bin[MAXPGPATH];
5659
staticPQExpBufferpgdumpopts;
57-
staticbooloutput_clean= false;
5860
staticboolskip_acls= false;
5961
staticboolverbose= false;
6062

@@ -82,6 +84,7 @@ main(int argc, char *argv[])
8284
enumtrivalueprompt_password=TRI_DEFAULT;
8385
booldata_only= false;
8486
boolglobals_only= false;
87+
booloutput_clean= false;
8588
boolroles_only= false;
8689
booltablespaces_only= false;
8790
boolschema_only= false;
@@ -90,8 +93,9 @@ main(int argc, char *argv[])
9093
constchar*std_strings;
9194
intc,
9295
ret;
96+
intoptindex;
9397

94-
structoptionlong_options[]= {
98+
staticstructoptionlong_options[]= {
9599
{"data-only",no_argument,NULL,'a'},
96100
{"clean",no_argument,NULL,'c'},
97101
{"file",required_argument,NULL,'f'},
@@ -130,8 +134,6 @@ main(int argc, char *argv[])
130134
{NULL,0,NULL,0}
131135
};
132136

133-
intoptindex;
134-
135137
set_pglocale_pgservice(argv[0],PG_TEXTDOMAIN("pg_dump"));
136138

137139
progname=get_progname(argv[0]);
@@ -442,16 +444,41 @@ main(int argc, char *argv[])
442444

443445
fprintf(OPF,"\\connect postgres\n\n");
444446

447+
/* Replicate encoding and std_strings in output */
448+
fprintf(OPF,"SET client_encoding = '%s';\n",
449+
pg_encoding_to_char(encoding));
450+
fprintf(OPF,"SET standard_conforming_strings = %s;\n",std_strings);
451+
if (strcmp(std_strings,"off")==0)
452+
fprintf(OPF,"SET escape_string_warning = off;\n");
453+
fprintf(OPF,"\n");
454+
445455
if (!data_only)
446456
{
447-
/* Replicate encoding and std_strings in output */
448-
fprintf(OPF,"SET client_encoding = '%s';\n",
449-
pg_encoding_to_char(encoding));
450-
fprintf(OPF,"SET standard_conforming_strings = %s;\n",std_strings);
451-
if (strcmp(std_strings,"off")==0)
452-
fprintf(OPF,"SET escape_string_warning = 'off';\n");
453-
fprintf(OPF,"\n");
457+
/*
458+
* If asked to --clean, do that first. We can avoid detailed
459+
* dependency analysis because databases never depend on each other,
460+
* and tablespaces never depend on each other. Roles could have
461+
* grants to each other, but DROP ROLE will clean those up silently.
462+
*/
463+
if (output_clean)
464+
{
465+
if (!globals_only&& !roles_only&& !tablespaces_only)
466+
dropDBs(conn);
454467

468+
if (!roles_only&& !no_tablespaces)
469+
{
470+
if (server_version >=80000)
471+
dropTablespaces(conn);
472+
}
473+
474+
if (!tablespaces_only)
475+
dropRoles(conn);
476+
}
477+
478+
/*
479+
* Now create objects as requested. Be careful that option logic
480+
* here is the same as for drops above.
481+
*/
455482
if (!tablespaces_only)
456483
{
457484
/* Dump roles (users) */
@@ -492,7 +519,6 @@ main(int argc, char *argv[])
492519
}
493520

494521

495-
496522
staticvoid
497523
help(void)
498524
{
@@ -541,6 +567,48 @@ help(void)
541567
}
542568

543569

570+
/*
571+
* Drop roles
572+
*/
573+
staticvoid
574+
dropRoles(PGconn*conn)
575+
{
576+
PGresult*res;
577+
inti_rolname;
578+
inti;
579+
580+
if (server_version >=80100)
581+
res=executeQuery(conn,
582+
"SELECT rolname "
583+
"FROM pg_authid "
584+
"ORDER BY 1");
585+
else
586+
res=executeQuery(conn,
587+
"SELECT usename as rolname "
588+
"FROM pg_shadow "
589+
"UNION "
590+
"SELECT groname as rolname "
591+
"FROM pg_group "
592+
"ORDER BY 1");
593+
594+
i_rolname=PQfnumber(res,"rolname");
595+
596+
if (PQntuples(res)>0)
597+
fprintf(OPF,"--\n-- Drop roles\n--\n\n");
598+
599+
for (i=0;i<PQntuples(res);i++)
600+
{
601+
constchar*rolename;
602+
603+
rolename=PQgetvalue(res,i,i_rolname);
604+
605+
fprintf(OPF,"DROP ROLE %s;\n",fmtId(rolename));
606+
}
607+
608+
PQclear(res);
609+
610+
fprintf(OPF,"\n\n");
611+
}
544612

545613
/*
546614
* Dump roles
@@ -637,14 +705,12 @@ dumpRoles(PGconn *conn)
637705

638706
resetPQExpBuffer(buf);
639707

640-
if (output_clean)
641-
appendPQExpBuffer(buf,"DROP ROLE %s;\n",fmtId(rolename));
642-
643708
/*
644709
* We dump CREATE ROLE followed by ALTER ROLE to ensure that the role
645-
* will acquire the right properties even if it already exists. (The
646-
* above DROP may therefore seem redundant, but it isn't really,
647-
* because this technique doesn't get rid of role memberships.)
710+
* will acquire the right properties even if it already exists (ie,
711+
* it won't hurt for the CREATE to fail). This is particularly
712+
* important for the role we are connected as, since even with --clean
713+
* we will have failed to drop it.
648714
*/
649715
appendPQExpBuffer(buf,"CREATE ROLE %s;\n",fmtId(rolename));
650716
appendPQExpBuffer(buf,"ALTER ROLE %s WITH",fmtId(rolename));
@@ -834,6 +900,40 @@ dumpGroups(PGconn *conn)
834900
fprintf(OPF,"\n\n");
835901
}
836902

903+
904+
/*
905+
* Drop tablespaces.
906+
*/
907+
staticvoid
908+
dropTablespaces(PGconn*conn)
909+
{
910+
PGresult*res;
911+
inti;
912+
913+
/*
914+
* Get all tablespaces except built-in ones (which we assume are named
915+
* pg_xxx)
916+
*/
917+
res=executeQuery(conn,"SELECT spcname "
918+
"FROM pg_catalog.pg_tablespace "
919+
"WHERE spcname !~ '^pg_' "
920+
"ORDER BY 1");
921+
922+
if (PQntuples(res)>0)
923+
fprintf(OPF,"--\n-- Drop tablespaces\n--\n\n");
924+
925+
for (i=0;i<PQntuples(res);i++)
926+
{
927+
char*spcname=PQgetvalue(res,i,0);
928+
929+
fprintf(OPF,"DROP TABLESPACE %s;\n",fmtId(spcname));
930+
}
931+
932+
PQclear(res);
933+
934+
fprintf(OPF,"\n\n");
935+
}
936+
837937
/*
838938
* Dump tablespaces.
839939
*/
@@ -880,9 +980,6 @@ dumpTablespaces(PGconn *conn)
880980
/* needed for buildACLCommands() */
881981
fspcname=strdup(fmtId(spcname));
882982

883-
if (output_clean)
884-
appendPQExpBuffer(buf,"DROP TABLESPACE %s;\n",fspcname);
885-
886983
appendPQExpBuffer(buf,"CREATE TABLESPACE %s",fspcname);
887984
appendPQExpBuffer(buf," OWNER %s",fmtId(spcowner));
888985

@@ -917,6 +1014,53 @@ dumpTablespaces(PGconn *conn)
9171014
fprintf(OPF,"\n\n");
9181015
}
9191016

1017+
1018+
/*
1019+
* Dump commands to drop each database.
1020+
*
1021+
* This should match the set of databases targeted by dumpCreateDB().
1022+
*/
1023+
staticvoid
1024+
dropDBs(PGconn*conn)
1025+
{
1026+
PGresult*res;
1027+
inti;
1028+
1029+
if (server_version >=70100)
1030+
res=executeQuery(conn,
1031+
"SELECT datname "
1032+
"FROM pg_database d "
1033+
"WHERE datallowconn ORDER BY 1");
1034+
else
1035+
res=executeQuery(conn,
1036+
"SELECT datname "
1037+
"FROM pg_database d "
1038+
"ORDER BY 1");
1039+
1040+
if (PQntuples(res)>0)
1041+
fprintf(OPF,"--\n-- Drop databases\n--\n\n");
1042+
1043+
for (i=0;i<PQntuples(res);i++)
1044+
{
1045+
char*dbname=PQgetvalue(res,i,0);
1046+
1047+
/*
1048+
* Skip "template1" and "postgres"; the restore script is almost
1049+
* certainly going to be run in one or the other, and we don't know
1050+
* which. This must agree with dumpCreateDB's choices!
1051+
*/
1052+
if (strcmp(dbname,"template1")!=0&&
1053+
strcmp(dbname,"postgres")!=0)
1054+
{
1055+
fprintf(OPF,"DROP DATABASE %s;\n",fmtId(dbname));
1056+
}
1057+
}
1058+
1059+
PQclear(res);
1060+
1061+
fprintf(OPF,"\n\n");
1062+
}
1063+
9201064
/*
9211065
* Dump commands to create each database.
9221066
*
@@ -984,7 +1128,7 @@ dumpCreateDB(PGconn *conn)
9841128
"(select usename from pg_shadow where usesysid=datdba), "
9851129
"(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
9861130
"pg_encoding_to_char(d.encoding), "
987-
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid"
1131+
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid,"
9881132
"datistemplate, '' as datacl, -1 as datconnlimit, "
9891133
"'pg_default' AS dattablespace "
9901134
"FROM pg_database d "
@@ -999,7 +1143,7 @@ dumpCreateDB(PGconn *conn)
9991143
"SELECT datname, "
10001144
"(select usename from pg_shadow where usesysid=datdba), "
10011145
"pg_encoding_to_char(d.encoding), "
1002-
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid"
1146+
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid,"
10031147
"'f' as datistemplate, "
10041148
"'' as datacl, -1 as datconnlimit, "
10051149
"'pg_default' AS dattablespace "
@@ -1033,9 +1177,6 @@ dumpCreateDB(PGconn *conn)
10331177
if (strcmp(dbname,"template1")!=0&&
10341178
strcmp(dbname,"postgres")!=0)
10351179
{
1036-
if (output_clean)
1037-
appendPQExpBuffer(buf,"DROP DATABASE %s;\n",fdbname);
1038-
10391180
appendPQExpBuffer(buf,"CREATE DATABASE %s",fdbname);
10401181

10411182
appendPQExpBuffer(buf," WITH TEMPLATE = template0");
@@ -1120,7 +1261,6 @@ dumpCreateDB(PGconn *conn)
11201261
}
11211262

11221263

1123-
11241264
/*
11251265
* Dump database-specific configuration
11261266
*/

0 commit comments

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