granicus.if.org Git - postgresql/blob - src/bin/pg_dump/pg_dumpall.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * pg_dumpall
   4  *
   5  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
   6  * Portions Copyright (c) 1994, Regents of the University of California
   7  *
   8  *
   9  * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.54 2004/10/16 03:10:15 momjian Exp $
  10  *
  11  *-------------------------------------------------------------------------
  12  */
  13
  14 #include "postgres_fe.h"
  15
  16 #include <unistd.h>
  17 #ifdef ENABLE_NLS
  18 #include <locale.h>
  19 #endif
  20 #ifndef HAVE_STRDUP
  21 #include "strdup.h"
  22 #endif
  23 #include <errno.h>
  24 #include <time.h>
  25
  26 #include "getopt_long.h"
  27
  28 #ifndef HAVE_OPTRESET
  29 int                     optreset;
  30 #endif
  31
  32 #include "dumputils.h"
  33 #include "libpq-fe.h"
  34 #include "pg_backup.h"
  35 #include "pqexpbuffer.h"
  36
  37 #define _(x) gettext((x))
  38
  39 /* version string we expect back from postgres */
  40 #define PG_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
  41
  42
  43 static const char *progname;
  44
  45 static void help(void);
  46
  47 static void dumpUsers(PGconn *conn, bool initdbonly);
  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);
  56
  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);
  61
  62 char            pg_dump_bin[MAXPGPATH];
  63 PQExpBuffer pgdumpopts;
  64 bool            output_clean = false;
  65 bool            skip_acls = false;
  66 bool            verbose = false;
  67 int                     server_version;
  68
  69 /* flags for -X long options */
  70 int                     disable_dollar_quoting = 0;
  71 int                     disable_triggers = 0;
  72 int                     use_setsessauth = 0;
  73
  74 int
  75 main(int argc, char *argv[])
  76 {
  77         char       *pghost = NULL;
  78         char       *pgport = NULL;
  79         char       *pguser = NULL;
  80         bool            force_password = false;
  81         bool            data_only = false;
  82         bool            globals_only = false;
  83         bool            schema_only = false;
  84         PGconn     *conn;
  85         int                     c,
  86                                 ret;
  87
  88         static struct option long_options[] = {
  89                 {"data-only", no_argument, NULL, 'a'},
  90                 {"clean", no_argument, NULL, 'c'},
  91                 {"inserts", no_argument, NULL, 'd'},
  92                 {"attribute-inserts", no_argument, NULL, 'D'},
  93                 {"column-inserts", no_argument, NULL, 'D'},
  94                 {"globals-only", no_argument, NULL, 'g'},
  95                 {"host", required_argument, NULL, 'h'},
  96                 {"ignore-version", no_argument, NULL, 'i'},
  97                 {"oids", no_argument, NULL, 'o'},
  98                 {"no-owner", no_argument, NULL, 'O'},
  99                 {"port", required_argument, NULL, 'p'},
 100                 {"password", no_argument, NULL, 'W'},
 101                 {"schema-only", no_argument, NULL, 's'},
 102                 {"superuser", required_argument, NULL, 'S'},
 103                 {"username", required_argument, NULL, 'U'},
 104                 {"verbose", no_argument, NULL, 'v'},
 105                 {"no-privileges", no_argument, NULL, 'x'},
 106                 {"no-acl", no_argument, NULL, 'x'},
 107
 108                 /*
 109                  * the following options don't have an equivalent short option
 110                  * letter, but are available as '-X long-name'
 111                  */
 112                 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
 113                 {"disable-triggers", no_argument, &disable_triggers, 1},
 114                 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
 115
 116                 {NULL, 0, NULL, 0}
 117         };
 118
 119         int                     optindex;
 120
 121         set_pglocale_pgservice(argv[0], "pg_dump");
 122
 123         progname = get_progname(argv[0]);
 124
 125         if (argc > 1)
 126         {
 127                 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
 128                 {
 129                         help();
 130                         exit(0);
 131                 }
 132                 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
 133                 {
 134                         puts("pg_dumpall (PostgreSQL) " PG_VERSION);
 135                         exit(0);
 136                 }
 137         }
 138
 139         if ((ret = find_other_exec(argv[0], "pg_dump", PG_VERSIONSTR,
 140                                                            pg_dump_bin)) < 0)
 141         {
 142                 char full_path[MAXPGPATH];
 143
 144                 if (find_my_exec(argv[0], full_path) < 0)
 145                         StrNCpy(full_path, progname, MAXPGPATH);
 146
 147                 if (ret == -1)
 148                         fprintf(stderr,
 149                                         _("The program \"pg_dump\" is needed by %s "
 150                                           "but was not found in the\n"
 151                                           "same directory as \"%s\".\n"
 152                                           "Check your installation.\n"),
 153                                         progname, full_path);
 154                 else
 155                         fprintf(stderr,
 156                                         _("The program \"pg_dump\" was found by \"%s\"\n"
 157                                           "but was not the same version as %s.\n"
 158                                           "Check your installation.\n"),
 159                                         full_path, progname);
 160                 exit(1);
 161         }
 162
 163         pgdumpopts = createPQExpBuffer();
 164
 165         while ((c = getopt_long(argc, argv, "acdDgh:ioOp:sS:U:vWxX:", long_options, &optindex)) != -1)
 166         {
 167                 switch (c)
 168                 {
 169                         case 'a':
 170                                 data_only = true;
 171                                 appendPQExpBuffer(pgdumpopts, " -a");
 172                                 break;
 173
 174                         case 'c':
 175                                 output_clean = true;
 176                                 break;
 177
 178                         case 'd':
 179                         case 'D':
 180                                 appendPQExpBuffer(pgdumpopts, " -%c", c);
 181                                 break;
 182
 183                         case 'g':
 184                                 globals_only = true;
 185                                 break;
 186
 187                         case 'h':
 188                                 pghost = optarg;
 189 #ifndef WIN32
 190                                 appendPQExpBuffer(pgdumpopts, " -h '%s'", pghost);
 191 #else
 192                                 appendPQExpBuffer(pgdumpopts, " -h \"%s\"", pghost);
 193 #endif
 194
 195                                 break;
 196
 197
 198
 199                         case 'i':
 200                         case 'o':
 201                                 appendPQExpBuffer(pgdumpopts, " -%c", c);
 202                                 break;
 203
 204                         case 'O':
 205                                 appendPQExpBuffer(pgdumpopts, " -O");
 206                                 break;
 207
 208                         case 'p':
 209                                 pgport = optarg;
 210 #ifndef WIN32
 211                                 appendPQExpBuffer(pgdumpopts, " -p '%s'", pgport);
 212 #else
 213                                 appendPQExpBuffer(pgdumpopts, " -p \"%s\"", pgport);
 214 #endif
 215                                 break;
 216
 217                         case 's':
 218                                 schema_only = true;
 219                                 appendPQExpBuffer(pgdumpopts, " -s");
 220                                 break;
 221
 222                         case 'S':
 223 #ifndef WIN32
 224                                 appendPQExpBuffer(pgdumpopts, " -S '%s'", optarg);
 225 #else
 226                                 appendPQExpBuffer(pgdumpopts, " -S \"%s\"", optarg);
 227 #endif
 228                                 break;
 229
 230                         case 'U':
 231                                 pguser = optarg;
 232 #ifndef WIN32
 233                                 appendPQExpBuffer(pgdumpopts, " -U '%s'", pguser);
 234 #else
 235                                 appendPQExpBuffer(pgdumpopts, " -U \"%s\"", pguser);
 236 #endif
 237                                 break;
 238
 239                         case 'v':
 240                                 verbose = true;
 241                                 appendPQExpBuffer(pgdumpopts, " -v");
 242                                 break;
 243
 244                         case 'W':
 245                                 force_password = true;
 246                                 appendPQExpBuffer(pgdumpopts, " -W");
 247                                 break;
 248
 249                         case 'x':
 250                                 skip_acls = true;
 251                                 appendPQExpBuffer(pgdumpopts, " -x");
 252                                 break;
 253
 254                         case 'X':
 255                                 if (strcmp(optarg, "disable-dollar-quoting") == 0)
 256                                         appendPQExpBuffer(pgdumpopts, " -X disable-dollar-quoting");
 257                                 else if (strcmp(optarg, "disable-triggers") == 0)
 258                                         appendPQExpBuffer(pgdumpopts, " -X disable-triggers");
 259                                 else if (strcmp(optarg, "use-set-session-authorization") == 0)
 260                                          /* no-op, still allowed for compatibility */ ;
 261                                 else
 262                                 {
 263                                         fprintf(stderr,
 264                                                         _("%s: invalid -X option -- %s\n"),
 265                                                         progname, optarg);
 266                                         fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 267                                         exit(1);
 268                                 }
 269                                 break;
 270
 271                         case 0:
 272                                 break;
 273
 274                         default:
 275                                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 276                                 exit(1);
 277                 }
 278         }
 279
 280         /* Add long options to the pg_dump argument list */
 281         if (disable_dollar_quoting)
 282                 appendPQExpBuffer(pgdumpopts, " -X disable-dollar-quoting");
 283         if (disable_triggers)
 284                 appendPQExpBuffer(pgdumpopts, " -X disable-triggers");
 285         if (use_setsessauth)
 286                 appendPQExpBuffer(pgdumpopts, " -X use-set-session-authorization");
 287
 288         if (optind < argc)
 289         {
 290                 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
 291                                 progname, argv[optind]);
 292                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 293                                 progname);
 294                 exit(1);
 295         }
 296
 297
 298         conn = connectDatabase("template1", pghost, pgport, pguser, force_password);
 299
 300         printf("--\n-- PostgreSQL database cluster dump\n--\n\n");
 301         if (verbose)
 302                 dumpTimestamp("Started on");
 303
 304         printf("\\connect \"template1\"\n\n");
 305
 306         if (!data_only)
 307         {
 308                 /* Dump all users excluding the initdb user */
 309                 dumpUsers(conn, false);
 310                 dumpGroups(conn);
 311                 if (server_version >= 80000)
 312                         dumpTablespaces(conn);
 313                 if (!globals_only)
 314                         dumpCreateDB(conn);
 315                 /* Dump alter command for initdb user */
 316                 dumpUsers(conn, true);
 317         }
 318
 319         if (!globals_only)
 320                 dumpDatabases(conn);
 321
 322         PQfinish(conn);
 323
 324         if (verbose)
 325                 dumpTimestamp("Completed on");
 326         printf("--\n-- PostgreSQL database cluster dump complete\n--\n\n");
 327
 328         exit(0);
 329 }
 330
 331
 332
 333 static void
 334 help(void)
 335 {
 336         printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname);
 337         printf(_("Usage:\n"));
 338         printf(_("  %s [OPTION]...\n"), progname);
 339
 340         printf(_("\nGeneral options:\n"));
 341         printf(_("  -i, --ignore-version     proceed even when server version mismatches\n"
 342                          "                           pg_dumpall version\n"));
 343         printf(_("  --help                   show this help, then exit\n"));
 344         printf(_("  --version                output version information, then exit\n"));
 345         printf(_("\nOptions controlling the output content:\n"));
 346         printf(_("  -a, --data-only          dump only the data, not the schema\n"));
 347         printf(_("  -c, --clean              clean (drop) databases prior to create\n"));
 348         printf(_("  -d, --inserts            dump data as INSERT, rather than COPY, commands\n"));
 349         printf(_("  -D, --column-inserts     dump data as INSERT commands with column names\n"));
 350         printf(_("  -g, --globals-only       dump only global objects, no databases\n"));
 351         printf(_("  -o, --oids               include OIDs in dump\n"));
 352         printf(_("  -O, --no-owner           do not output commands to set object ownership\n"));
 353         printf(_("  -s, --schema-only        dump only the schema, no data\n"));
 354         printf(_("  -S, --superuser=NAME     specify the superuser user name to use in the dump\n"));
 355         printf(_("  -x, --no-privileges      do not dump privileges (grant/revoke)\n"));
 356         printf(_("  -X disable-dollar-quoting, --disable-dollar-quoting\n"
 357                          "                           disable dollar quoting, use SQL standard quoting\n"));
 358         printf(_("  -X disable-triggers, --disable-triggers\n"
 359                          "                           disable triggers during data-only restore\n"));
 360         printf(_("  -X use-set-session-authorization, --use-set-session-authorization\n"
 361                          "                           use SESSION AUTHORIZATION commands instead of\n"
 362                          "                           OWNER TO commands\n"));
 363
 364         printf(_("\nConnection options:\n"));
 365         printf(_("  -h, --host=HOSTNAME      database server host or socket directory\n"));
 366         printf(_("  -p, --port=PORT          database server port number\n"));
 367         printf(_("  -U, --username=NAME      connect as specified database user\n"));
 368         printf(_("  -W, --password           force password prompt (should happen automatically)\n"));
 369
 370         printf(_("\nThe SQL script will be written to the standard output.\n\n"));
 371         printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
 372 }
 373
 374
 375
 376 /*
 377  * Dump users
 378  * Is able to dump all non initdb users or just the initdb user.
 379  */
 380 static void
 381 dumpUsers(PGconn *conn, bool initdbonly)
 382 {
 383         PGresult   *res;
 384         int                     i;
 385
 386         if (server_version >= 70100)
 387                 res = executeQuery(conn,
 388                                                 "SELECT usename, usesysid, passwd, usecreatedb, "
 389                                                    "usesuper, valuntil, "
 390                                                    "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner "
 391                                                    "FROM pg_shadow");
 392         else
 393                 res = executeQuery(conn,
 394                                                 "SELECT usename, usesysid, passwd, usecreatedb, "
 395                                                    "usesuper, valuntil, "
 396                                                    "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner "
 397                                                    "FROM pg_shadow");
 398
 399         if (PQntuples(res) > 0 || (!initdbonly && output_clean))
 400                 printf("--\n-- Users\n--\n\n");
 401         if (!initdbonly && output_clean)
 402                 printf("DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');\n\n");
 403
 404         for (i = 0; i < PQntuples(res); i++)
 405         {
 406                 const char *username;
 407                 bool            clusterowner;
 408                 PQExpBuffer buf = createPQExpBuffer();
 409
 410                 username = PQgetvalue(res, i, 0);
 411                 clusterowner = (strcmp(PQgetvalue(res, i, 6), "t") == 0);
 412
 413                 /* Check which pass we're on */
 414                 if ((initdbonly && !clusterowner) || (!initdbonly && clusterowner))
 415                         continue;
 416
 417                 /*
 418                  * Dump ALTER USER for the cluster owner and CREATE USER for all
 419                  * other users
 420                  */
 421                 if (!clusterowner)
 422                         appendPQExpBuffer(buf, "CREATE USER %s WITH SYSID %s",
 423                                                           fmtId(username),
 424                                                           PQgetvalue(res, i, 1));
 425                 else
 426                         appendPQExpBuffer(buf, "ALTER USER %s WITH",
 427                                                           fmtId(username));
 428
 429                 if (!PQgetisnull(res, i, 2))
 430                 {
 431                         appendPQExpBuffer(buf, " PASSWORD ");
 432                         appendStringLiteral(buf, PQgetvalue(res, i, 2), true);
 433                 }
 434
 435                 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
 436                         appendPQExpBuffer(buf, " CREATEDB");
 437                 else
 438                         appendPQExpBuffer(buf, " NOCREATEDB");
 439
 440                 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
 441                         appendPQExpBuffer(buf, " CREATEUSER");
 442                 else
 443                         appendPQExpBuffer(buf, " NOCREATEUSER");
 444
 445                 if (!PQgetisnull(res, i, 5))
 446                         appendPQExpBuffer(buf, " VALID UNTIL '%s'",
 447                                                           PQgetvalue(res, i, 5));
 448
 449                 appendPQExpBuffer(buf, ";\n");
 450
 451                 printf("%s", buf->data);
 452                 destroyPQExpBuffer(buf);
 453
 454                 if (server_version >= 70300)
 455                         dumpUserConfig(conn, username);
 456         }
 457
 458         PQclear(res);
 459         printf("\n\n");
 460 }
 461
 462
 463
 464 /*
 465  * Dump groups.
 466  */
 467 static void
 468 dumpGroups(PGconn *conn)
 469 {
 470         PGresult   *res;
 471         int                     i;
 472
 473         res = executeQuery(conn, "SELECT groname, grosysid, grolist FROM pg_group");
 474
 475         if (PQntuples(res) > 0 || output_clean)
 476                 printf("--\n-- Groups\n--\n\n");
 477         if (output_clean)
 478                 printf("DELETE FROM pg_group;\n\n");
 479
 480         for (i = 0; i < PQntuples(res); i++)
 481         {
 482                 PQExpBuffer buf = createPQExpBuffer();
 483                 char       *val;
 484                 char       *tok;
 485
 486                 appendPQExpBuffer(buf, "CREATE GROUP %s WITH SYSID %s;\n",
 487                                                   fmtId(PQgetvalue(res, i, 0)),
 488                                                   PQgetvalue(res, i, 1));
 489
 490                 val = strdup(PQgetvalue(res, i, 2));
 491                 tok = strtok(val, ",{}");
 492                 while (tok)
 493                 {
 494                         PGresult   *res2;
 495                         PQExpBuffer buf2 = createPQExpBuffer();
 496                         int                     j;
 497
 498                         appendPQExpBuffer(buf2, "SELECT usename FROM pg_shadow WHERE usesysid = %s;", tok);
 499                         res2 = executeQuery(conn, buf2->data);
 500                         destroyPQExpBuffer(buf2);
 501
 502                         for (j = 0; j < PQntuples(res2); j++)
 503                         {
 504                                 appendPQExpBuffer(buf, "ALTER GROUP %s ", fmtId(PQgetvalue(res, i, 0)));
 505                                 appendPQExpBuffer(buf, "ADD USER %s;\n", fmtId(PQgetvalue(res2, j, 0)));
 506                         }
 507
 508                         PQclear(res2);
 509
 510                         tok = strtok(NULL, "{},");
 511                 }
 512                 free(val);
 513
 514                 printf("%s", buf->data);
 515                 destroyPQExpBuffer(buf);
 516         }
 517
 518         PQclear(res);
 519         printf("\n\n");
 520 }
 521
 522 /*
 523  * Dump tablespaces.
 524  */
 525 static void
 526 dumpTablespaces(PGconn *conn)
 527 {
 528         PGresult   *res;
 529         int                     i;
 530
 531         /*
 532          * Get all tablespaces except built-in ones (which we assume are named
 533          * pg_xxx)
 534          */
 535         res = executeQuery(conn, "SELECT spcname, "
 536                                          "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
 537                                            "spclocation, spcacl "
 538                                            "FROM pg_catalog.pg_tablespace "
 539                                            "WHERE spcname NOT LIKE 'pg\\_%'");
 540
 541         if (PQntuples(res) > 0)
 542                 printf("--\n-- Tablespaces\n--\n\n");
 543
 544         for (i = 0; i < PQntuples(res); i++)
 545         {
 546                 PQExpBuffer buf = createPQExpBuffer();
 547                 char       *spcname = PQgetvalue(res, i, 0);
 548                 char       *spcowner = PQgetvalue(res, i, 1);
 549                 char       *spclocation = PQgetvalue(res, i, 2);
 550                 char       *spcacl = PQgetvalue(res, i, 3);
 551                 char       *fspcname;
 552
 553                 /* needed for buildACLCommands() */
 554                 fspcname = strdup(fmtId(spcname));
 555
 556                 if (output_clean)
 557                         appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", fspcname);
 558
 559                 appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
 560                 appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
 561
 562                 appendPQExpBuffer(buf, " LOCATION ");
 563                 appendStringLiteral(buf, spclocation, true);
 564                 appendPQExpBuffer(buf, ";\n");
 565
 566                 if (!skip_acls &&
 567                         !buildACLCommands(fspcname, "TABLESPACE", spcacl, spcowner,
 568                                                           server_version, buf))
 569                 {
 570                         fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
 571                                         progname, spcacl, fspcname);
 572                         PQfinish(conn);
 573                         exit(1);
 574                 }
 575
 576                 printf("%s", buf->data);
 577
 578                 free(fspcname);
 579                 destroyPQExpBuffer(buf);
 580         }
 581
 582         PQclear(res);
 583         printf("\n\n");
 584 }
 585
 586 /*
 587  * Dump commands to create each database.
 588  *
 589  * To minimize the number of reconnections (and possibly ensuing
 590  * password prompts) required by the output script, we emit all CREATE
 591  * DATABASE commands during the initial phase of the script, and then
 592  * run pg_dump for each database to dump the contents of that
 593  * database.  We skip databases marked not datallowconn, since we'd be
 594  * unable to connect to them anyway (and besides, we don't want to
 595  * dump template0).
 596  */
 597 static void
 598 dumpCreateDB(PGconn *conn)
 599 {
 600         PGresult   *res;
 601         int                     i;
 602
 603         printf("--\n-- Database creation\n--\n\n");
 604
 605         if (server_version >= 80000)
 606                 res = executeQuery(conn,
 607                                                    "SELECT datname, "
 608                                                    "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
 609                                                    "pg_encoding_to_char(d.encoding), "
 610                                                    "datistemplate, datacl, "
 611                                                    "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
 612                 "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
 613                                                    "WHERE datallowconn ORDER BY 1");
 614         else if (server_version >= 70300)
 615                 res = executeQuery(conn,
 616                                                    "SELECT datname, "
 617                                                    "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
 618                                                    "pg_encoding_to_char(d.encoding), "
 619                                                    "datistemplate, datacl, "
 620                                                    "'pg_default' AS dattablespace "
 621                 "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
 622                                                    "WHERE datallowconn ORDER BY 1");
 623         else if (server_version >= 70100)
 624                 res = executeQuery(conn,
 625                                                    "SELECT datname, "
 626                                                    "coalesce("
 627                                 "(select usename from pg_shadow where usesysid=datdba), "
 628                                                    "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
 629                                                    "pg_encoding_to_char(d.encoding), "
 630                                                    "datistemplate, '' as datacl, "
 631                                                    "'pg_default' AS dattablespace "
 632                                                    "FROM pg_database d "
 633                                                    "WHERE datallowconn ORDER BY 1");
 634         else
 635         {
 636                 /*
 637                  * Note: 7.0 fails to cope with sub-select in COALESCE, so just
 638                  * deal with getting a NULL by not printing any OWNER clause.
 639                  */
 640                 res = executeQuery(conn,
 641                                                    "SELECT datname, "
 642                                 "(select usename from pg_shadow where usesysid=datdba), "
 643                                                    "pg_encoding_to_char(d.encoding), "
 644                                                    "'f' as datistemplate, "
 645                                                    "'' as datacl, "
 646                                                    "'pg_default' AS dattablespace "
 647                                                    "FROM pg_database d "
 648                                                    "ORDER BY 1");
 649         }
 650
 651         for (i = 0; i < PQntuples(res); i++)
 652         {
 653                 PQExpBuffer buf;
 654                 char       *dbname = PQgetvalue(res, i, 0);
 655                 char       *dbowner = PQgetvalue(res, i, 1);
 656                 char       *dbencoding = PQgetvalue(res, i, 2);
 657                 char       *dbistemplate = PQgetvalue(res, i, 3);
 658                 char       *dbacl = PQgetvalue(res, i, 4);
 659                 char       *dbtablespace = PQgetvalue(res, i, 5);
 660                 char       *fdbname;
 661
 662                 if (strcmp(dbname, "template1") == 0)
 663                         continue;
 664
 665                 buf = createPQExpBuffer();
 666
 667                 /* needed for buildACLCommands() */
 668                 fdbname = strdup(fmtId(dbname));
 669
 670                 if (output_clean)
 671                         appendPQExpBuffer(buf, "DROP DATABASE %s;\n", fdbname);
 672
 673                 appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
 674
 675                 appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
 676
 677                 if (strlen(dbowner) != 0)
 678                         appendPQExpBuffer(buf, " OWNER = %s",
 679                                                           fmtId(dbowner));
 680
 681                 appendPQExpBuffer(buf, " ENCODING = ");
 682                 appendStringLiteral(buf, dbencoding, true);
 683
 684                 /* Output tablespace if it isn't default */
 685                 if (strcmp(dbtablespace, "pg_default") != 0)
 686                         appendPQExpBuffer(buf, " TABLESPACE = %s",
 687                                                           fmtId(dbtablespace));
 688
 689                 appendPQExpBuffer(buf, ";\n");
 690
 691                 if (strcmp(dbistemplate, "t") == 0)
 692                 {
 693                         appendPQExpBuffer(buf, "UPDATE pg_database SET datistemplate = 't' WHERE datname = ");
 694                         appendStringLiteral(buf, dbname, true);
 695                         appendPQExpBuffer(buf, ";\n");
 696                 }
 697
 698                 if (!skip_acls &&
 699                         !buildACLCommands(fdbname, "DATABASE", dbacl, dbowner,
 700                                                           server_version, buf))
 701                 {
 702                         fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
 703                                         progname, dbacl, fdbname);
 704                         PQfinish(conn);
 705                         exit(1);
 706                 }
 707
 708                 printf("%s", buf->data);
 709                 destroyPQExpBuffer(buf);
 710                 free(fdbname);
 711
 712                 if (server_version >= 70300)
 713                         dumpDatabaseConfig(conn, dbname);
 714         }
 715
 716         PQclear(res);
 717         printf("\n\n");
 718 }
 719
 720
 721
 722 /*
 723  * Dump database-specific configuration
 724  */
 725 static void
 726 dumpDatabaseConfig(PGconn *conn, const char *dbname)
 727 {
 728         PQExpBuffer buf = createPQExpBuffer();
 729         int                     count = 1;
 730
 731         for (;;)
 732         {
 733                 PGresult   *res;
 734
 735                 printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
 736                 appendStringLiteral(buf, dbname, true);
 737                 appendPQExpBuffer(buf, ";");
 738
 739                 res = executeQuery(conn, buf->data);
 740                 if (!PQgetisnull(res, 0, 0))
 741                 {
 742                         makeAlterConfigCommand(PQgetvalue(res, 0, 0), "DATABASE", dbname);
 743                         PQclear(res);
 744                         count++;
 745                 }
 746                 else
 747                 {
 748                         PQclear(res);
 749                         break;
 750                 }
 751         }
 752
 753         destroyPQExpBuffer(buf);
 754 }
 755
 756
 757
 758 /*
 759  * Dump user-specific configuration
 760  */
 761 static void
 762 dumpUserConfig(PGconn *conn, const char *username)
 763 {
 764         PQExpBuffer buf = createPQExpBuffer();
 765         int                     count = 1;
 766
 767         for (;;)
 768         {
 769                 PGresult   *res;
 770
 771                 printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
 772                 appendStringLiteral(buf, username, true);
 773                 appendPQExpBuffer(buf, ";");
 774
 775                 res = executeQuery(conn, buf->data);
 776                 if (!PQgetisnull(res, 0, 0))
 777                 {
 778                         makeAlterConfigCommand(PQgetvalue(res, 0, 0), "USER", username);
 779                         PQclear(res);
 780                         count++;
 781                 }
 782                 else
 783                 {
 784                         PQclear(res);
 785                         break;
 786                 }
 787         }
 788
 789         destroyPQExpBuffer(buf);
 790 }
 791
 792
 793
 794 /*
 795  * Helper function for dumpXXXConfig().
 796  */
 797 static void
 798 makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name)
 799 {
 800         char       *pos;
 801         char       *mine;
 802         PQExpBuffer buf = createPQExpBuffer();
 803
 804         mine = strdup(arrayitem);
 805         pos = strchr(mine, '=');
 806         if (pos == NULL)
 807                 return;
 808
 809         *pos = 0;
 810         appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
 811         appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
 812
 813         /*
 814          * Some GUC variable names are 'LIST' type and hence must not be
 815          * quoted.
 816          */
 817         if (strcasecmp(mine, "DateStyle") == 0
 818                 || strcasecmp(mine, "search_path") == 0)
 819                 appendPQExpBuffer(buf, "%s", pos + 1);
 820         else
 821                 appendStringLiteral(buf, pos + 1, false);
 822         appendPQExpBuffer(buf, ";\n");
 823
 824         printf("%s", buf->data);
 825         destroyPQExpBuffer(buf);
 826         free(mine);
 827 }
 828
 829
 830
 831 /*
 832  * Dump contents of databases.
 833  */
 834 static void
 835 dumpDatabases(PGconn *conn)
 836 {
 837         PGresult   *res;
 838         int                     i;
 839
 840         if (server_version >= 70100)
 841                 res = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1");
 842         else
 843                 res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1");
 844
 845         for (i = 0; i < PQntuples(res); i++)
 846         {
 847                 int                     ret;
 848
 849                 char       *dbname = PQgetvalue(res, i, 0);
 850
 851                 if (verbose)
 852                         fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);
 853
 854                 printf("\\connect %s\n\n", fmtId(dbname));
 855                 ret = runPgDump(dbname);
 856                 if (ret != 0)
 857                 {
 858                         fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname);
 859                         exit(1);
 860                 }
 861         }
 862
 863         PQclear(res);
 864 }
 865
 866
 867
 868 /*
 869  * Run pg_dump on dbname.
 870  */
 871 static int
 872 runPgDump(const char *dbname)
 873 {
 874         PQExpBuffer cmd = createPQExpBuffer();
 875         const char *p;
 876         int                     ret;
 877
 878         /*
 879          * Win32 has to use double-quotes for args, rather than single quotes.
 880          * Strangely enough, this is the only place we pass a database name on
 881          * the command line, except template1 that doesn't need quoting.
 882          */
 883 #ifndef WIN32
 884         appendPQExpBuffer(cmd, "%s\"%s\" %s -Fp '", SYSTEMQUOTE, pg_dump_bin,
 885 #else
 886         appendPQExpBuffer(cmd, "%s\"%s\" %s -Fp \"", SYSTEMQUOTE, pg_dump_bin,
 887 #endif
 888                                           pgdumpopts->data);
 889
 890         /* Shell quoting is not quite like SQL quoting, so can't use fmtId */
 891         for (p = dbname; *p; p++)
 892         {
 893 #ifndef WIN32
 894                 if (*p == '\'')
 895                         appendPQExpBuffer(cmd, "'\"'\"'");
 896 #else
 897                 if (*p == '"')
 898                         appendPQExpBuffer(cmd, "\\\"");
 899 #endif
 900                 else
 901                         appendPQExpBufferChar(cmd, *p);
 902         }
 903
 904 #ifndef WIN32
 905         appendPQExpBufferChar(cmd, '\'');
 906 #else
 907         appendPQExpBufferChar(cmd, '"');
 908 #endif
 909
 910         appendPQExpBuffer(cmd, "%s", SYSTEMQUOTE);
 911
 912         if (verbose)
 913                 fprintf(stderr, _("%s: running \"%s\"\n"), progname, cmd->data);
 914
 915         fflush(stdout);
 916         fflush(stderr);
 917
 918         ret = system(cmd->data);
 919
 920         destroyPQExpBuffer(cmd);
 921
 922         return ret;
 923 }
 924
 925
 926
 927 /*
 928  * Make a database connection with the given parameters.  An
 929  * interactive password prompt is automatically issued if required.
 930  */
 931 static PGconn *
 932 connectDatabase(const char *dbname, const char *pghost, const char *pgport,
 933                                 const char *pguser, bool require_password)
 934 {
 935         PGconn     *conn;
 936         char       *password = NULL;
 937         bool            need_pass = false;
 938         const char *remoteversion_str;
 939
 940         if (require_password)
 941                 password = simple_prompt("Password: ", 100, false);
 942
 943         /*
 944          * Start the connection.  Loop until we have a password if requested
 945          * by backend.
 946          */
 947         do
 948         {
 949                 need_pass = false;
 950                 conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, pguser, password);
 951
 952                 if (!conn)
 953                 {
 954                         fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
 955                                         progname, dbname);
 956                         exit(1);
 957                 }
 958
 959                 if (PQstatus(conn) == CONNECTION_BAD &&
 960                         strcmp(PQerrorMessage(conn), PQnoPasswordSupplied) == 0 &&
 961                         !feof(stdin))
 962                 {
 963                         PQfinish(conn);
 964                         need_pass = true;
 965                         free(password);
 966                         password = NULL;
 967                         password = simple_prompt("Password: ", 100, false);
 968                 }
 969         } while (need_pass);
 970
 971         if (password)
 972                 free(password);
 973
 974         /* check to see that the backend connection was successfully made */
 975         if (PQstatus(conn) == CONNECTION_BAD)
 976         {
 977                 fprintf(stderr, _("%s: could not connect to database \"%s\": %s\n"),
 978                                 progname, dbname, PQerrorMessage(conn));
 979                 exit(1);
 980         }
 981
 982         remoteversion_str = PQparameterStatus(conn, "server_version");
 983         if (!remoteversion_str)
 984         {
 985                 fprintf(stderr, _("%s: could not get server version\n"), progname);
 986                 exit(1);
 987         }
 988         server_version = parse_version(remoteversion_str);
 989         if (server_version < 0)
 990         {
 991                 fprintf(stderr, _("%s: could not parse server version \"%s\"\n"),
 992                                 progname, remoteversion_str);
 993                 exit(1);
 994         }
 995
 996         return conn;
 997 }
 998
 999
1000
1001 /*
1002  * Run a query, return the results, exit program on failure.
1003  */
1004 static PGresult *
1005 executeQuery(PGconn *conn, const char *query)
1006 {
1007         PGresult   *res;
1008
1009         if (verbose)
1010                 fprintf(stderr, _("%s: executing %s\n"), progname, query);
1011
1012         res = PQexec(conn, query);
1013         if (!res ||
1014                 PQresultStatus(res) != PGRES_TUPLES_OK)
1015         {
1016                 fprintf(stderr, _("%s: query failed: %s"), progname, PQerrorMessage(conn));
1017                 fprintf(stderr, _("%s: query was: %s\n"), progname, query);
1018                 PQfinish(conn);
1019                 exit(1);
1020         }
1021
1022         return res;
1023 }
1024
1025
1026 /*
1027  * dumpTimestamp
1028  */
1029 static void
1030 dumpTimestamp(char *msg)
1031 {
1032         char            buf[256];
1033         time_t          now = time(NULL);
1034
1035         if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
1036                 printf("-- %s %s\n\n", msg, buf);
1037 }