Skip to content

Commit 2364928

Browse files
peterepetere
committed
Dump ALTER DATABASE/USER ... SET ...
1 parent 4469f1a commit 2364928

File tree

1 file changed

+117
-2
lines changed

1 file changed

+117
-2
lines changed

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 117 additions & 2 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-
* $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.2 2002/08/27 21:33:41 petere Exp $
9+
* $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.3 2002/08/28 18:25:05 petere Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -36,10 +36,15 @@
3636
static char *progname;
3737

3838
static void help(void);
39+
3940
static void dumpUsers(PGconn *conn);
4041
static void dumpGroups(PGconn *conn);
4142
static void dumpCreateDB(PGconn *conn);
43+
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
44+
static void dumpUserConfig(PGconn *conn, const char *username);
45+
static void makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name);
4246
static void dumpDatabases(PGconn *conn);
47+
4348
static int runPgDump(const char *dbname);
4449
static PGconn *connectDatabase(const char *dbname, const char *pghost, const char *pgport,
4550
const char *pguser, bool require_password);
@@ -254,6 +259,7 @@ dumpUsers(PGconn *conn)
254259
PGresult *res;
255260
int i;
256261

262+
printf("--\n-- Users\n--\n\n");
257263
printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
258264

259265
res = executeQuery(conn,
@@ -264,9 +270,11 @@ dumpUsers(PGconn *conn)
264270
for (i = 0; i < PQntuples(res); i++)
265271
{
266272
PQExpBuffer buf = createPQExpBuffer();
273+
const char *username;
267274

275+
username = PQgetvalue(res, i, 0);
268276
appendPQExpBuffer(buf, "CREATE USER %s WITH SYSID %s",
269-
fmtId(PQgetvalue(res, i, 0)),
277+
fmtId(username),
270278
PQgetvalue(res, i, 1));
271279

272280
if (!PQgetisnull(res, i, 2))
@@ -292,6 +300,8 @@ dumpUsers(PGconn *conn)
292300

293301
printf("%s", buf->data);
294302
destroyPQExpBuffer(buf);
303+
304+
dumpUserConfig(conn, username);
295305
}
296306

297307
PQclear(res);
@@ -309,6 +319,7 @@ dumpGroups(PGconn *conn)
309319
PGresult *res;
310320
int i;
311321

322+
printf("--\n-- Groups\n--\n\n");
312323
printf("DELETE FROM pg_group;\n\n");
313324

314325
res = executeQuery(conn, "SELECT groname, grosysid, grolist FROM pg_group;");
@@ -374,6 +385,8 @@ dumpCreateDB(PGconn *conn)
374385
PGresult *res;
375386
int i;
376387

388+
printf("--\n-- Database creation\n--\n\n");
389+
377390
/* Basically this query returns: dbname, dbowner, encoding, istemplate, dbpath */
378391
res = executeQuery(conn, "SELECT datname, coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), pg_encoding_to_char(d.encoding), datistemplate, datpath FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) WHERE datallowconn ORDER BY 1;");
379392

@@ -414,6 +427,8 @@ dumpCreateDB(PGconn *conn)
414427
}
415428
printf("%s", buf->data);
416429
destroyPQExpBuffer(buf);
430+
431+
dumpDatabaseConfig(conn, dbname);
417432
}
418433

419434
PQclear(res);
@@ -422,6 +437,106 @@ dumpCreateDB(PGconn *conn)
422437

423438

424439

440+
/*
441+
* Dump database-specific configuration
442+
*/
443+
static void
444+
dumpDatabaseConfig(PGconn *conn, const char *dbname)
445+
{
446+
PQExpBuffer buf = createPQExpBuffer();
447+
int count = 1;
448+
449+
for(;;)
450+
{
451+
PGresult *res;
452+
453+
printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
454+
appendStringLiteral(buf, dbname, true);
455+
appendPQExpBuffer(buf, ";");
456+
457+
res = executeQuery(conn, buf->data);
458+
if (!PQgetisnull(res, 0, 0))
459+
{
460+
makeAlterConfigCommand(PQgetvalue(res, 0, 0), "DATABASE", dbname);
461+
PQclear(res);
462+
count++;
463+
}
464+
else
465+
{
466+
PQclear(res);
467+
break;
468+
}
469+
}
470+
471+
destroyPQExpBuffer(buf);
472+
}
473+
474+
475+
476+
/*
477+
* Dump user-specific configuration
478+
*/
479+
static void
480+
dumpUserConfig(PGconn *conn, const char *username)
481+
{
482+
PQExpBuffer buf = createPQExpBuffer();
483+
int count = 1;
484+
485+
for(;;)
486+
{
487+
PGresult *res;
488+
489+
printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
490+
appendStringLiteral(buf, username, true);
491+
appendPQExpBuffer(buf, ";");
492+
493+
res = executeQuery(conn, buf->data);
494+
if (!PQgetisnull(res, 0, 0))
495+
{
496+
makeAlterConfigCommand(PQgetvalue(res, 0, 0), "USER", username);
497+
PQclear(res);
498+
count++;
499+
}
500+
else
501+
{
502+
PQclear(res);
503+
break;
504+
}
505+
}
506+
507+
destroyPQExpBuffer(buf);
508+
}
509+
510+
511+
512+
/*
513+
* Helper function for dumpXXXConfig().
514+
*/
515+
static void
516+
makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name)
517+
{
518+
char *pos;
519+
char *mine;
520+
PQExpBuffer buf = createPQExpBuffer();
521+
522+
mine = strdup(arrayitem);
523+
pos = strchr(mine, '=');
524+
if (pos == NULL)
525+
return;
526+
527+
*pos = 0;
528+
appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
529+
appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
530+
appendStringLiteral(buf, pos + 1, false);
531+
appendPQExpBuffer(buf, ";\n");
532+
533+
printf("%s", buf->data);
534+
destroyPQExpBuffer(buf);
535+
free(mine);
536+
}
537+
538+
539+
425540
/*
426541
* Dump contents of databases.
427542
*/

0 commit comments

Comments
 (0)