pg_dumpall.c 40.9 KB
Newer Older
1 2
/*-------------------------------------------------------------------------
 *
3
 * pg_dumpall.c
4
 *
Bruce Momjian's avatar
Bruce Momjian committed
5
 * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
6 7 8
 * Portions Copyright (c) 1994, Regents of the University of California
 *
 *
9
 * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.118 2009/02/26 16:02:38 petere Exp $
10 11 12 13 14 15
 *
 *-------------------------------------------------------------------------
 */

#include "postgres_fe.h"

16
#include <time.h>
17
#include <unistd.h>
18

19 20 21 22
#ifdef ENABLE_NLS
#include <locale.h>
#endif

23
#include "getopt_long.h"
24

25
#ifndef HAVE_INT_OPTRESET
Bruce Momjian's avatar
Bruce Momjian committed
26
int			optreset;
27 28
#endif

29
#include "dumputils.h"
30
#include "pg_backup.h"
31

32 33
/* version string we expect back from pg_dump */
#define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
34

35

36
static const char *progname;
37 38

static void help(void);
39

40
static void dumpRoles(PGconn *conn);
41
static void dumpRoleMembership(PGconn *conn);
42
static void dumpGroups(PGconn *conn);
43
static void dumpTablespaces(PGconn *conn);
44
static void dumpCreateDB(PGconn *conn);
45 46
static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
static void dumpUserConfig(PGconn *conn, const char *username);
47
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
Bruce Momjian's avatar
Bruce Momjian committed
48
					   const char *type, const char *name);
49
static void dumpDatabases(PGconn *conn);
50
static void dumpTimestamp(char *msg);
51
static void doShellQuoting(PQExpBuffer buf, const char *str);
52

Bruce Momjian's avatar
Bruce Momjian committed
53
static int	runPgDump(const char *dbname);
54
static PGconn *connectDatabase(const char *dbname, const char *pghost, const char *pgport,
55
			  const char *pguser, enum trivalue prompt_password, bool fail_on_error);
56
static PGresult *executeQuery(PGconn *conn, const char *query);
57 58
static void executeCommand(PGconn *conn, const char *query);

59
static char pg_dump_bin[MAXPGPATH];
60
static PQExpBuffer pgdumpopts;
61 62 63 64 65 66
static bool output_clean = false;
static bool skip_acls = false;
static bool verbose = false;

static int	disable_dollar_quoting = 0;
static int	disable_triggers = 0;
67
static int	no_tablespaces = 0;
68 69
static int	use_setsessauth = 0;
static int	server_version;
70

Bruce Momjian's avatar
Bruce Momjian committed
71 72
static FILE *OPF;
static char *filename = NULL;
73

74 75
static int	binary_upgrade = 0;

