1 /*-------------------------------------------------------------------------
5 * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
6 * Portions Copyright (c) 1994, Regents of the University of California
9 * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.42 2004/06/18 06:14:00 tgl Exp $
11 *-------------------------------------------------------------------------
14 #include "postgres_fe.h"
26 #include "getopt_long.h"
32 #include "dumputils.h"
34 #include "pg_backup.h"
35 #include "pqexpbuffer.h"
37 #define _(x) gettext((x))
39 /* version string we expect back from postgres */
40 #define PG_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
43 static const char *progname;
45 static void help(void);
47 static void dumpUsers(PGconn *conn);
48 static void dumpGroups(PGconn *conn);
49 static void dumpTablespaces(PGconn *conn);
50 static void dumpCreateDB(PGconn *conn);
51 static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
52 static void dumpUserConfig(PGconn *conn, const char *username);
53 static void makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name);
54 static void dumpDatabases(PGconn *conn);
55 static void dumpTimestamp(char *msg);
57 static int runPgDump(const char *dbname);
58 static PGconn *connectDatabase(const char *dbname, const char *pghost, const char *pgport,
59 const char *pguser, bool require_password);
60 static PGresult *executeQuery(PGconn *conn, const char *query);
63 char pg_dump_bin[MAXPGPATH];
64 PQExpBuffer pgdumpopts;
65 bool output_clean = false;
66 bool skip_acls = false;
73 main(int argc, char *argv[])
78 bool force_password = false;
79 bool data_only = false;
80 bool globals_only = false;
81 bool schema_only = false;
85 static struct option long_options[] = {
86 {"data-only", no_argument, NULL, 'a'},
87 {"clean", no_argument, NULL, 'c'},
88 {"inserts", no_argument, NULL, 'd'},
89 {"attribute-inserts", no_argument, NULL, 'D'},
90 {"column-inserts", no_argument, NULL, 'D'},
91 {"globals-only", no_argument, NULL, 'g'},
92 {"host", required_argument, NULL, 'h'},
93 {"ignore-version", no_argument, NULL, 'i'},
94 {"oids", no_argument, NULL, 'o'},
95 {"port", required_argument, NULL, 'p'},
96 {"password", no_argument, NULL, 'W'},
97 {"schema-only", no_argument, NULL, 's'},
98 {"username", required_argument, NULL, 'U'},
99 {"verbose", no_argument, NULL, 'v'},
100 {"no-privileges", no_argument, NULL, 'x'},
101 {"no-acl", no_argument, NULL, 'x'},
107 set_pglocale_pgservice(argv[0], "pg_dump");
109 progname = get_progname(argv[0]);
113 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
118 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
120 puts("pg_dumpall (PostgreSQL) " PG_VERSION);
125 if ((ret = find_other_exec(argv[0], "pg_dump", PG_VERSIONSTR,
130 _("The program \"pg_dump\" is needed by %s "
131 "but was not found in the same directory as \"%s\".\n"
132 "Check your installation.\n"),
136 _("The program \"pg_dump\" was found by %s "
137 "but was not the same version as \"%s\".\n"
138 "Check your installation.\n"),
143 pgdumpopts = createPQExpBuffer();
145 while ((c = getopt_long(argc, argv, "acdDgh:iop:sU:vWx", long_options, &optindex)) != -1)
151 appendPQExpBuffer(pgdumpopts, " -a");
160 appendPQExpBuffer(pgdumpopts, " -%c", c);
169 appendPQExpBuffer(pgdumpopts, " -h '%s'", pghost);
174 appendPQExpBuffer(pgdumpopts, " -%c", c);
179 appendPQExpBuffer(pgdumpopts, " -p '%s'", pgport);
184 appendPQExpBuffer(pgdumpopts, " -s");
189 appendPQExpBuffer(pgdumpopts, " -U '%s'", pguser);
194 appendPQExpBuffer(pgdumpopts, " -v");
198 force_password = true;
199 appendPQExpBuffer(pgdumpopts, " -W");
204 appendPQExpBuffer(pgdumpopts, " -x");
208 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
215 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
216 progname, argv[optind]);
217 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
223 conn = connectDatabase("template1", pghost, pgport, pguser, force_password);
225 printf("--\n-- PostgreSQL database cluster dump\n--\n\n");
227 dumpTimestamp("Started on");
229 printf("\\connect \"template1\"\n\n");
235 if (server_version >= 70500)
236 dumpTablespaces(conn);
249 dumpTimestamp("Completed on");
250 printf("--\n-- PostgreSQL database cluster dump complete\n--\n\n");
260 printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname);
261 printf(_("Usage:\n"));
262 printf(_(" %s [OPTION]...\n"), progname);
264 printf(_("\nOptions:\n"));
265 printf(_(" -a, --data-only dump only the data, not the schema\n"));
266 printf(_(" -c, --clean clean (drop) databases prior to create\n"));
267 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
268 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
269 printf(_(" -g, --globals-only dump only global objects, no databases\n"));
270 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
271 " pg_dumpall version\n"));
272 printf(_(" -s, --schema-only dump only the schema, no data\n"));
273 printf(_(" -o, --oids include OIDs in dump\n"));
274 printf(_(" -v, --verbose verbose mode\n"));
275 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
276 printf(_(" --help show this help, then exit\n"));
277 printf(_(" --version output version information, then exit\n"));
279 printf(_("\nConnection options:\n"));
280 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
281 printf(_(" -p, --port=PORT database server port number\n"));
282 printf(_(" -U, --username=NAME connect as specified database user\n"));
283 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
285 printf(_("\nThe SQL script will be written to the standard output.\n\n"));
286 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
292 * Dump users (but not the user created by initdb).
295 dumpUsers(PGconn *conn)
300 printf("--\n-- Users\n--\n\n");
301 printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
303 if (server_version >= 70100)
304 res = executeQuery(conn,
305 "SELECT usename, usesysid, passwd, usecreatedb, "
306 "usesuper, valuntil "
308 "WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0')");
310 res = executeQuery(conn,
311 "SELECT usename, usesysid, passwd, usecreatedb, "
312 "usesuper, valuntil "
314 "WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template1')");
316 for (i = 0; i < PQntuples(res); i++)
318 PQExpBuffer buf = createPQExpBuffer();
319 const char *username;
321 username = PQgetvalue(res, i, 0);
322 appendPQExpBuffer(buf, "CREATE USER %s WITH SYSID %s",
324 PQgetvalue(res, i, 1));
326 if (!PQgetisnull(res, i, 2))
328 appendPQExpBuffer(buf, " PASSWORD ");
329 appendStringLiteral(buf, PQgetvalue(res, i, 2), true);
332 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
333 appendPQExpBuffer(buf, " CREATEDB");
335 appendPQExpBuffer(buf, " NOCREATEDB");
337 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
338 appendPQExpBuffer(buf, " CREATEUSER");
340 appendPQExpBuffer(buf, " NOCREATEUSER");
342 if (!PQgetisnull(res, i, 5))
343 appendPQExpBuffer(buf, " VALID UNTIL '%s'",
344 PQgetvalue(res, i, 5));
346 appendPQExpBuffer(buf, ";\n");
348 printf("%s", buf->data);
349 destroyPQExpBuffer(buf);
351 if (server_version >= 70300)
352 dumpUserConfig(conn, username);
365 dumpGroups(PGconn *conn)
370 printf("--\n-- Groups\n--\n\n");
371 printf("DELETE FROM pg_group;\n\n");
373 res = executeQuery(conn, "SELECT groname, grosysid, grolist FROM pg_group");
375 for (i = 0; i < PQntuples(res); i++)
377 PQExpBuffer buf = createPQExpBuffer();
381 appendPQExpBuffer(buf, "CREATE GROUP %s WITH SYSID %s;\n",
382 fmtId(PQgetvalue(res, i, 0)),
383 PQgetvalue(res, i, 1));
385 val = strdup(PQgetvalue(res, i, 2));
386 tok = strtok(val, ",{}");
390 PQExpBuffer buf2 = createPQExpBuffer();
393 appendPQExpBuffer(buf2, "SELECT usename FROM pg_shadow WHERE usesysid = %s;", tok);
394 res2 = executeQuery(conn, buf2->data);
395 destroyPQExpBuffer(buf2);
397 for (j = 0; j < PQntuples(res2); j++)
399 appendPQExpBuffer(buf, "ALTER GROUP %s ", fmtId(PQgetvalue(res, i, 0)));
400 appendPQExpBuffer(buf, "ADD USER %s;\n", fmtId(PQgetvalue(res2, j, 0)));
405 tok = strtok(NULL, "{},");
409 printf("%s", buf->data);
410 destroyPQExpBuffer(buf);
421 dumpTablespaces(PGconn *conn)
426 printf("--\n-- Tablespaces\n--\n\n");
429 * Get all tablespaces except for the system default and global
432 res = executeQuery(conn, "SELECT spcname, "
433 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
434 "spclocation, spcacl "
435 "FROM pg_catalog.pg_tablespace "
436 "WHERE spcname NOT IN ('default', 'global')");
438 for (i = 0; i < PQntuples(res); i++)
440 PQExpBuffer buf = createPQExpBuffer();
441 char *spcname = PQgetvalue(res, i, 0);
442 char *spcowner = PQgetvalue(res, i, 1);
443 char *spclocation = PQgetvalue(res, i, 2);
444 char *spcacl = PQgetvalue(res, i, 3);
447 /* needed for buildACLCommands() */
448 fspcname = strdup(fmtId(spcname));
451 appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", fspcname);
453 appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
454 appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
456 appendPQExpBuffer(buf, " LOCATION ");
457 appendStringLiteral(buf, spclocation, true);
458 appendPQExpBuffer(buf, ";\n");
461 !buildACLCommands(fspcname, "TABLESPACE", spcacl, spcowner,
462 server_version, buf))
464 fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
465 progname, spcacl, fspcname);
470 printf("%s", buf->data);
473 destroyPQExpBuffer(buf);
481 * Dump commands to create each database.
483 * To minimize the number of reconnections (and possibly ensuing
484 * password prompts) required by the output script, we emit all CREATE
485 * DATABASE commands during the initial phase of the script, and then
486 * run pg_dump for each database to dump the contents of that
487 * database. We skip databases marked not datallowconn, since we'd be
488 * unable to connect to them anyway (and besides, we don't want to
492 dumpCreateDB(PGconn *conn)
497 printf("--\n-- Database creation\n--\n\n");
499 if (server_version >= 70500)
500 res = executeQuery(conn,
502 "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
503 "pg_encoding_to_char(d.encoding), "
504 "datistemplate, datacl, "
505 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
506 "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
507 "WHERE datallowconn ORDER BY 1");
508 else if (server_version >= 70300)
509 res = executeQuery(conn,
511 "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
512 "pg_encoding_to_char(d.encoding), "
513 "datistemplate, datacl, "
514 "'default' AS dattablespace "
515 "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
516 "WHERE datallowconn ORDER BY 1");
517 else if (server_version >= 70100)
518 res = executeQuery(conn,
521 "(select usename from pg_shadow where usesysid=datdba), "
522 "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
523 "pg_encoding_to_char(d.encoding), "
524 "datistemplate, '' as datacl, "
525 "'default' AS dattablespace "
526 "FROM pg_database d "
527 "WHERE datallowconn ORDER BY 1");
531 * Note: 7.0 fails to cope with sub-select in COALESCE, so just
532 * deal with getting a NULL by not printing any OWNER clause.
534 res = executeQuery(conn,
536 "(select usename from pg_shadow where usesysid=datdba), "
537 "pg_encoding_to_char(d.encoding), "
538 "'f' as datistemplate, "
540 "'default' AS dattablespace "
541 "FROM pg_database d "
545 for (i = 0; i < PQntuples(res); i++)
548 char *dbname = PQgetvalue(res, i, 0);
549 char *dbowner = PQgetvalue(res, i, 1);
550 char *dbencoding = PQgetvalue(res, i, 2);
551 char *dbistemplate = PQgetvalue(res, i, 3);
552 char *dbacl = PQgetvalue(res, i, 4);
553 char *dbtablespace = PQgetvalue(res, i, 5);
556 if (strcmp(dbname, "template1") == 0)
559 buf = createPQExpBuffer();
561 /* needed for buildACLCommands() */
562 fdbname = strdup(fmtId(dbname));
565 appendPQExpBuffer(buf, "DROP DATABASE %s;\n", fdbname);
567 appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
569 appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
571 if (strlen(dbowner) != 0)
572 appendPQExpBuffer(buf, " OWNER = %s",
575 appendPQExpBuffer(buf, " ENCODING = ");
576 appendStringLiteral(buf, dbencoding, true);
578 /* Output tablespace if it isn't default */
579 if (strcmp(dbtablespace, "default") != 0)
580 appendPQExpBuffer(buf, " TABLESPACE = %s",
581 fmtId(dbtablespace));
583 appendPQExpBuffer(buf, ";\n");
585 if (strcmp(dbistemplate, "t") == 0)
587 appendPQExpBuffer(buf, "UPDATE pg_database SET datistemplate = 't' WHERE datname = ");
588 appendStringLiteral(buf, dbname, true);
589 appendPQExpBuffer(buf, ";\n");
593 !buildACLCommands(fdbname, "DATABASE", dbacl, dbowner,
594 server_version, buf))
596 fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
597 progname, dbacl, fdbname);
602 printf("%s", buf->data);
603 destroyPQExpBuffer(buf);
606 if (server_version >= 70300)
607 dumpDatabaseConfig(conn, dbname);
617 * Dump database-specific configuration
620 dumpDatabaseConfig(PGconn *conn, const char *dbname)
622 PQExpBuffer buf = createPQExpBuffer();
629 printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
630 appendStringLiteral(buf, dbname, true);
631 appendPQExpBuffer(buf, ";");
633 res = executeQuery(conn, buf->data);
634 if (!PQgetisnull(res, 0, 0))
636 makeAlterConfigCommand(PQgetvalue(res, 0, 0), "DATABASE", dbname);
647 destroyPQExpBuffer(buf);
653 * Dump user-specific configuration
656 dumpUserConfig(PGconn *conn, const char *username)
658 PQExpBuffer buf = createPQExpBuffer();
665 printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
666 appendStringLiteral(buf, username, true);
667 appendPQExpBuffer(buf, ";");
669 res = executeQuery(conn, buf->data);
670 if (!PQgetisnull(res, 0, 0))
672 makeAlterConfigCommand(PQgetvalue(res, 0, 0), "USER", username);
683 destroyPQExpBuffer(buf);
689 * Helper function for dumpXXXConfig().
692 makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name)
696 PQExpBuffer buf = createPQExpBuffer();
698 mine = strdup(arrayitem);
699 pos = strchr(mine, '=');
704 appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
705 appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
706 appendStringLiteral(buf, pos + 1, false);
707 appendPQExpBuffer(buf, ";\n");
709 printf("%s", buf->data);
710 destroyPQExpBuffer(buf);
717 * Dump contents of databases.
720 dumpDatabases(PGconn *conn)
725 if (server_version >= 70100)
726 res = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1");
728 res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1");
730 for (i = 0; i < PQntuples(res); i++)
734 char *dbname = PQgetvalue(res, i, 0);
737 fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);
739 printf("\\connect %s\n\n", fmtId(dbname));
740 ret = runPgDump(dbname);
743 fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname);
754 * Run pg_dump on dbname.
757 runPgDump(const char *dbname)
759 PQExpBuffer cmd = createPQExpBuffer();
763 appendPQExpBuffer(cmd, "%s\"%s\" %s -Fp '", SYSTEMQUOTE, pg_dump_bin,
766 /* Shell quoting is not quite like SQL quoting, so can't use fmtId */
767 for (p = dbname; *p; p++)
770 appendPQExpBuffer(cmd, "'\"'\"'");
772 appendPQExpBufferChar(cmd, *p);
775 appendPQExpBufferChar(cmd, '\'');
776 appendStringLiteral(cmd, SYSTEMQUOTE, false);
779 fprintf(stderr, _("%s: running \"%s\"\n"), progname, cmd->data);
784 ret = system(cmd->data);
786 destroyPQExpBuffer(cmd);
794 * Make a database connection with the given parameters. An
795 * interactive password prompt is automatically issued if required.
798 connectDatabase(const char *dbname, const char *pghost, const char *pgport,
799 const char *pguser, bool require_password)
802 char *password = NULL;
803 bool need_pass = false;
804 const char *remoteversion_str;
806 if (require_password)
807 password = simple_prompt("Password: ", 100, false);
810 * Start the connection. Loop until we have a password if requested
816 conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, pguser, password);
820 fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
825 if (PQstatus(conn) == CONNECTION_BAD &&
826 strcmp(PQerrorMessage(conn), "fe_sendauth: no password supplied\n") == 0 &&
833 password = simple_prompt("Password: ", 100, false);
840 /* check to see that the backend connection was successfully made */
841 if (PQstatus(conn) == CONNECTION_BAD)
843 fprintf(stderr, _("%s: could not connect to database \"%s\": %s\n"),
844 progname, dbname, PQerrorMessage(conn));
848 remoteversion_str = PQparameterStatus(conn, "server_version");
849 if (!remoteversion_str)
851 fprintf(stderr, _("%s: could not get server version\n"), progname);
854 server_version = parse_version(remoteversion_str);
855 if (server_version < 0)
857 fprintf(stderr, _("%s: could not parse server version \"%s\"\n"),
858 progname, remoteversion_str);
868 * Run a query, return the results, exit program on failure.
871 executeQuery(PGconn *conn, const char *query)
876 fprintf(stderr, _("%s: executing %s\n"), progname, query);
878 res = PQexec(conn, query);
880 PQresultStatus(res) != PGRES_TUPLES_OK)
882 fprintf(stderr, _("%s: query failed: %s"), progname, PQerrorMessage(conn));
883 fprintf(stderr, _("%s: query was: %s\n"), progname, query);
896 dumpTimestamp(char *msg)
899 time_t now = time(NULL);
901 if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
902 printf("-- %s %s\n\n", msg, buf);