76 77 78
int
main(int argc, char *argv[])
{
Bruce Momjian's avatar
Bruce Momjian committed
79 80 81 82
	char	   *pghost = NULL;
	char	   *pgport = NULL;
	char	   *pguser = NULL;
	char	   *pgdb = NULL;
83
	char	   *use_role = NULL;
84
	enum trivalue prompt_password = TRI_DEFAULT;
85
	bool		data_only = false;
86
	bool		globals_only = false;
87 88
	bool		roles_only = false;
	bool		tablespaces_only = false;
89
	bool		schema_only = false;
Bruce Momjian's avatar
Bruce Momjian committed
90
	PGconn	   *conn;
91
	int			encoding;
Bruce Momjian's avatar
Bruce Momjian committed
92
	const char *std_strings;
Bruce Momjian's avatar
Bruce Momjian committed
93 94
	int			c,
				ret;
95

96 97
	struct option long_options[] = {
		{"binary-upgrade", no_argument, &binary_upgrade, 1},	/* not documented */
98
		{"data-only", no_argument, NULL, 'a'},
99 100 101 102
		{"clean", no_argument, NULL, 'c'},
		{"inserts", no_argument, NULL, 'd'},
		{"attribute-inserts", no_argument, NULL, 'D'},
		{"column-inserts", no_argument, NULL, 'D'},
103
		{"file", required_argument, NULL, 'f'},
104
		{"globals-only", no_argument, NULL, 'g'},
105 106
		{"host", required_argument, NULL, 'h'},
		{"ignore-version", no_argument, NULL, 'i'},
107
		{"database", required_argument, NULL, 'l'},
108
		{"oids", no_argument, NULL, 'o'},
109
		{"no-owner", no_argument, NULL, 'O'},
110
		{"port", required_argument, NULL, 'p'},
111
		{"roles-only", no_argument, NULL, 'r'},
112
		{"schema-only", no_argument, NULL, 's'},
113
		{"superuser", required_argument, NULL, 'S'},
114
		{"tablespaces-only", no_argument, NULL, 't'},
115 116
		{"username", required_argument, NULL, 'U'},
		{"verbose", no_argument, NULL, 'v'},
117
		{"no-password", no_argument, NULL, 'w'},
118
		{"password", no_argument, NULL, 'W'},
119 120
		{"no-privileges", no_argument, NULL, 'x'},
		{"no-acl", no_argument, NULL, 'x'},
121 122

		/*
123
		 * the following options don't have an equivalent short option letter
124 125 126
		 */
		{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
		{"disable-triggers", no_argument, &disable_triggers, 1},
127
		{"lock-wait-timeout", required_argument, NULL, 2},
128
		{"no-tablespaces", no_argument, &no_tablespaces, 1},
129
		{"role", required_argument, NULL, 3},
130 131
		{"use-set-session-authorization", no_argument, &use_setsessauth, 1},

132 133 134 135 136
		{NULL, 0, NULL, 0}
	};

	int			optindex;

137
	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_dump"));
138

139
	progname = get_progname(argv[0]);
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154

	if (argc > 1)
	{
		if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
		{
			help();
			exit(0);
		}
		if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
		{
			puts("pg_dumpall (PostgreSQL) " PG_VERSION);
			exit(0);
		}
	}

155
	if ((ret = find_other_exec(argv[0], "pg_dump", PGDUMP_VERSIONSTR,
156
							   pg_dump_bin)) < 0)
157
	{
158
		char		full_path[MAXPGPATH];
159 160

		if (find_my_exec(argv[0], full_path) < 0)
161
			strlcpy(full_path, progname, sizeof(full_path));
162

163 164
		if (ret == -1)
			fprintf(stderr,
Bruce Momjian's avatar
Bruce Momjian committed
165
					_("The program \"pg_dump\" is needed by %s "
166 167
					  "but was not found in the\n"
					  "same directory as \"%s\".\n"
Bruce Momjian's avatar
Bruce Momjian committed
168
					  "Check your installation.\n"),
169
					progname, full_path);
170 171
		else
			fprintf(stderr,
172 173
					_("The program \"pg_dump\" was found by \"%s\"\n"
					  "but was not the same version as %s.\n"
Bruce Momjian's avatar
Bruce Momjian committed
174
					  "Check your installation.\n"),
175
					full_path, progname);
176 177 178
		exit(1);
	}

179 180
	pgdumpopts = createPQExpBuffer();

181
	while ((c = getopt_long(argc, argv, "acdDf:gh:il:oOp:rsS:tU:vwWxX:", long_options, &optindex)) != -1)
182 183 184
	{
		switch (c)
		{
185 186 187 188 189
			case 'a':
				data_only = true;
				appendPQExpBuffer(pgdumpopts, " -a");
				break;

190 191 192 193 194 195 196 197
			case 'c':
				output_clean = true;
				break;

			case 'd':
			case 'D':
				appendPQExpBuffer(pgdumpopts, " -%c", c);
				break;
Bruce Momjian's avatar
Bruce Momjian committed
198

199 200
			case 'f':
				filename = optarg;
201 202
				appendPQExpBuffer(pgdumpopts, " -f ");
				doShellQuoting(pgdumpopts, filename);
203
				break;
204 205 206 207 208 209 210

			case 'g':
				globals_only = true;
				break;

			case 'h':
				pghost = optarg;
211 212
				appendPQExpBuffer(pgdumpopts, " -h ");
				doShellQuoting(pgdumpopts, pghost);
213 214 215
				break;

			case 'i':
216
				/* ignored, deprecated option */
217
				break;
Bruce Momjian's avatar
Bruce Momjian committed
218

219 220 221
			case 'l':
				pgdb = optarg;
				break;
222

223
			case 'o':
224
				appendPQExpBuffer(pgdumpopts, " -o");
225 226
				break;

227 228 229 230
			case 'O':
				appendPQExpBuffer(pgdumpopts, " -O");
				break;

231 232
			case 'p':
				pgport = optarg;
233 234
				appendPQExpBuffer(pgdumpopts, " -p ");
				doShellQuoting(pgdumpopts, pgport);
235
				break;
Bruce Momjian's avatar
Bruce Momjian committed
236

237 238 239
			case 'r':
				roles_only = true;
				break;
240

241 242 243 244 245
			case 's':
				schema_only = true;
				appendPQExpBuffer(pgdumpopts, " -s");
				break;

246
			case 'S':
247 248
				appendPQExpBuffer(pgdumpopts, " -S ");
				doShellQuoting(pgdumpopts, optarg);
249
				break;
Bruce Momjian's avatar
Bruce Momjian committed
250

251 252 253
			case 't':
				tablespaces_only = true;
				break;
254

255 256
			case 'U':
				pguser = optarg;
257 258
				appendPQExpBuffer(pgdumpopts, " -U ");
				doShellQuoting(pgdumpopts, pguser);
259 260 261 262 263 264 265
				break;

			case 'v':
				verbose = true;
				appendPQExpBuffer(pgdumpopts, " -v");
				break;

266 267 268 269 270
			case 'w':
				prompt_password = TRI_NO;
				appendPQExpBuffer(pgdumpopts, " -w");
				break;

271
			case 'W':
272
				prompt_password = TRI_YES;
273 274 275
				appendPQExpBuffer(pgdumpopts, " -W");
				break;

276 277 278 279 280
			case 'x':
				skip_acls = true;
				appendPQExpBuffer(pgdumpopts, " -x");
				break;

281
			case 'X':
282
				/* -X is a deprecated alternative to long options */
283
				if (strcmp(optarg, "disable-dollar-quoting") == 0)
284
					disable_dollar_quoting = 1;
285
				else if (strcmp(optarg, "disable-triggers") == 0)
286 287 288
					disable_triggers = 1;
				else if (strcmp(optarg, "no-tablespaces") == 0) 
					no_tablespaces = 1;
289
				else if (strcmp(optarg, "use-set-session-authorization") == 0)
290
					use_setsessauth = 1;
291 292 293 294 295 296 297 298 299 300 301 302 303
				else
				{
					fprintf(stderr,
							_("%s: invalid -X option -- %s\n"),
							progname, optarg);
					fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
					exit(1);
				}
				break;

			case 0:
				break;

304
			case 2:
305 306 307 308 309 310 311 312
				appendPQExpBuffer(pgdumpopts, " --lock-wait-timeout ");
				doShellQuoting(pgdumpopts, optarg);
				break;

			case 3:
				use_role = optarg;
				appendPQExpBuffer(pgdumpopts, " --role ");
				doShellQuoting(pgdumpopts, use_role);
313 314
				break;

Bruce Momjian's avatar
Bruce Momjian committed
315
			default:
316
				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
Bruce Momjian's avatar
Bruce Momjian committed
317
				exit(1);
318 319 320
		}
	}

Bruce Momjian's avatar
Bruce Momjian committed
321
	/* Add long options to the pg_dump argument list */
322 323
	if (binary_upgrade)
		appendPQExpBuffer(pgdumpopts, " --binary-upgrade");
324
	if (disable_dollar_quoting)
325
		appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting");
326
	if (disable_triggers)
327
		appendPQExpBuffer(pgdumpopts, " --disable-triggers");
328 329
	if (no_tablespaces)
		appendPQExpBuffer(pgdumpopts, " --no-tablespaces");
330
	if (use_setsessauth)
331
		appendPQExpBuffer(pgdumpopts, " --use-set-session-authorization");
Bruce Momjian's avatar
Bruce Momjian committed
332

Bruce Momjian's avatar
Bruce Momjian committed
333 334
	if (optind < argc)
	{
335 336 337 338
		fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
				progname, argv[optind]);
		fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
				progname);
Bruce Momjian's avatar
Bruce Momjian committed
339 340
		exit(1);
	}
Bruce Momjian's avatar
Bruce Momjian committed
341

342 343 344
	/* Make sure the user hasn't specified a mix of globals-only options */
	if (globals_only && roles_only)
	{
Peter Eisentraut's avatar
Peter Eisentraut committed
345
		fprintf(stderr, _("%s: options -g/--globals-only and -r/--roles-only cannot be used together\n"),
346 347 348 349 350
				progname);
		fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
				progname);
		exit(1);
	}
Bruce Momjian's avatar
Bruce Momjian committed
351

352 353
	if (globals_only && tablespaces_only)
	{
Peter Eisentraut's avatar
Peter Eisentraut committed
354
		fprintf(stderr, _("%s: options -g/--globals-only and -t/--tablespaces-only cannot be used together\n"),
355 356 357 358 359
				progname);
		fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
				progname);
		exit(1);
	}
Bruce Momjian's avatar
Bruce Momjian committed
360

361 362
	if (roles_only && tablespaces_only)
	{
Peter Eisentraut's avatar
Peter Eisentraut committed
363
		fprintf(stderr, _("%s: options -r/--roles-only and -t/--tablespaces-only cannot be used together\n"),
364 365 366 367 368
				progname);
		fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
				progname);
		exit(1);
	}
369

370
	/*
371 372
	 * If there was a database specified on the command line, use that,
	 * otherwise try to connect to database "postgres", and failing that
373 374 375
	 * "template1".  "postgres" is the preferred choice for 8.1 and later
	 * servers, but it usually will not exist on older ones.
	 */
376 377 378
	if (pgdb)
	{
		conn = connectDatabase(pgdb, pghost, pgport, pguser,
379
							   prompt_password, false);
Bruce Momjian's avatar
Bruce Momjian committed
380

381 382 383 384 385 386 387 388 389 390
		if (!conn)
		{
			fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
					progname, pgdb);
			exit(1);
		}
	}
	else
	{
		conn = connectDatabase("postgres", pghost, pgport, pguser,
391
							   prompt_password, false);
392 393
		if (!conn)
			conn = connectDatabase("template1", pghost, pgport, pguser,
394
								   prompt_password, true);
Bruce Momjian's avatar
Bruce Momjian committed
395

396 397
		if (!conn)
		{
Peter Eisentraut's avatar
Peter Eisentraut committed
398
			fprintf(stderr, _("%s: could not connect to databases \"postgres\" or \"template1\"\n"
Bruce Momjian's avatar
Bruce Momjian committed
399
							  "Please specify an alternative database.\n"),
400 401 402 403 404 405
					progname);
			fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
					progname);
			exit(1);
		}
	}
Bruce Momjian's avatar
Bruce Momjian committed
406

407 408 409 410 411 412 413 414
	/*
	 * Open the output file if required, otherwise use stdout
	 */
	if (filename)
	{
		OPF = fopen(filename, PG_BINARY_W);
		if (!OPF)
		{
415 416
			fprintf(stderr, _("%s: could not open the output file \"%s\": %s\n"),
					progname, filename, strerror(errno));
417 418 419 420 421
			exit(1);
		}
	}
	else
		OPF = stdout;
422

423
	/*
Bruce Momjian's avatar
Bruce Momjian committed
424 425
	 * Get the active encoding and the standard_conforming_strings setting, so
	 * we know how to escape strings.
426 427 428 429 430 431
	 */
	encoding = PQclientEncoding(conn);
	std_strings = PQparameterStatus(conn, "standard_conforming_strings");
	if (!std_strings)
		std_strings = "off";

432 433 434 435 436 437 438 439 440 441
	/* Set the role if requested */
	if (use_role && server_version >= 80100)
	{
		PQExpBuffer query = createPQExpBuffer();

		appendPQExpBuffer(query, "SET ROLE %s", fmtId(use_role));
		executeCommand(conn, query->data);
		destroyPQExpBuffer(query);
	}

442
	fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
443
	if (verbose)
Bruce Momjian's avatar
Bruce Momjian committed
444
		dumpTimestamp("Started on");
445

446
	fprintf(OPF, "\\connect postgres\n\n");
447

448 449
	if (!data_only)
	{
450
		/* Replicate encoding and std_strings in output */
451
		fprintf(OPF, "SET client_encoding = '%s';\n",
Bruce Momjian's avatar
Bruce Momjian committed
452
				pg_encoding_to_char(encoding));
453
		fprintf(OPF, "SET standard_conforming_strings = %s;\n", std_strings);
Bruce Momjian's avatar
Bruce Momjian committed
454
		if (strcmp(std_strings, "off") == 0)
455 456
			fprintf(OPF, "SET escape_string_warning = 'off';\n");
		fprintf(OPF, "\n");
457

458 459 460 461 462 463 464 465 466 467 468
		if (!tablespaces_only)
		{
			/* Dump roles (users) */
			dumpRoles(conn);

			/* Dump role memberships --- need different method for pre-8.1 */
			if (server_version >= 80100)
				dumpRoleMembership(conn);
			else
				dumpGroups(conn);
		}
469

470
		if (!roles_only && !no_tablespaces)
471 472 473 474 475
		{
			/* Dump tablespaces */
			if (server_version >= 80000)
				dumpTablespaces(conn);
		}
476

477
		/* Dump CREATE DATABASE commands */
478
		if (!globals_only && !roles_only && !tablespaces_only)
479
			dumpCreateDB(conn);
480
	}
481

482
	if (!globals_only && !roles_only && !tablespaces_only)
483
		dumpDatabases(conn);
484 485

	PQfinish(conn);
486

487 488
	if (verbose)
		dumpTimestamp("Completed on");
489
	fprintf(OPF, "--\n-- PostgreSQL database cluster dump complete\n--\n\n");
Bruce Momjian's avatar
Bruce Momjian committed
490

491 492
	if (filename)
		fclose(OPF);
493

494 495 496 497 498 499 500 501 502 503
	exit(0);
}



static void
help(void)
{
	printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname);
	printf(_("Usage:\n"));
504
	printf(_("  %s [OPTION]...\n"), progname);
505

506
	printf(_("\nGeneral options:\n"));
507 508 509 510
	printf(_("  -f, --file=FILENAME         output file name\n"));
	printf(_("  --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock\n"));
	printf(_("  --help                      show this help, then exit\n"));
	printf(_("  --version                   output version information, then exit\n"));
511
	printf(_("\nOptions controlling the output content:\n"));
512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527
	printf(_("  -a, --data-only             dump only the data, not the schema\n"));
	printf(_("  -c, --clean                 clean (drop) databases before recreating\n"));
	printf(_("  -d, --inserts               dump data as INSERT commands, rather than COPY\n"));
	printf(_("  -D, --column-inserts        dump data as INSERT commands with column names\n"));
	printf(_("  -g, --globals-only          dump only global objects, no databases\n"));
	printf(_("  -o, --oids                  include OIDs in dump\n"));
	printf(_("  -O, --no-owner              skip restoration of object ownership\n"));
	printf(_("  -r, --roles-only            dump only roles, no databases or tablespaces\n"));
	printf(_("  -s, --schema-only           dump only the schema, no data\n"));
	printf(_("  -S, --superuser=NAME        superuser user name to use in the dump\n"));
	printf(_("  -t, --tablespaces-only      dump only tablespaces, no databases or roles\n"));
	printf(_("  -x, --no-privileges         do not dump privileges (grant/revoke)\n"));
	printf(_("  --disable-dollar-quoting    disable dollar quoting, use SQL standard quoting\n"));
	printf(_("  --disable-triggers          disable triggers during data-only restore\n"));
	printf(_("  --no-tablespaces            do not dump tablespace assignments\n"));
	printf(_("  --role=ROLENAME             do SET ROLE before dump\n"));
528
	printf(_("  --use-set-session-authorization\n"
529 530
		 "                              use SET SESSION AUTHORIZATION commands instead of\n"
		 "                              ALTER OWNER commands to set ownership\n"));
531 532

	printf(_("\nConnection options:\n"));
533
	printf(_("  -h, --host=HOSTNAME      database server host or socket directory\n"));
534
	printf(_("  -l, --database=DBNAME    alternative default database\n"));
535 536
	printf(_("  -p, --port=PORT          database server port number\n"));
	printf(_("  -U, --username=NAME      connect as specified database user\n"));
537
	printf(_("  -w, --no-password        never prompt for password\n"));
538
	printf(_("  -W, --password           force password prompt (should happen automatically)\n"));
539

540 541
	printf(_("\nIf -f/--file is not used, then the SQL script will be written to the standard\n"
		"output.\n\n"));
542 543 544 545 546 547
	printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
}



/*
548
 * Dump roles
549 550
 */
static void
551
dumpRoles(PGconn *conn)
552
{
553
	PQExpBuffer buf = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
554
	PGresult   *res;
555 556 557 558 559 560 561 562 563
	int			i_rolname,
				i_rolsuper,
				i_rolinherit,
				i_rolcreaterole,
				i_rolcreatedb,
				i_rolcatupdate,
				i_rolcanlogin,
				i_rolconnlimit,
				i_rolpassword,
564 565
				i_rolvaliduntil,
				i_rolcomment;
Bruce Momjian's avatar
Bruce Momjian committed
566
	int			i;
567

568
	/* note: rolconfig is dumped later */
569
	if (server_version >= 80200)
570 571 572 573
		printfPQExpBuffer(buf,
						  "SELECT rolname, rolsuper, rolinherit, "
						  "rolcreaterole, rolcreatedb, rolcatupdate, "
						  "rolcanlogin, rolconnlimit, rolpassword, "
574
						  "rolvaliduntil, "
Bruce Momjian's avatar
Bruce Momjian committed
575
			  "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
576 577
						  "FROM pg_authid "
						  "ORDER BY 1");
578 579 580 581 582 583
	else if (server_version >= 80100)
		printfPQExpBuffer(buf,
						  "SELECT rolname, rolsuper, rolinherit, "
						  "rolcreaterole, rolcreatedb, rolcatupdate, "
						  "rolcanlogin, rolconnlimit, rolpassword, "
						  "rolvaliduntil, null as rolcomment "
584 585
						  "FROM pg_authid "
						  "ORDER BY 1");
586
	else
587 588 589 590 591 592 593 594 595 596
		printfPQExpBuffer(buf,
						  "SELECT usename as rolname, "
						  "usesuper as rolsuper, "
						  "true as rolinherit, "
						  "usesuper as rolcreaterole, "
						  "usecreatedb as rolcreatedb, "
						  "usecatupd as rolcatupdate, "
						  "true as rolcanlogin, "
						  "-1 as rolconnlimit, "
						  "passwd as rolpassword, "
597
						  "valuntil as rolvaliduntil, "
598
						  "null as rolcomment "
599 600 601 602 603 604 605 606 607 608 609
						  "FROM pg_shadow "
						  "UNION ALL "
						  "SELECT groname as rolname, "
						  "false as rolsuper, "
						  "true as rolinherit, "
						  "false as rolcreaterole, "
						  "false as rolcreatedb, "
						  "false as rolcatupdate, "
						  "false as rolcanlogin, "
						  "-1 as rolconnlimit, "
						  "null::text as rolpassword, "
610 611
						  "null::abstime as rolvaliduntil, "
						  "null as rolcomment "
612 613 614 615
						  "FROM pg_group "
						  "WHERE NOT EXISTS (SELECT 1 FROM pg_shadow "
						  " WHERE usename = groname) "
						  "ORDER BY 1");
616 617 618 619 620 621 622 623 624 625 626 627 628

	res = executeQuery(conn, buf->data);

	i_rolname = PQfnumber(res, "rolname");
	i_rolsuper = PQfnumber(res, "rolsuper");
	i_rolinherit = PQfnumber(res, "rolinherit");
	i_rolcreaterole = PQfnumber(res, "rolcreaterole");
	i_rolcreatedb = PQfnumber(res, "rolcreatedb");
	i_rolcatupdate = PQfnumber(res, "rolcatupdate");
	i_rolcanlogin = PQfnumber(res, "rolcanlogin");
	i_rolconnlimit = PQfnumber(res, "rolconnlimit");
	i_rolpassword = PQfnumber(res, "rolpassword");
	i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
629
	i_rolcomment = PQfnumber(res, "rolcomment");
630

631
	if (PQntuples(res) > 0)
632
		fprintf(OPF, "--\n-- Roles\n--\n\n");
633 634 635

	for (i = 0; i < PQntuples(res); i++)
	{
636
		const char *rolename;
Bruce Momjian's avatar
Bruce Momjian committed
637

638
		rolename = PQgetvalue(res, i, i_rolname);
639

640 641 642 643
		resetPQExpBuffer(buf);

		if (output_clean)
			appendPQExpBuffer(buf, "DROP ROLE %s;\n", fmtId(rolename));
644

Bruce Momjian's avatar
Bruce Momjian committed
645
		/*
646 647 648
		 * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role
		 * will acquire the right properties even if it already exists. (The
		 * above DROP may therefore seem redundant, but it isn't really,
649
		 * because this technique doesn't get rid of role memberships.)
Bruce Momjian's avatar
Bruce Momjian committed
650
		 */
651 652
		appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename));
		appendPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename));
653

654 655 656 657
		if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0)
			appendPQExpBuffer(buf, " SUPERUSER");
		else
			appendPQExpBuffer(buf, " NOSUPERUSER");
658

659 660 661 662 663 664 665 666 667 668 669
		if (strcmp(PQgetvalue(res, i, i_rolinherit), "t") == 0)
			appendPQExpBuffer(buf, " INHERIT");
		else
			appendPQExpBuffer(buf, " NOINHERIT");

		if (strcmp(PQgetvalue(res, i, i_rolcreaterole), "t") == 0)
			appendPQExpBuffer(buf, " CREATEROLE");
		else
			appendPQExpBuffer(buf, " NOCREATEROLE");

		if (strcmp(PQgetvalue(res, i, i_rolcreatedb), "t") == 0)
670 671 672 673
			appendPQExpBuffer(buf, " CREATEDB");
		else
			appendPQExpBuffer(buf, " NOCREATEDB");

674 675
		if (strcmp(PQgetvalue(res, i, i_rolcanlogin), "t") == 0)
			appendPQExpBuffer(buf, " LOGIN");
676
		else
677 678 679 680 681
			appendPQExpBuffer(buf, " NOLOGIN");

		if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0)
			appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
							  PQgetvalue(res, i, i_rolconnlimit));
682

683 684 685
		if (!PQgetisnull(res, i, i_rolpassword))
		{
			appendPQExpBuffer(buf, " PASSWORD ");
686
			appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn);
687 688 689
		}

		if (!PQgetisnull(res, i, i_rolvaliduntil))
690
			appendPQExpBuffer(buf, " VALID UNTIL '%s'",
691
							  PQgetvalue(res, i, i_rolvaliduntil));
692 693 694

		appendPQExpBuffer(buf, ";\n");

Bruce Momjian's avatar
Bruce Momjian committed
695 696
		if (!PQgetisnull(res, i, i_rolcomment))
		{
697
			appendPQExpBuffer(buf, "COMMENT ON ROLE %s IS ", fmtId(rolename));
698
			appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolcomment), conn);
699 700 701
			appendPQExpBuffer(buf, ";\n");
		}

702
		fprintf(OPF, "%s", buf->data);
703

704
		if (server_version >= 70300)
705
			dumpUserConfig(conn, rolename);
706 707 708
	}

	PQclear(res);
709

710
	fprintf(OPF, "\n\n");
711 712

	destroyPQExpBuffer(buf);
713 714 715
}


716 717 718 719 720 721 722 723 724 725 726 727 728 729
/*
 * Dump role memberships.  This code is used for 8.1 and later servers.
 *
 * Note: we expect dumpRoles already created all the roles, but there is
 * no membership yet.
 */
static void
dumpRoleMembership(PGconn *conn)
{
	PGresult   *res;
	int			i;

	res = executeQuery(conn, "SELECT ur.rolname AS roleid, "
					   "um.rolname AS member, "
730 731
					   "a.admin_option, "
					   "ug.rolname AS grantor "
732 733 734
					   "FROM pg_auth_members a "
					   "LEFT JOIN pg_authid ur on ur.oid = a.roleid "
					   "LEFT JOIN pg_authid um on um.oid = a.member "
735 736
					   "LEFT JOIN pg_authid ug on ug.oid = a.grantor "
					   "ORDER BY 1,2,3");
737

738
	if (PQntuples(res) > 0)
739
		fprintf(OPF, "--\n-- Role memberships\n--\n\n");
740 741 742 743 744

	for (i = 0; i < PQntuples(res); i++)
	{
		char	   *roleid = PQgetvalue(res, i, 0);
		char	   *member = PQgetvalue(res, i, 1);
745
		char	   *option = PQgetvalue(res, i, 2);
746

747 748
		fprintf(OPF, "GRANT %s", fmtId(roleid));
		fprintf(OPF, " TO %s", fmtId(member));
749
		if (*option == 't')
750
			fprintf(OPF, " WITH ADMIN OPTION");
751 752 753 754 755 756 757

		/*
		 * We don't track the grantor very carefully in the backend, so cope
		 * with the possibility that it has been dropped.
		 */
		if (!PQgetisnull(res, i, 3))
		{
Bruce Momjian's avatar
Bruce Momjian committed
758
			char	   *grantor = PQgetvalue(res, i, 3);
759 760 761 762

			fprintf(OPF, " GRANTED BY %s", fmtId(grantor));
		}
		fprintf(OPF, ";\n");
763 764 765 766
	}

	PQclear(res);

767
	fprintf(OPF, "\n\n");
768
}
769 770

/*
771 772 773 774 775 776
 * Dump group memberships from a pre-8.1 server.  It's annoying that we
 * can't share any useful amount of code with the post-8.1 case, but
 * the catalog representations are too different.
 *
 * Note: we expect dumpRoles already created all the roles, but there is
 * no membership yet.
777 778 779 780
 */
static void
dumpGroups(PGconn *conn)
{
781
	PQExpBuffer buf = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
782 783
	PGresult   *res;
	int			i;
784

785 786
	res = executeQuery(conn,
					   "SELECT groname, grolist FROM pg_group ORDER BY 1");
787

788
	if (PQntuples(res) > 0)
789
		fprintf(OPF, "--\n-- Role memberships\n--\n\n");
790

791 792
	for (i = 0; i < PQntuples(res); i++)
	{
793
		char	   *groname = PQgetvalue(res, i, 0);
794 795 796
		char	   *grolist = PQgetvalue(res, i, 1);
		PGresult   *res2;
		int			j;
797

798 799 800 801 802
		/*
		 * Array representation is {1,2,3} ... convert to (1,2,3)
		 */
		if (strlen(grolist) < 3)
			continue;
803

804 805 806 807 808 809 810 811
		grolist = strdup(grolist);
		grolist[0] = '(';
		grolist[strlen(grolist) - 1] = ')';
		printfPQExpBuffer(buf,
						  "SELECT usename FROM pg_shadow "
						  "WHERE usesysid IN %s ORDER BY 1",
						  grolist);
		free(grolist);
812

813
		res2 = executeQuery(conn, buf->data);
814

815 816 817
		for (j = 0; j < PQntuples(res2); j++)
		{
			char	   *usename = PQgetvalue(res2, j, 0);
818

819 820 821 822 823 824
			/*
			 * Don't try to grant a role to itself; can happen if old
			 * installation has identically named user and group.
			 */
			if (strcmp(groname, usename) == 0)
				continue;
825

826 827
			fprintf(OPF, "GRANT %s", fmtId(groname));
			fprintf(OPF, " TO %s;\n", fmtId(usename));
828
		}
829 830

		PQclear(res2);
831 832 833
	}

	PQclear(res);
834 835
	destroyPQExpBuffer(buf);

836
	fprintf(OPF, "\n\n");
837 838
}

839 840 841 842 843 844 845 846 847 848
/*
 * Dump tablespaces.
 */
static void
dumpTablespaces(PGconn *conn)
{
	PGresult   *res;
	int			i;

	/*
849 850
	 * Get all tablespaces except built-in ones (which we assume are named
	 * pg_xxx)
851
	 */
852 853
	if (server_version >= 80200)
		res = executeQuery(conn, "SELECT spcname, "
Bruce Momjian's avatar
Bruce Momjian committed
854
						 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
855
						   "spclocation, spcacl, "
Bruce Momjian's avatar
Bruce Momjian committed
856
						"pg_catalog.shobj_description(oid, 'pg_tablespace') "
857 858 859
						   "FROM pg_catalog.pg_tablespace "
						   "WHERE spcname !~ '^pg_' "
						   "ORDER BY 1");
Bruce Momjian's avatar
Bruce Momjian committed
860
	else
861
		res = executeQuery(conn, "SELECT spcname, "
Bruce Momjian's avatar
Bruce Momjian committed
862
						 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
863 864 865 866 867
						   "spclocation, spcacl, "
						   "null "
						   "FROM pg_catalog.pg_tablespace "
						   "WHERE spcname !~ '^pg_' "
						   "ORDER BY 1");
Bruce Momjian's avatar
Bruce Momjian committed
868

869
	if (PQntuples(res) > 0)
870
		fprintf(OPF, "--\n-- Tablespaces\n--\n\n");
871 872 873 874

	for (i = 0; i < PQntuples(res); i++)
	{
		PQExpBuffer buf = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
875 876 877 878
		char	   *spcname = PQgetvalue(res, i, 0);
		char	   *spcowner = PQgetvalue(res, i, 1);
		char	   *spclocation = PQgetvalue(res, i, 2);
		char	   *spcacl = PQgetvalue(res, i, 3);
879
		char	   *spccomment = PQgetvalue(res, i, 4);
Bruce Momjian's avatar
Bruce Momjian committed
880
		char	   *fspcname;
881 882 883 884 885 886 887 888 889

		/* needed for buildACLCommands() */
		fspcname = strdup(fmtId(spcname));

		if (output_clean)
			appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", fspcname);

		appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
		appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
890

891
		appendPQExpBuffer(buf, " LOCATION ");
892
		appendStringLiteralConn(buf, spclocation, conn);
893 894 895
		appendPQExpBuffer(buf, ";\n");

		if (!skip_acls &&
896
			!buildACLCommands(fspcname, NULL, "TABLESPACE", spcacl, spcowner,
897 898 899 900 901 902 903 904
							  server_version, buf))
		{
			fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
					progname, spcacl, fspcname);
			PQfinish(conn);
			exit(1);
		}

Bruce Momjian's avatar
Bruce Momjian committed
905 906
		if (spccomment && strlen(spccomment))
		{
907
			appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", fspcname);
908
			appendStringLiteralConn(buf, spccomment, conn);
909 910 911
			appendPQExpBuffer(buf, ";\n");
		}

912
		fprintf(OPF, "%s", buf->data);
913 914 915 916 917 918

		free(fspcname);
		destroyPQExpBuffer(buf);
	}

	PQclear(res);
919
	fprintf(OPF, "\n\n");
920
}
921 922 923 924 925 926 927 928 929 930 931 932 933 934 935

/*
 * Dump commands to create each database.
 *
 * To minimize the number of reconnections (and possibly ensuing
 * password prompts) required by the output script, we emit all CREATE
 * DATABASE commands during the initial phase of the script, and then
 * run pg_dump for each database to dump the contents of that
 * database.  We skip databases marked not datallowconn, since we'd be
 * unable to connect to them anyway (and besides, we don't want to
 * dump template0).
 */
static void
dumpCreateDB(PGconn *conn)
{
936
	PQExpBuffer buf = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
937 938
	PGresult   *res;
	int			i;
939

940
	fprintf(OPF, "--\n-- Database creation\n--\n\n");
941

942
	if (server_version >= 80400)
943 944 945 946
		res = executeQuery(conn,
						   "SELECT datname, "
						   "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
						   "pg_encoding_to_char(d.encoding), "
947
						   "datcollate, datctype, datfrozenxid, "
948 949 950 951 952 953 954 955 956
						   "datistemplate, datacl, datconnlimit, "
						   "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
			  "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
						   "WHERE datallowconn ORDER BY 1");
	else if (server_version >= 80100)
		res = executeQuery(conn,
						   "SELECT datname, "
						   "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
						   "pg_encoding_to_char(d.encoding), "
957
						   "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
958 959
						   "datistemplate, datacl, datconnlimit, "
						   "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
960
			  "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
961 962
						   "WHERE datallowconn ORDER BY 1");
	else if (server_version >= 80000)
963 964 965 966
		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), "
967
						   "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
968
						   "datistemplate, datacl, -1 as datconnlimit, "
969
						   "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
970
		   "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
971 972 973 974 975 976
						   "WHERE datallowconn ORDER BY 1");
	else if (server_version >= 70300)
		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), "
977
						   "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
978
						   "datistemplate, datacl, -1 as datconnlimit, "
979
						   "'pg_default' AS dattablespace "
980
		   "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
981 982 983 984 985
						   "WHERE datallowconn ORDER BY 1");
	else if (server_version >= 70100)
		res = executeQuery(conn,
						   "SELECT datname, "
						   "coalesce("
986
					"(select usename from pg_shadow where usesysid=datdba), "
987 988
						   "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
						   "pg_encoding_to_char(d.encoding), "
989
						   "null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid"
990
						   "datistemplate, '' as datacl, -1 as datconnlimit, "
991
						   "'pg_default' AS dattablespace "
992 993 994 995 996
						   "FROM pg_database d "
						   "WHERE datallowconn ORDER BY 1");
	else
	{
		/*
997 998
		 * Note: 7.0 fails to cope with sub-select in COALESCE, so just deal
		 * with getting a NULL by not printing any OWNER clause.
999 1000 1001
		 */
		res = executeQuery(conn,
						   "SELECT datname, "
1002
					"(select usename from pg_shadow where usesysid=datdba), "
1003
						   "pg_encoding_to_char(d.encoding), "
1004
						   "null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid"
1005
						   "'f' as datistemplate, "
1006
						   "'' as datacl, -1 as datconnlimit, "
1007
						   "'pg_default' AS dattablespace "
1008 1009 1010
						   "FROM pg_database d "
						   "ORDER BY 1");
	}
1011 1012 1013

	for (i = 0; i < PQntuples(res); i++)
	{
Bruce Momjian's avatar
Bruce Momjian committed
1014 1015 1016
		char	   *dbname = PQgetvalue(res, i, 0);
		char	   *dbowner = PQgetvalue(res, i, 1);
		char	   *dbencoding = PQgetvalue(res, i, 2);
1017 1018
		char	   *dbcollate = PQgetvalue(res, i, 3);
		char	   *dbctype = PQgetvalue(res, i, 4);
1019 1020 1021 1022 1023
		uint32	   dbfrozenxid = atooid(PQgetvalue(res, i, 5));
		char	   *dbistemplate = PQgetvalue(res, i, 6);
		char	   *dbacl = PQgetvalue(res, i, 7);
		char	   *dbconnlimit = PQgetvalue(res, i, 8);
		char	   *dbtablespace = PQgetvalue(res, i, 9);
1024
		char	   *fdbname;
1025

1026 1027
		fdbname = strdup(fmtId(dbname));

1028 1029
		resetPQExpBuffer(buf);

1030 1031 1032 1033 1034 1035 1036 1037 1038 1039
		/*
		 * Skip the CREATE DATABASE commands for "template1" and "postgres",
		 * since they are presumably already there in the destination cluster.
		 * We do want to emit their ACLs and config options if any, however.
		 */
		if (strcmp(dbname, "template1") != 0 &&
			strcmp(dbname, "postgres") != 0)
		{
			if (output_clean)
				appendPQExpBuffer(buf, "DROP DATABASE %s;\n", fdbname);
1040

1041
			appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
1042

1043
			appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
1044

1045 1046
			if (strlen(dbowner) != 0)
				appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner));
1047

1048
			appendPQExpBuffer(buf, " ENCODING = ");
1049
			appendStringLiteralConn(buf, dbencoding, conn);
1050

1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062
			if (strlen(dbcollate) != 0)
			{
				appendPQExpBuffer(buf, " COLLATE = ");
				appendStringLiteralConn(buf, dbcollate, conn);
			}

			if (strlen(dbctype) != 0)
			{
				appendPQExpBuffer(buf, " CTYPE = ");
				appendStringLiteralConn(buf, dbctype, conn);
			}

1063
			/*
Bruce Momjian's avatar
Bruce Momjian committed
1064 1065 1066 1067 1068 1069
			 * Output tablespace if it isn't the default.  For default, it
			 * uses the default from the template database.  If tablespace is
			 * specified and tablespace creation failed earlier, (e.g. no such
			 * directory), the database creation will fail too.  One solution
			 * would be to use 'SET default_tablespace' like we do in pg_dump
			 * for setting non-default database locations.
1070
			 */
1071
			if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces)
1072 1073
				appendPQExpBuffer(buf, " TABLESPACE = %s",
								  fmtId(dbtablespace));
1074

1075 1076 1077 1078
			if (strcmp(dbconnlimit, "-1") != 0)
				appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
								  dbconnlimit);

1079
			appendPQExpBuffer(buf, ";\n");
1080 1081 1082 1083

			if (strcmp(dbistemplate, "t") == 0)
			{
				appendPQExpBuffer(buf, "UPDATE pg_database SET datistemplate = 't' WHERE datname = ");
1084
				appendStringLiteralConn(buf, dbname, conn);
1085 1086
				appendPQExpBuffer(buf, ";\n");
			}
1087 1088 1089 1090 1091 1092 1093 1094 1095

			if (binary_upgrade)
			{
				appendPQExpBuffer(buf, "\n-- For binary upgrade, set datfrozenxid.\n");
				appendPQExpBuffer(buf, "UPDATE pg_database\n"
									 "SET datfrozenxid = '%u'\n"
									 "WHERE	datname = '%s';\n",
									 dbfrozenxid, fdbname);
			}
1096
		}
1097

1098
		if (!skip_acls &&
1099
			!buildACLCommands(fdbname, NULL, "DATABASE", dbacl, dbowner,
1100 1101
							  server_version, buf))
		{
1102
			fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
1103 1104 1105 1106 1107
					progname, dbacl, fdbname);
			PQfinish(conn);
			exit(1);
		}

1108
		fprintf(OPF, "%s", buf->data);
1109

1110 1111
		if (server_version >= 70300)
			dumpDatabaseConfig(conn, dbname);
1112 1113

		free(fdbname);
1114 1115 1116
	}

	PQclear(res);
1117 1118
	destroyPQExpBuffer(buf);

1119
	fprintf(OPF, "\n\n");
1120 1121 1122 1123
}



1124 1125 1126 1127 1128 1129 1130
/*
 * Dump database-specific configuration
 */
static void
dumpDatabaseConfig(PGconn *conn, const char *dbname)
{
	PQExpBuffer buf = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
1131
	int			count = 1;
1132

Bruce Momjian's avatar
Bruce Momjian committed
1133
	for (;;)
1134 1135 1136 1137
	{
		PGresult   *res;

		printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
1138
		appendStringLiteralConn(buf, dbname, conn);
1139 1140 1141 1142 1143
		appendPQExpBuffer(buf, ";");

		res = executeQuery(conn, buf->data);
		if (!PQgetisnull(res, 0, 0))
		{
1144 1145
			makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
								   "DATABASE", dbname);
1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167
			PQclear(res);
			count++;
		}
		else
		{
			PQclear(res);
			break;
		}
	}

	destroyPQExpBuffer(buf);
}



/*
 * Dump user-specific configuration
 */
static void
dumpUserConfig(PGconn *conn, const char *username)
{
	PQExpBuffer buf = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
1168
	int			count = 1;
1169

Bruce Momjian's avatar
Bruce Momjian committed
1170
	for (;;)
1171 1172 1173
	{
		PGresult   *res;

1174 1175 1176 1177
		if (server_version >= 80100)
			printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
		else
			printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
1178
		appendStringLiteralConn(buf, username, conn);
1179 1180

		res = executeQuery(conn, buf->data);
1181 1182
		if (PQntuples(res) == 1 &&
			!PQgetisnull(res, 0, 0))
1183
		{
1184 1185
			makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
								   "ROLE", username);
1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204
			PQclear(res);
			count++;
		}
		else
		{
			PQclear(res);
			break;
		}
	}

	destroyPQExpBuffer(buf);
}



/*
 * Helper function for dumpXXXConfig().
 */
static void
1205 1206
makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
					   const char *type, const char *name)
1207
{
Bruce Momjian's avatar
Bruce Momjian committed
1208 1209
	char	   *pos;
	char	   *mine;
1210 1211 1212 1213 1214 1215 1216 1217 1218 1219
	PQExpBuffer buf = createPQExpBuffer();

	mine = strdup(arrayitem);
	pos = strchr(mine, '=');
	if (pos == NULL)
		return;

	*pos = 0;
	appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
	appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
Bruce Momjian's avatar
Bruce Momjian committed
1220 1221

	/*
1222
	 * Some GUC variable names are 'LIST' type and hence must not be quoted.
Bruce Momjian's avatar
Bruce Momjian committed
1223
	 */
1224 1225
	if (pg_strcasecmp(mine, "DateStyle") == 0
		|| pg_strcasecmp(mine, "search_path") == 0)
1226
		appendPQExpBuffer(buf, "%s", pos + 1);
Bruce Momjian's avatar
Bruce Momjian committed
1227
	else
1228
		appendStringLiteralConn(buf, pos + 1, conn);
1229 1230
	appendPQExpBuffer(buf, ";\n");

1231
	fprintf(OPF, "%s", buf->data);
1232 1233 1234 1235 1236 1237
	destroyPQExpBuffer(buf);
	free(mine);
}



1238 1239 1240 1241 1242 1243
/*
 * Dump contents of databases.
 */
static void
dumpDatabases(PGconn *conn)
{
Bruce Momjian's avatar
Bruce Momjian committed
1244 1245
	PGresult   *res;
	int			i;
1246

1247 1248 1249 1250 1251
	if (server_version >= 70100)
		res = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1");
	else
		res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1");

1252 1253
	for (i = 0; i < PQntuples(res); i++)
	{
Bruce Momjian's avatar
Bruce Momjian committed
1254 1255 1256
		int			ret;

		char	   *dbname = PQgetvalue(res, i, 0);
1257 1258 1259 1260

		if (verbose)
			fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);

1261
		fprintf(OPF, "\\connect %s\n\n", fmtId(dbname));
Bruce Momjian's avatar
Bruce Momjian committed
1262

1263 1264
		if (filename)
			fclose(OPF);
Bruce Momjian's avatar
Bruce Momjian committed
1265

1266 1267 1268
		ret = runPgDump(dbname);
		if (ret != 0)
		{
1269
			fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname);
1270 1271
			exit(1);
		}
Bruce Momjian's avatar
Bruce Momjian committed
1272

1273 1274 1275 1276 1277
		if (filename)
		{
			OPF = fopen(filename, PG_BINARY_A);
			if (!OPF)
			{
1278 1279
				fprintf(stderr, _("%s: could not re-open the output file \"%s\": %s\n"),
						progname, filename, strerror(errno));
1280 1281 1282
				exit(1);
			}
		}
Bruce Momjian's avatar
Bruce Momjian committed
1283

1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297
	}

	PQclear(res);
}



/*
 * Run pg_dump on dbname.
 */
static int
runPgDump(const char *dbname)
{
	PQExpBuffer cmd = createPQExpBuffer();
Bruce Momjian's avatar
Bruce Momjian committed
1298
	int			ret;
1299

1300 1301 1302
	appendPQExpBuffer(cmd, SYSTEMQUOTE "\"%s\" %s", pg_dump_bin,
					  pgdumpopts->data);

1303
	/*
Bruce Momjian's avatar
Bruce Momjian committed
1304 1305
	 * If we have a filename, use the undocumented plain-append pg_dump
	 * format.
Bruce Momjian's avatar
Bruce Momjian committed
1306
	 */
1307
	if (filename)
1308
		appendPQExpBuffer(cmd, " -Fa ");
1309
	else
1310
		appendPQExpBuffer(cmd, " -Fp ");
1311

1312
	doShellQuoting(cmd, dbname);
Bruce Momjian's avatar
Bruce Momjian committed
1313

1314 1315
	appendPQExpBuffer(cmd, "%s", SYSTEMQUOTE);

1316
	if (verbose)
1317
		fprintf(stderr, _("%s: running \"%s\"\n"), progname, cmd->data);
1318

1319 1320 1321
	fflush(stdout);
	fflush(stderr);

1322
	ret = system(cmd->data);
1323

1324 1325 1326 1327 1328 1329 1330 1331 1332
	destroyPQExpBuffer(cmd);

	return ret;
}


/*
 * Make a database connection with the given parameters.  An
 * interactive password prompt is automatically issued if required.
1333 1334 1335
 *
 * If fail_on_error is false, we return NULL without printing any message
 * on failure, but preserve any prompted password for the next try.
1336 1337 1338
 */
static PGconn *
connectDatabase(const char *dbname, const char *pghost, const char *pgport,
1339
				const char *pguser, enum trivalue prompt_password, bool fail_on_error)
1340 1341
{
	PGconn	   *conn;
1342
	bool		new_pass;
1343
	const char *remoteversion_str;
1344
	int			my_version;
1345
	static char *password = NULL;
1346

1347
	if (prompt_password == TRI_YES && !password)
1348 1349 1350
		password = simple_prompt("Password: ", 100, false);

	/*
1351 1352
	 * Start the connection.  Loop until we have a password if requested by
	 * backend.
1353 1354 1355
	 */
	do
	{
1356
		new_pass = false;
1357 1358 1359 1360
		conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, pguser, password);

		if (!conn)
		{
1361
			fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
1362
					progname, dbname);
1363
			exit(1);
1364 1365 1366
		}

		if (PQstatus(conn) == CONNECTION_BAD &&
1367
			PQconnectionNeedsPassword(conn) &&
1368 1369
			password == NULL &&
			prompt_password != TRI_NO)
1370 1371 1372
		{
			PQfinish(conn);
			password = simple_prompt("Password: ", 100, false);
1373
			new_pass = true;
1374
		}
1375
	} while (new_pass);
1376 1377 1378 1379

	/* check to see that the backend connection was successfully made */
	if (PQstatus(conn) == CONNECTION_BAD)
	{
1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391
		if (fail_on_error)
		{
			fprintf(stderr,
					_("%s: could not connect to database \"%s\": %s\n"),
					progname, dbname, PQerrorMessage(conn));
			exit(1);
		}
		else
		{
			PQfinish(conn);
			return NULL;
		}
1392 1393
	}

1394 1395
	remoteversion_str = PQparameterStatus(conn, "server_version");
	if (!remoteversion_str)
1396 1397 1398 1399
	{
		fprintf(stderr, _("%s: could not get server version\n"), progname);
		exit(1);
	}
1400 1401
	server_version = parse_version(remoteversion_str);
	if (server_version < 0)
1402
	{
1403 1404 1405
		fprintf(stderr, _("%s: could not parse server version \"%s\"\n"),
				progname, remoteversion_str);
		exit(1);
1406 1407
	}

1408 1409 1410 1411 1412 1413 1414 1415
	my_version = parse_version(PG_VERSION);
	if (my_version < 0)
	{
		fprintf(stderr, _("%s: could not parse version \"%s\"\n"),
				progname, PG_VERSION);
		exit(1);
	}

1416 1417 1418 1419
	/*
	 * We allow the server to be back to 7.0, and up to any minor release
	 * of our own major version.  (See also version check in pg_dump.c.)
	 */
1420
	if (my_version != server_version
1421 1422
		&& (server_version < 70000 ||
			(server_version / 100) > (my_version / 100)))
1423 1424 1425
	{
		fprintf(stderr, _("server version: %s; %s version: %s\n"),
				remoteversion_str, progname, PG_VERSION);
1426 1427
		fprintf(stderr, _("aborting because of server version mismatch\n"));
		exit(1);
1428 1429
	}

1430
	/*
1431 1432
	 * On 7.3 and later, make sure we are not fooled by non-system schemas in
	 * the search path.
1433 1434 1435 1436
	 */
	if (server_version >= 70300)
		executeCommand(conn, "SET search_path = pg_catalog");

1437 1438 1439 1440 1441 1442 1443 1444 1445 1446
	return conn;
}


/*
 * Run a query, return the results, exit program on failure.
 */
static PGresult *
executeQuery(PGconn *conn, const char *query)
{
Bruce Momjian's avatar
Bruce Momjian committed
1447
	PGresult   *res;
1448

1449 1450 1451
	if (verbose)
		fprintf(stderr, _("%s: executing %s\n"), progname, query);

1452 1453 1454 1455
	res = PQexec(conn, query);
	if (!res ||
		PQresultStatus(res) != PGRES_TUPLES_OK)
	{
1456 1457 1458 1459
		fprintf(stderr, _("%s: query failed: %s"),
				progname, PQerrorMessage(conn));
		fprintf(stderr, _("%s: query was: %s\n"),
				progname, query);
1460 1461 1462 1463 1464 1465
		PQfinish(conn);
		exit(1);
	}

	return res;
}
1466

1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492
/*
 * As above for a SQL command (which returns nothing).
 */
static void
executeCommand(PGconn *conn, const char *query)
{
	PGresult   *res;

	if (verbose)
		fprintf(stderr, _("%s: executing %s\n"), progname, query);

	res = PQexec(conn, query);
	if (!res ||
		PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, _("%s: query failed: %s"),
				progname, PQerrorMessage(conn));
		fprintf(stderr, _("%s: query was: %s\n"),
				progname, query);
		PQfinish(conn);
		exit(1);
	}

	PQclear(res);
}

1493 1494 1495 1496 1497 1498 1499

/*
 * dumpTimestamp
 */
static void
dumpTimestamp(char *msg)
{
Bruce Momjian's avatar
Bruce Momjian committed
1500 1501
	char		buf[256];
	time_t		now = time(NULL);
1502

1503 1504 1505
	/*
	 * We don't print the timezone on Win32, because the names are long and
	 * localized, which means they may contain characters in various random
Bruce Momjian's avatar
Bruce Momjian committed
1506 1507
	 * encodings; this has been seen to cause encoding errors when reading the
	 * dump script.
1508 1509 1510 1511 1512 1513 1514 1515
	 */
	if (strftime(buf, sizeof(buf),
#ifndef WIN32
				 "%Y-%m-%d %H:%M:%S %Z",
#else
				 "%Y-%m-%d %H:%M:%S",
#endif
				 localtime(&now)) != 0)
1516
		fprintf(OPF, "-- %s %s\n\n", msg, buf);
1517
}
1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552


/*
 * Append the given string to the shell command being built in the buffer,
 * with suitable shell-style quoting.
 */
static void
doShellQuoting(PQExpBuffer buf, const char *str)
{
	const char *p;

#ifndef WIN32
	appendPQExpBufferChar(buf, '\'');
	for (p = str; *p; p++)
	{
		if (*p == '\'')
			appendPQExpBuffer(buf, "'\"'\"'");
		else
			appendPQExpBufferChar(buf, *p);
	}
	appendPQExpBufferChar(buf, '\'');

#else /* WIN32 */

	appendPQExpBufferChar(buf, '"');
	for (p = str; *p; p++)
	{
		if (*p == '"')
			appendPQExpBuffer(buf, "\\\"");
		else
			appendPQExpBufferChar(buf, *p);
	}
	appendPQExpBufferChar(buf, '"');
#endif /* WIN32 */
}