src/bin/pg_dump/pg_dumpall.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * pg_dumpall.c
   4  *
   5  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
   6  * Portions Copyright (c) 1994, Regents of the University of California
   7  *
   8  *
   9  * $PostgreSQL$
  10  *
  11  *-------------------------------------------------------------------------
  12  */
  13
  14 #include "postgres_fe.h"
  15
  16 #include <time.h>
  17 #include <unistd.h>
  18
  19 #ifdef ENABLE_NLS
  20 #include <locale.h>
  21 #endif
  22
  23 #include "getopt_long.h"
  24
  25 #include "dumputils.h"
  26 #include "pg_backup.h"
  27
  28 /* version string we expect back from pg_dump */
  29 #define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
  30
  31
  32 static const char *progname;
  33
  34 static void help(void);
  35
  36 static void dropRoles(PGconn *conn);
  37 static void dumpRoles(PGconn *conn);
  38 static void dumpRoleMembership(PGconn *conn);
  39 static void dumpGroups(PGconn *conn);
  40 static void dropTablespaces(PGconn *conn);
  41 static void dumpTablespaces(PGconn *conn);
  42 static void dropDBs(PGconn *conn);
  43 static void dumpCreateDB(PGconn *conn);
  44 static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
  45 static void dumpUserConfig(PGconn *conn, const char *username);
  46 static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
  47                                            const char *type, const char *name);
  48 static void dumpDatabases(PGconn *conn);
  49 static void dumpTimestamp(char *msg);
  50 static void doShellQuoting(PQExpBuffer buf, const char *str);
  51
  52 static int      runPgDump(const char *dbname);
  53 static PGconn *connectDatabase(const char *dbname, const char *pghost, const char *pgport,
  54           const char *pguser, enum trivalue prompt_password, bool fail_on_error);
  55 static PGresult *executeQuery(PGconn *conn, const char *query);
  56 static void executeCommand(PGconn *conn, const char *query);
  57
  58 static char pg_dump_bin[MAXPGPATH];
  59 static PQExpBuffer pgdumpopts;
  60 static bool skip_acls = false;
  61 static bool verbose = false;
  62
  63 static int      binary_upgrade = 0;
  64 static int      column_inserts = 0;
  65 static int      disable_dollar_quoting = 0;
  66 static int      disable_triggers = 0;
  67 static int      inserts = 0;
  68 static int      no_tablespaces = 0;
  69 static int      use_setsessauth = 0;
  70 static int      server_version;
  71
  72 static FILE *OPF;
  73 static char *filename = NULL;
  74
  75
  76 int
  77 main(int argc, char *argv[])
  78 {
  79         char       *pghost = NULL;
  80         char       *pgport = NULL;
  81         char       *pguser = NULL;
  82         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         bool            output_clean = false;
  88         bool            roles_only = false;
  89         bool            tablespaces_only = false;
  90         bool            schema_only = false;
  91         PGconn     *conn;
  92         int                     encoding;
  93         const char *std_strings;
  94         int                     c,
  95                                 ret;
  96         int                     optindex;
  97
  98         static struct option long_options[] = {
  99                 {"data-only", no_argument, NULL, 'a'},
 100                 {"clean", no_argument, NULL, 'c'},
 101                 {"file", required_argument, NULL, 'f'},
 102                 {"globals-only", no_argument, NULL, 'g'},
 103                 {"host", required_argument, NULL, 'h'},
 104                 {"ignore-version", no_argument, NULL, 'i'},
 105                 {"database", required_argument, NULL, 'l'},
 106                 {"oids", no_argument, NULL, 'o'},
 107                 {"no-owner", no_argument, NULL, 'O'},
 108                 {"port", required_argument, NULL, 'p'},
 109                 {"roles-only", no_argument, NULL, 'r'},
 110                 {"schema-only", no_argument, NULL, 's'},
 111                 {"superuser", required_argument, NULL, 'S'},
 112                 {"tablespaces-only", no_argument, NULL, 't'},
 113                 {"username", required_argument, NULL, 'U'},
 114                 {"verbose", no_argument, NULL, 'v'},
 115                 {"no-password", no_argument, NULL, 'w'},
 116                 {"password", no_argument, NULL, 'W'},
 117                 {"no-privileges", no_argument, NULL, 'x'},
 118                 {"no-acl", no_argument, NULL, 'x'},
 119
 120                 /*
 121                  * the following options don't have an equivalent short option letter
 122                  */
 123                 {"attribute-inserts", no_argument, &column_inserts, 1},
 124                 {"binary-upgrade", no_argument, &binary_upgrade, 1},
 125                 {"column-inserts", no_argument, &column_inserts, 1},
 126                 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
 127                 {"disable-triggers", no_argument, &disable_triggers, 1},
 128                 {"inserts", no_argument, &inserts, 1},
 129                 {"lock-wait-timeout", required_argument, NULL, 2},
 130                 {"no-tablespaces", no_argument, &no_tablespaces, 1},
 131                 {"role", required_argument, NULL, 3},
 132                 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
 133
 134                 {NULL, 0, NULL, 0}
 135         };
 136
 137         set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_dump"));
 138
 139         progname = get_progname(argv[0]);
 140
 141         if (argc > 1)
 142         {
 143                 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
 144                 {
 145                         help();
 146                         exit(0);
 147                 }
 148                 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
 149                 {
 150                         puts("pg_dumpall (PostgreSQL) " PG_VERSION);
 151                         exit(0);
 152                 }
 153         }
 154
 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                 if (ret == -1)
 164                         fprintf(stderr,
 165                                         _("The program \"pg_dump\" is needed by %s "
 166                                           "but was not found in the\n"
 167                                           "same directory as \"%s\".\n"
 168                                           "Check your installation.\n"),
 169                                         progname, full_path);
 170                 else
 171                         fprintf(stderr,
 172                                         _("The program \"pg_dump\" was found by \"%s\"\n"
 173                                           "but was not the same version as %s.\n"
 174                                           "Check your installation.\n"),
 175                                         full_path, progname);
 176                 exit(1);
 177         }
 178
 179         pgdumpopts = createPQExpBuffer();
 180
 181         while ((c = getopt_long(argc, argv, "acf:gh:il:oOp:rsS:tU:vwWxX:", long_options, &optindex)) != -1)
 182         {
 183                 switch (c)
 184                 {
 185                         case 'a':
 186                                 data_only = true;
 187                                 appendPQExpBuffer(pgdumpopts, " -a");
 188                                 break;
 189
 190                         case 'c':
 191                                 output_clean = true;
 192                                 break;
 193
 194                         case 'f':
 195                                 filename = optarg;
 196                                 appendPQExpBuffer(pgdumpopts, " -f ");
 197                                 doShellQuoting(pgdumpopts, filename);
 198                                 break;
 199
 200                         case 'g':
 201                                 globals_only = true;
 202                                 break;
 203
 204                         case 'h':
 205                                 pghost = optarg;
 206                                 appendPQExpBuffer(pgdumpopts, " -h ");
 207                                 doShellQuoting(pgdumpopts, pghost);
 208                                 break;
 209
 210                         case 'i':
 211                                 /* ignored, deprecated option */
 212                                 break;
 213
 214                         case 'l':
 215                                 pgdb = optarg;
 216                                 break;
 217
 218                         case 'o':
 219                                 appendPQExpBuffer(pgdumpopts, " -o");
 220                                 break;
 221
 222                         case 'O':
 223                                 appendPQExpBuffer(pgdumpopts, " -O");
 224                                 break;
 225
 226                         case 'p':
 227                                 pgport = optarg;
 228                                 appendPQExpBuffer(pgdumpopts, " -p ");
 229                                 doShellQuoting(pgdumpopts, pgport);
 230                                 break;
 231
 232                         case 'r':
 233                                 roles_only = true;
 234                                 break;
 235
 236                         case 's':
 237                                 schema_only = true;
 238                                 appendPQExpBuffer(pgdumpopts, " -s");
 239                                 break;
 240
 241                         case 'S':
 242                                 appendPQExpBuffer(pgdumpopts, " -S ");
 243                                 doShellQuoting(pgdumpopts, optarg);
 244                                 break;
 245
 246                         case 't':
 247                                 tablespaces_only = true;
 248                                 break;
 249
 250                         case 'U':
 251                                 pguser = optarg;
 252                                 appendPQExpBuffer(pgdumpopts, " -U ");
 253                                 doShellQuoting(pgdumpopts, pguser);
 254                                 break;
 255
 256                         case 'v':
 257                                 verbose = true;
 258                                 appendPQExpBuffer(pgdumpopts, " -v");
 259                                 break;
 260
 261                         case 'w':
 262                                 prompt_password = TRI_NO;
 263                                 appendPQExpBuffer(pgdumpopts, " -w");
 264                                 break;
 265
 266                         case 'W':
 267                                 prompt_password = TRI_YES;
 268                                 appendPQExpBuffer(pgdumpopts, " -W");
 269                                 break;
 270
 271                         case 'x':
 272                                 skip_acls = true;
 273                                 appendPQExpBuffer(pgdumpopts, " -x");
 274                                 break;
 275
 276                         case 'X':
 277                                 /* -X is a deprecated alternative to long options */
 278                                 if (strcmp(optarg, "disable-dollar-quoting") == 0)
 279                                         disable_dollar_quoting = 1;
 280                                 else if (strcmp(optarg, "disable-triggers") == 0)
 281                                         disable_triggers = 1;
 282                                 else if (strcmp(optarg, "no-tablespaces") == 0)
 283                                         no_tablespaces = 1;
 284                                 else if (strcmp(optarg, "use-set-session-authorization") == 0)
 285                                         use_setsessauth = 1;
 286                                 else
 287                                 {
 288                                         fprintf(stderr,
 289                                                         _("%s: invalid -X option -- %s\n"),
 290                                                         progname, optarg);
 291                                         fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 292                                         exit(1);
 293                                 }
 294                                 break;
 295
 296                         case 0:
 297                                 break;
 298
 299                         case 2:
 300                                 appendPQExpBuffer(pgdumpopts, " --lock-wait-timeout ");
 301                                 doShellQuoting(pgdumpopts, optarg);
 302                                 break;
 303
 304                         case 3:
 305                                 use_role = optarg;
 306                                 appendPQExpBuffer(pgdumpopts, " --role ");
 307                                 doShellQuoting(pgdumpopts, use_role);
 308                                 break;
 309
 310                         default:
 311                                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 312                                 exit(1);
 313                 }
 314         }
 315
 316         /* Add long options to the pg_dump argument list */
 317         if (binary_upgrade)
 318                 appendPQExpBuffer(pgdumpopts, " --binary-upgrade");
 319         if (column_inserts)
 320                 appendPQExpBuffer(pgdumpopts, " --column-inserts");
 321         if (disable_dollar_quoting)
 322                 appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting");
 323         if (disable_triggers)
 324                 appendPQExpBuffer(pgdumpopts, " --disable-triggers");
 325         if (inserts)
 326                 appendPQExpBuffer(pgdumpopts, " --inserts");
 327         if (no_tablespaces)
 328                 appendPQExpBuffer(pgdumpopts, " --no-tablespaces");
 329         if (use_setsessauth)
 330                 appendPQExpBuffer(pgdumpopts, " --use-set-session-authorization");
 331
 332         if (optind < argc)
 333         {
 334                 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
 335                                 progname, argv[optind]);
 336                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 337                                 progname);
 338                 exit(1);
 339         }
 340
 341         /* Make sure the user hasn't specified a mix of globals-only options */
 342         if (globals_only && roles_only)
 343         {
 344                 fprintf(stderr, _("%s: options -g/--globals-only and -r/--roles-only cannot be used together\n"),
 345                                 progname);
 346                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 347                                 progname);
 348                 exit(1);
 349         }
 350
 351         if (globals_only && tablespaces_only)
 352         {
 353                 fprintf(stderr, _("%s: options -g/--globals-only and -t/--tablespaces-only cannot be used together\n"),
 354                                 progname);
 355                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 356                                 progname);
 357                 exit(1);
 358         }
 359
 360         if (roles_only && tablespaces_only)
 361         {
 362                 fprintf(stderr, _("%s: options -r/--roles-only and -t/--tablespaces-only cannot be used together\n"),
 363                                 progname);
 364                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 365                                 progname);
 366                 exit(1);
 367         }
 368
 369         /*
 370          * If there was a database specified on the command line, use that,
 371          * otherwise try to connect to database "postgres", and failing that
 372          * "template1".  "postgres" is the preferred choice for 8.1 and later
 373          * servers, but it usually will not exist on older ones.
 374          */
 375         if (pgdb)
 376         {
 377                 conn = connectDatabase(pgdb, pghost, pgport, pguser,
 378                                                            prompt_password, false);
 379
 380                 if (!conn)
 381                 {
 382                         fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
 383                                         progname, pgdb);
 384                         exit(1);
 385                 }
 386         }
 387         else
 388         {
 389                 conn = connectDatabase("postgres", pghost, pgport, pguser,
 390                                                            prompt_password, false);
 391                 if (!conn)
 392                         conn = connectDatabase("template1", pghost, pgport, pguser,
 393                                                                    prompt_password, true);
 394
 395                 if (!conn)
 396                 {
 397                         fprintf(stderr, _("%s: could not connect to databases \"postgres\" or \"template1\"\n"
 398                                                           "Please specify an alternative database.\n"),
 399                                         progname);
 400                         fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
 401                                         progname);
 402                         exit(1);
 403                 }
 404         }
 405
 406         /*
 407          * Open the output file if required, otherwise use stdout
 408          */
 409         if (filename)
 410         {
 411                 OPF = fopen(filename, PG_BINARY_W);
 412                 if (!OPF)
 413                 {
 414                         fprintf(stderr, _("%s: could not open the output file \"%s\": %s\n"),
 415                                         progname, filename, strerror(errno));
 416                         exit(1);
 417                 }
 418         }
 419         else
 420                 OPF = stdout;
 421
 422         /*
 423          * Get the active encoding and the standard_conforming_strings setting, so
 424          * we know how to escape strings.
 425          */
 426         encoding = PQclientEncoding(conn);
 427         std_strings = PQparameterStatus(conn, "standard_conforming_strings");
 428         if (!std_strings)
 429                 std_strings = "off";
 430
 431         /* Set the role if requested */
 432         if (use_role && server_version >= 80100)
 433         {
 434                 PQExpBuffer query = createPQExpBuffer();
 435
 436                 appendPQExpBuffer(query, "SET ROLE %s", fmtId(use_role));
 437                 executeCommand(conn, query->data);
 438                 destroyPQExpBuffer(query);
 439         }
 440
 441         fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
 442         if (verbose)
 443                 dumpTimestamp("Started on");
 444
 445         fprintf(OPF, "\\connect postgres\n\n");
 446
 447         /* Replicate encoding and std_strings in output */
 448         fprintf(OPF, "SET client_encoding = '%s';\n",
 449                         pg_encoding_to_char(encoding));
 450         fprintf(OPF, "SET standard_conforming_strings = %s;\n", std_strings);
 451         if (strcmp(std_strings, "off") == 0)
 452                 fprintf(OPF, "SET escape_string_warning = off;\n");
 453         fprintf(OPF, "\n");
 454
 455         if (!data_only)
 456         {
 457                 /*
 458                  * If asked to --clean, do that first.  We can avoid detailed
 459                  * dependency analysis because databases never depend on each other,
 460                  * and tablespaces never depend on each other.  Roles could have
 461                  * grants to each other, but DROP ROLE will clean those up silently.
 462                  */
 463                 if (output_clean)
 464                 {
 465                         if (!globals_only && !roles_only && !tablespaces_only)
 466                                 dropDBs(conn);
 467
 468                         if (!roles_only && !no_tablespaces)
 469                         {
 470                                 if (server_version >= 80000)
 471                                         dropTablespaces(conn);
 472                         }
 473
 474                         if (!tablespaces_only)
 475                                 dropRoles(conn);
 476                 }
 477
 478                 /*
 479                  * Now create objects as requested.  Be careful that option logic here
 480                  * is the same as for drops above.
 481                  */
 482                 if (!tablespaces_only)
 483                 {
 484                         /* Dump roles (users) */
 485                         dumpRoles(conn);
 486
 487                         /* Dump role memberships --- need different method for pre-8.1 */
 488                         if (server_version >= 80100)
 489                                 dumpRoleMembership(conn);
 490                         else
 491                                 dumpGroups(conn);
 492                 }
 493
 494                 if (!roles_only && !no_tablespaces)
 495                 {
 496                         /* Dump tablespaces */
 497                         if (server_version >= 80000)
 498                                 dumpTablespaces(conn);
 499                 }
 500
 501                 /* Dump CREATE DATABASE commands */
 502                 if (!globals_only && !roles_only && !tablespaces_only)
 503                         dumpCreateDB(conn);
 504         }
 505
 506         if (!globals_only && !roles_only && !tablespaces_only)
 507                 dumpDatabases(conn);
 508
 509         PQfinish(conn);
 510
 511         if (verbose)
 512                 dumpTimestamp("Completed on");
 513         fprintf(OPF, "--\n-- PostgreSQL database cluster dump complete\n--\n\n");
 514
 515         if (filename)
 516                 fclose(OPF);
 517
 518         exit(0);
 519 }
 520
 521
 522 static void
 523 help(void)
 524 {
 525         printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname);
 526         printf(_("Usage:\n"));
 527         printf(_("  %s [OPTION]...\n"), progname);
 528
 529         printf(_("\nGeneral options:\n"));
 530         printf(_("  -f, --file=FILENAME         output file name\n"));
 531         printf(_("  --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock\n"));
 532         printf(_("  --help                      show this help, then exit\n"));
 533         printf(_("  --version                   output version information, then exit\n"));
 534         printf(_("\nOptions controlling the output content:\n"));
 535         printf(_("  -a, --data-only             dump only the data, not the schema\n"));
 536         printf(_("  -c, --clean                 clean (drop) databases before recreating\n"));
 537         printf(_("  -g, --globals-only          dump only global objects, no databases\n"));
 538         printf(_("  -o, --oids                  include OIDs in dump\n"));
 539         printf(_("  -O, --no-owner              skip restoration of object ownership\n"));
 540         printf(_("  -r, --roles-only            dump only roles, no databases or tablespaces\n"));
 541         printf(_("  -s, --schema-only           dump only the schema, no data\n"));
 542         printf(_("  -S, --superuser=NAME        superuser user name to use in the dump\n"));
 543         printf(_("  -t, --tablespaces-only      dump only tablespaces, no databases or roles\n"));
 544         printf(_("  -x, --no-privileges         do not dump privileges (grant/revoke)\n"));
 545         printf(_("  --binary-upgrade            for use by upgrade utilities only\n"));
 546         printf(_("  --inserts                   dump data as INSERT commands, rather than COPY\n"));
 547         printf(_("  --column-inserts            dump data as INSERT commands with column names\n"));
 548         printf(_("  --disable-dollar-quoting    disable dollar quoting, use SQL standard quoting\n"));
 549         printf(_("  --disable-triggers          disable triggers during data-only restore\n"));
 550         printf(_("  --no-tablespaces            do not dump tablespace assignments\n"));
 551         printf(_("  --role=ROLENAME             do SET ROLE before dump\n"));
 552         printf(_("  --use-set-session-authorization\n"
 553                          "                              use SET SESSION AUTHORIZATION commands instead of\n"
 554         "                              ALTER OWNER commands to set ownership\n"));
 555
 556         printf(_("\nConnection options:\n"));
 557         printf(_("  -h, --host=HOSTNAME      database server host or socket directory\n"));
 558         printf(_("  -l, --database=DBNAME    alternative default database\n"));
 559         printf(_("  -p, --port=PORT          database server port number\n"));
 560         printf(_("  -U, --username=NAME      connect as specified database user\n"));
 561         printf(_("  -w, --no-password        never prompt for password\n"));
 562         printf(_("  -W, --password           force password prompt (should happen automatically)\n"));
 563
 564         printf(_("\nIf -f/--file is not used, then the SQL script will be written to the standard\n"
 565                          "output.\n\n"));
 566         printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
 567 }
 568
 569
 570 /*
 571  * Drop roles
 572  */
 573 static void
 574 dropRoles(PGconn *conn)
 575 {
 576         PGresult   *res;
 577         int                     i_rolname;
 578         int                     i;
 579
 580         if (server_version >= 80100)
 581                 res = executeQuery(conn,
 582                                                    "SELECT rolname "
 583                                                    "FROM pg_authid "
 584                                                    "ORDER BY 1");
 585         else
 586                 res = executeQuery(conn,
 587                                                    "SELECT usename as rolname "
 588                                                    "FROM pg_shadow "
 589                                                    "UNION "
 590                                                    "SELECT groname as rolname "
 591                                                    "FROM pg_group "
 592                                                    "ORDER BY 1");
 593
 594         i_rolname = PQfnumber(res, "rolname");
 595
 596         if (PQntuples(res) > 0)
 597                 fprintf(OPF, "--\n-- Drop roles\n--\n\n");
 598
 599         for (i = 0; i < PQntuples(res); i++)
 600         {
 601                 const char *rolename;
 602
 603                 rolename = PQgetvalue(res, i, i_rolname);
 604
 605                 fprintf(OPF, "DROP ROLE %s;\n", fmtId(rolename));
 606         }
 607
 608         PQclear(res);
 609
 610         fprintf(OPF, "\n\n");
 611 }
 612
 613 /*
 614  * Dump roles
 615  */
 616 static void
 617 dumpRoles(PGconn *conn)
 618 {
 619         PQExpBuffer buf = createPQExpBuffer();
 620         PGresult   *res;
 621         int                     i_rolname,
 622                                 i_rolsuper,
 623                                 i_rolinherit,
 624                                 i_rolcreaterole,
 625                                 i_rolcreatedb,
 626                                 i_rolcatupdate,
 627                                 i_rolcanlogin,
 628                                 i_rolconnlimit,
 629                                 i_rolpassword,
 630                                 i_rolvaliduntil,
 631                                 i_rolcomment;
 632         int                     i;
 633
 634         /* note: rolconfig is dumped later */
 635         if (server_version >= 80200)
 636                 printfPQExpBuffer(buf,
 637                                                   "SELECT rolname, rolsuper, rolinherit, "
 638                                                   "rolcreaterole, rolcreatedb, rolcatupdate, "
 639                                                   "rolcanlogin, rolconnlimit, rolpassword, "
 640                                                   "rolvaliduntil, "
 641                           "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
 642                                                   "FROM pg_authid "
 643                                                   "ORDER BY 1");
 644         else if (server_version >= 80100)
 645                 printfPQExpBuffer(buf,
 646                                                   "SELECT rolname, rolsuper, rolinherit, "
 647                                                   "rolcreaterole, rolcreatedb, rolcatupdate, "
 648                                                   "rolcanlogin, rolconnlimit, rolpassword, "
 649                                                   "rolvaliduntil, null as rolcomment "
 650                                                   "FROM pg_authid "
 651                                                   "ORDER BY 1");
 652         else
 653                 printfPQExpBuffer(buf,
 654                                                   "SELECT usename as rolname, "
 655                                                   "usesuper as rolsuper, "
 656                                                   "true as rolinherit, "
 657                                                   "usesuper as rolcreaterole, "
 658                                                   "usecreatedb as rolcreatedb, "
 659                                                   "usecatupd as rolcatupdate, "
 660                                                   "true as rolcanlogin, "
 661                                                   "-1 as rolconnlimit, "
 662                                                   "passwd as rolpassword, "
 663                                                   "valuntil as rolvaliduntil, "
 664                                                   "null as rolcomment "
 665                                                   "FROM pg_shadow "
 666                                                   "UNION ALL "
 667                                                   "SELECT groname as rolname, "
 668                                                   "false as rolsuper, "
 669                                                   "true as rolinherit, "
 670                                                   "false as rolcreaterole, "
 671                                                   "false as rolcreatedb, "
 672                                                   "false as rolcatupdate, "
 673                                                   "false as rolcanlogin, "
 674                                                   "-1 as rolconnlimit, "
 675                                                   "null::text as rolpassword, "
 676                                                   "null::abstime as rolvaliduntil, "
 677                                                   "null as rolcomment "
 678                                                   "FROM pg_group "
 679                                                   "WHERE NOT EXISTS (SELECT 1 FROM pg_shadow "
 680                                                   " WHERE usename = groname) "
 681                                                   "ORDER BY 1");
 682
 683         res = executeQuery(conn, buf->data);
 684
 685         i_rolname = PQfnumber(res, "rolname");
 686         i_rolsuper = PQfnumber(res, "rolsuper");
 687         i_rolinherit = PQfnumber(res, "rolinherit");
 688         i_rolcreaterole = PQfnumber(res, "rolcreaterole");
 689         i_rolcreatedb = PQfnumber(res, "rolcreatedb");
 690         i_rolcatupdate = PQfnumber(res, "rolcatupdate");
 691         i_rolcanlogin = PQfnumber(res, "rolcanlogin");
 692         i_rolconnlimit = PQfnumber(res, "rolconnlimit");
 693         i_rolpassword = PQfnumber(res, "rolpassword");
 694         i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
 695         i_rolcomment = PQfnumber(res, "rolcomment");
 696
 697         if (PQntuples(res) > 0)
 698                 fprintf(OPF, "--\n-- Roles\n--\n\n");
 699
 700         for (i = 0; i < PQntuples(res); i++)
 701         {
 702                 const char *rolename;
 703
 704                 rolename = PQgetvalue(res, i, i_rolname);
 705
 706                 resetPQExpBuffer(buf);
 707
 708                 /*
 709                  * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role
 710                  * will acquire the right properties even if it already exists (ie, it
 711                  * won't hurt for the CREATE to fail).  This is particularly important
 712                  * for the role we are connected as, since even with --clean we will
 713                  * have failed to drop it.
 714                  */
 715                 appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename));
 716                 appendPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename));
 717
 718                 if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0)
 719                         appendPQExpBuffer(buf, " SUPERUSER");
 720                 else
 721                         appendPQExpBuffer(buf, " NOSUPERUSER");
 722
 723                 if (strcmp(PQgetvalue(res, i, i_rolinherit), "t") == 0)
 724                         appendPQExpBuffer(buf, " INHERIT");
 725                 else
 726                         appendPQExpBuffer(buf, " NOINHERIT");
 727
 728                 if (strcmp(PQgetvalue(res, i, i_rolcreaterole), "t") == 0)
 729                         appendPQExpBuffer(buf, " CREATEROLE");
 730                 else
 731                         appendPQExpBuffer(buf, " NOCREATEROLE");
 732
 733                 if (strcmp(PQgetvalue(res, i, i_rolcreatedb), "t") == 0)
 734                         appendPQExpBuffer(buf, " CREATEDB");
 735                 else
 736                         appendPQExpBuffer(buf, " NOCREATEDB");
 737
 738                 if (strcmp(PQgetvalue(res, i, i_rolcanlogin), "t") == 0)
 739                         appendPQExpBuffer(buf, " LOGIN");
 740                 else
 741                         appendPQExpBuffer(buf, " NOLOGIN");
 742
 743                 if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0)
 744                         appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
 745                                                           PQgetvalue(res, i, i_rolconnlimit));
 746
 747                 if (!PQgetisnull(res, i, i_rolpassword))
 748                 {
 749                         appendPQExpBuffer(buf, " PASSWORD ");
 750                         appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn);
 751                 }
 752
 753                 if (!PQgetisnull(res, i, i_rolvaliduntil))
 754                         appendPQExpBuffer(buf, " VALID UNTIL '%s'",
 755                                                           PQgetvalue(res, i, i_rolvaliduntil));
 756
 757                 appendPQExpBuffer(buf, ";\n");
 758
 759                 if (!PQgetisnull(res, i, i_rolcomment))
 760                 {
 761                         appendPQExpBuffer(buf, "COMMENT ON ROLE %s IS ", fmtId(rolename));
 762                         appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolcomment), conn);
 763                         appendPQExpBuffer(buf, ";\n");
 764                 }
 765
 766                 fprintf(OPF, "%s", buf->data);
 767
 768                 if (server_version >= 70300)
 769                         dumpUserConfig(conn, rolename);
 770         }
 771
 772         PQclear(res);
 773
 774         fprintf(OPF, "\n\n");
 775
 776         destroyPQExpBuffer(buf);
 777 }
 778
 779
 780 /*
 781  * Dump role memberships.  This code is used for 8.1 and later servers.
 782  *
 783  * Note: we expect dumpRoles already created all the roles, but there is
 784  * no membership yet.
 785  */
 786 static void
 787 dumpRoleMembership(PGconn *conn)
 788 {
 789         PGresult   *res;
 790         int                     i;
 791
 792         res = executeQuery(conn, "SELECT ur.rolname AS roleid, "
 793                                            "um.rolname AS member, "
 794                                            "a.admin_option, "
 795                                            "ug.rolname AS grantor "
 796                                            "FROM pg_auth_members a "
 797                                            "LEFT JOIN pg_authid ur on ur.oid = a.roleid "
 798                                            "LEFT JOIN pg_authid um on um.oid = a.member "
 799                                            "LEFT JOIN pg_authid ug on ug.oid = a.grantor "
 800                                            "ORDER BY 1,2,3");
 801
 802         if (PQntuples(res) > 0)
 803                 fprintf(OPF, "--\n-- Role memberships\n--\n\n");
 804
 805         for (i = 0; i < PQntuples(res); i++)
 806         {
 807                 char       *roleid = PQgetvalue(res, i, 0);
 808                 char       *member = PQgetvalue(res, i, 1);
 809                 char       *option = PQgetvalue(res, i, 2);
 810
 811                 fprintf(OPF, "GRANT %s", fmtId(roleid));
 812                 fprintf(OPF, " TO %s", fmtId(member));
 813                 if (*option == 't')
 814                         fprintf(OPF, " WITH ADMIN OPTION");
 815
 816                 /*
 817                  * We don't track the grantor very carefully in the backend, so cope
 818                  * with the possibility that it has been dropped.
 819                  */
 820                 if (!PQgetisnull(res, i, 3))
 821                 {
 822                         char       *grantor = PQgetvalue(res, i, 3);
 823
 824                         fprintf(OPF, " GRANTED BY %s", fmtId(grantor));
 825                 }
 826                 fprintf(OPF, ";\n");
 827         }
 828
 829         PQclear(res);
 830
 831         fprintf(OPF, "\n\n");
 832 }
 833
 834 /*
 835  * Dump group memberships from a pre-8.1 server.  It's annoying that we
 836  * can't share any useful amount of code with the post-8.1 case, but
 837  * the catalog representations are too different.
 838  *
 839  * Note: we expect dumpRoles already created all the roles, but there is
 840  * no membership yet.
 841  */
 842 static void
 843 dumpGroups(PGconn *conn)
 844 {
 845         PQExpBuffer buf = createPQExpBuffer();
 846         PGresult   *res;
 847         int                     i;
 848
 849         res = executeQuery(conn,
 850                                            "SELECT groname, grolist FROM pg_group ORDER BY 1");
 851
 852         if (PQntuples(res) > 0)
 853                 fprintf(OPF, "--\n-- Role memberships\n--\n\n");
 854
 855         for (i = 0; i < PQntuples(res); i++)
 856         {
 857                 char       *groname = PQgetvalue(res, i, 0);
 858                 char       *grolist = PQgetvalue(res, i, 1);
 859                 PGresult   *res2;
 860                 int                     j;
 861
 862                 /*
 863                  * Array representation is {1,2,3} ... convert to (1,2,3)
 864                  */
 865                 if (strlen(grolist) < 3)
 866                         continue;
 867
 868                 grolist = strdup(grolist);
 869                 grolist[0] = '(';
 870                 grolist[strlen(grolist) - 1] = ')';
 871                 printfPQExpBuffer(buf,
 872                                                   "SELECT usename FROM pg_shadow "
 873                                                   "WHERE usesysid IN %s ORDER BY 1",
 874                                                   grolist);
 875                 free(grolist);
 876
 877                 res2 = executeQuery(conn, buf->data);
 878
 879                 for (j = 0; j < PQntuples(res2); j++)
 880                 {
 881                         char       *usename = PQgetvalue(res2, j, 0);
 882
 883                         /*
 884                          * Don't try to grant a role to itself; can happen if old
 885                          * installation has identically named user and group.
 886                          */
 887                         if (strcmp(groname, usename) == 0)
 888                                 continue;
 889
 890                         fprintf(OPF, "GRANT %s", fmtId(groname));
 891                         fprintf(OPF, " TO %s;\n", fmtId(usename));
 892                 }
 893
 894                 PQclear(res2);
 895         }
 896
 897         PQclear(res);
 898         destroyPQExpBuffer(buf);
 899
 900         fprintf(OPF, "\n\n");
 901 }
 902
 903
 904 /*
 905  * Drop tablespaces.
 906  */
 907 static void
 908 dropTablespaces(PGconn *conn)
 909 {
 910         PGresult   *res;
 911         int                     i;
 912
 913         /*
 914          * Get all tablespaces except built-in ones (which we assume are named
 915          * pg_xxx)
 916          */
 917         res = executeQuery(conn, "SELECT spcname "
 918                                            "FROM pg_catalog.pg_tablespace "
 919                                            "WHERE spcname !~ '^pg_' "
 920                                            "ORDER BY 1");
 921
 922         if (PQntuples(res) > 0)
 923                 fprintf(OPF, "--\n-- Drop tablespaces\n--\n\n");
 924
 925         for (i = 0; i < PQntuples(res); i++)
 926         {
 927                 char       *spcname = PQgetvalue(res, i, 0);
 928
 929                 fprintf(OPF, "DROP TABLESPACE %s;\n", fmtId(spcname));
 930         }
 931
 932         PQclear(res);
 933
 934         fprintf(OPF, "\n\n");
 935 }
 936
 937 /*
 938  * Dump tablespaces.
 939  */
 940 static void
 941 dumpTablespaces(PGconn *conn)
 942 {
 943         PGresult   *res;
 944         int                     i;
 945
 946         /*
 947          * Get all tablespaces except built-in ones (which we assume are named
 948          * pg_xxx)
 949          */
 950         if (server_version >= 80200)
 951                 res = executeQuery(conn, "SELECT spcname, "
 952                                                  "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
 953                                                    "spclocation, spcacl, "
 954                                                 "pg_catalog.shobj_description(oid, 'pg_tablespace') "
 955                                                    "FROM pg_catalog.pg_tablespace "
 956                                                    "WHERE spcname !~ '^pg_' "
 957                                                    "ORDER BY 1");
 958         else
 959                 res = executeQuery(conn, "SELECT spcname, "
 960                                                  "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
 961                                                    "spclocation, spcacl, "
 962                                                    "null "
 963                                                    "FROM pg_catalog.pg_tablespace "
 964                                                    "WHERE spcname !~ '^pg_' "
 965                                                    "ORDER BY 1");
 966
 967         if (PQntuples(res) > 0)
 968                 fprintf(OPF, "--\n-- Tablespaces\n--\n\n");
 969
 970         for (i = 0; i < PQntuples(res); i++)
 971         {
 972                 PQExpBuffer buf = createPQExpBuffer();
 973                 char       *spcname = PQgetvalue(res, i, 0);
 974                 char       *spcowner = PQgetvalue(res, i, 1);
 975                 char       *spclocation = PQgetvalue(res, i, 2);
 976                 char       *spcacl = PQgetvalue(res, i, 3);
 977                 char       *spccomment = PQgetvalue(res, i, 4);
 978                 char       *fspcname;
 979
 980                 /* needed for buildACLCommands() */
 981                 fspcname = strdup(fmtId(spcname));
 982
 983                 appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
 984                 appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
 985
 986                 appendPQExpBuffer(buf, " LOCATION ");
 987                 appendStringLiteralConn(buf, spclocation, conn);
 988                 appendPQExpBuffer(buf, ";\n");
 989
 990                 if (!skip_acls &&
 991                         !buildACLCommands(fspcname, NULL, "TABLESPACE", spcacl, spcowner,
 992                                                           server_version, buf))
 993                 {
 994                         fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
 995                                         progname, spcacl, fspcname);
 996                         PQfinish(conn);
 997                         exit(1);
 998                 }
 999
1000                 if (spccomment && strlen(spccomment))
1001                 {
1002                         appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", fspcname);
1003                         appendStringLiteralConn(buf, spccomment, conn);
1004                         appendPQExpBuffer(buf, ";\n");
1005                 }
1006
1007                 fprintf(OPF, "%s", buf->data);
1008
1009                 free(fspcname);
1010                 destroyPQExpBuffer(buf);
1011         }
1012
1013         PQclear(res);
1014         fprintf(OPF, "\n\n");
1015 }
1016
1017
1018 /*
1019  * Dump commands to drop each database.
1020  *
1021  * This should match the set of databases targeted by dumpCreateDB().
1022  */
1023 static void
1024 dropDBs(PGconn *conn)
1025 {
1026         PGresult   *res;
1027         int                     i;
1028
1029         if (server_version >= 70100)
1030                 res = executeQuery(conn,
1031                                                    "SELECT datname "
1032                                                    "FROM pg_database d "
1033                                                    "WHERE datallowconn ORDER BY 1");
1034         else
1035                 res = executeQuery(conn,
1036                                                    "SELECT datname "
1037                                                    "FROM pg_database d "
1038                                                    "ORDER BY 1");
1039
1040         if (PQntuples(res) > 0)
1041                 fprintf(OPF, "--\n-- Drop databases\n--\n\n");
1042
1043         for (i = 0; i < PQntuples(res); i++)
1044         {
1045                 char       *dbname = PQgetvalue(res, i, 0);
1046
1047                 /*
1048                  * Skip "template1" and "postgres"; the restore script is almost
1049                  * certainly going to be run in one or the other, and we don't know
1050                  * which.  This must agree with dumpCreateDB's choices!
1051                  */
1052                 if (strcmp(dbname, "template1") != 0 &&
1053                         strcmp(dbname, "postgres") != 0)
1054                 {
1055                         fprintf(OPF, "DROP DATABASE %s;\n", fmtId(dbname));
1056                 }
1057         }
1058
1059         PQclear(res);
1060
1061         fprintf(OPF, "\n\n");
1062 }
1063
1064 /*
1065  * Dump commands to create each database.
1066  *
1067  * To minimize the number of reconnections (and possibly ensuing
1068  * password prompts) required by the output script, we emit all CREATE
1069  * DATABASE commands during the initial phase of the script, and then
1070  * run pg_dump for each database to dump the contents of that
1071  * database.  We skip databases marked not datallowconn, since we'd be
1072  * unable to connect to them anyway (and besides, we don't want to
1073  * dump template0).
1074  */
1075 static void
1076 dumpCreateDB(PGconn *conn)
1077 {
1078         PQExpBuffer buf = createPQExpBuffer();
1079         char       *default_encoding = NULL;
1080         char       *default_collate = NULL;
1081         char       *default_ctype = NULL;
1082         PGresult   *res;
1083         int                     i;
1084
1085         fprintf(OPF, "--\n-- Database creation\n--\n\n");
1086
1087         /*
1088          * First, get the installation's default encoding and locale information.
1089          * We will dump encoding and locale specifications in the CREATE DATABASE
1090          * commands for just those databases with values different from defaults.
1091          *
1092          * We consider template0's encoding and locale (or, pre-7.1, template1's)
1093          * to define the installation default.  Pre-8.4 installations do not have
1094          * per-database locale settings; for them, every database must necessarily
1095          * be using the installation default, so there's no need to do anything
1096          * (which is good, since in very old versions there is no good way to find
1097          * out what the installation locale is anyway...)
1098          */
1099         if (server_version >= 80400)
1100                 res = executeQuery(conn,
1101                                                    "SELECT pg_encoding_to_char(encoding), "
1102                                                    "datcollate, datctype "
1103                                                    "FROM pg_database "
1104                                                    "WHERE datname = 'template0'");
1105         else if (server_version >= 70100)
1106                 res = executeQuery(conn,
1107                                                    "SELECT pg_encoding_to_char(encoding), "
1108                                                    "null::text AS datcollate, null::text AS datctype "
1109                                                    "FROM pg_database "
1110                                                    "WHERE datname = 'template0'");
1111         else
1112                 res = executeQuery(conn,
1113                                                    "SELECT pg_encoding_to_char(encoding), "
1114                                                    "null::text AS datcollate, null::text AS datctype "
1115                                                    "FROM pg_database "
1116                                                    "WHERE datname = 'template1'");
1117
1118         /* If for some reason the template DB isn't there, treat as unknown */
1119         if (PQntuples(res) > 0)
1120         {
1121                 if (!PQgetisnull(res, 0, 0))
1122                         default_encoding = strdup(PQgetvalue(res, 0, 0));
1123                 if (!PQgetisnull(res, 0, 1))
1124                         default_collate = strdup(PQgetvalue(res, 0, 1));
1125                 if (!PQgetisnull(res, 0, 2))
1126                         default_ctype = strdup(PQgetvalue(res, 0, 2));
1127         }
1128
1129         PQclear(res);
1130
1131         /* Now collect all the information about databases to dump */
1132         if (server_version >= 80400)
1133                 res = executeQuery(conn,
1134                                                    "SELECT datname, "
1135                                                    "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1136                                                    "pg_encoding_to_char(d.encoding), "
1137                                                    "datcollate, datctype, datfrozenxid, "
1138                                                    "datistemplate, datacl, datconnlimit, "
1139                                                    "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1140                           "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1141                                                    "WHERE datallowconn ORDER BY 1");
1142         else if (server_version >= 80100)
1143                 res = executeQuery(conn,
1144                                                    "SELECT datname, "
1145                                                    "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1146                                                    "pg_encoding_to_char(d.encoding), "
1147                    "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
1148                                                    "datistemplate, datacl, datconnlimit, "
1149                                                    "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1150                           "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1151                                                    "WHERE datallowconn ORDER BY 1");
1152         else if (server_version >= 80000)
1153                 res = executeQuery(conn,
1154                                                    "SELECT datname, "
1155                                                    "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
1156                                                    "pg_encoding_to_char(d.encoding), "
1157                    "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
1158                                                    "datistemplate, datacl, -1 as datconnlimit, "
1159                                                    "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1160                    "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
1161                                                    "WHERE datallowconn ORDER BY 1");
1162         else if (server_version >= 70300)
1163                 res = executeQuery(conn,
1164                                                    "SELECT datname, "
1165                                                    "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
1166                                                    "pg_encoding_to_char(d.encoding), "
1167                    "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
1168                                                    "datistemplate, datacl, -1 as datconnlimit, "
1169                                                    "'pg_default' AS dattablespace "
1170                    "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
1171                                                    "WHERE datallowconn ORDER BY 1");
1172         else if (server_version >= 70100)
1173                 res = executeQuery(conn,
1174                                                    "SELECT datname, "
1175                                                    "coalesce("
1176                                         "(select usename from pg_shadow where usesysid=datdba), "
1177                                                    "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
1178                                                    "pg_encoding_to_char(d.encoding), "
1179                                                    "null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid, "
1180                                                    "datistemplate, '' as datacl, -1 as datconnlimit, "
1181                                                    "'pg_default' AS dattablespace "
1182                                                    "FROM pg_database d "
1183                                                    "WHERE datallowconn ORDER BY 1");
1184         else
1185         {
1186                 /*
1187                  * Note: 7.0 fails to cope with sub-select in COALESCE, so just deal
1188                  * with getting a NULL by not printing any OWNER clause.
1189                  */
1190                 res = executeQuery(conn,
1191                                                    "SELECT datname, "
1192                                         "(select usename from pg_shadow where usesysid=datdba), "
1193                                                    "pg_encoding_to_char(d.encoding), "
1194                                                    "null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid, "
1195                                                    "'f' as datistemplate, "
1196                                                    "'' as datacl, -1 as datconnlimit, "
1197                                                    "'pg_default' AS dattablespace "
1198                                                    "FROM pg_database d "
1199                                                    "ORDER BY 1");
1200         }
1201
1202         for (i = 0; i < PQntuples(res); i++)
1203         {
1204                 char       *dbname = PQgetvalue(res, i, 0);
1205                 char       *dbowner = PQgetvalue(res, i, 1);
1206                 char       *dbencoding = PQgetvalue(res, i, 2);
1207                 char       *dbcollate = PQgetvalue(res, i, 3);
1208                 char       *dbctype = PQgetvalue(res, i, 4);
1209                 uint32          dbfrozenxid = atooid(PQgetvalue(res, i, 5));
1210                 char       *dbistemplate = PQgetvalue(res, i, 6);
1211                 char       *dbacl = PQgetvalue(res, i, 7);
1212                 char       *dbconnlimit = PQgetvalue(res, i, 8);
1213                 char       *dbtablespace = PQgetvalue(res, i, 9);
1214                 char       *fdbname;
1215
1216                 fdbname = strdup(fmtId(dbname));
1217
1218                 resetPQExpBuffer(buf);
1219
1220                 /*
1221                  * Skip the CREATE DATABASE commands for "template1" and "postgres",
1222                  * since they are presumably already there in the destination cluster.
1223                  * We do want to emit their ACLs and config options if any, however.
1224                  */
1225                 if (strcmp(dbname, "template1") != 0 &&
1226                         strcmp(dbname, "postgres") != 0)
1227                 {
1228                         appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
1229
1230                         appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
1231
1232                         if (strlen(dbowner) != 0)
1233                                 appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner));
1234
1235                         if (default_encoding && strcmp(dbencoding, default_encoding) != 0)
1236                         {
1237                                 appendPQExpBuffer(buf, " ENCODING = ");
1238                                 appendStringLiteralConn(buf, dbencoding, conn);
1239                         }
1240
1241                         if (default_collate && strcmp(dbcollate, default_collate) != 0)
1242                         {
1243                                 appendPQExpBuffer(buf, " LC_COLLATE = ");
1244                                 appendStringLiteralConn(buf, dbcollate, conn);
1245                         }
1246
1247                         if (default_ctype && strcmp(dbctype, default_ctype) != 0)
1248                         {
1249                                 appendPQExpBuffer(buf, " LC_CTYPE = ");
1250                                 appendStringLiteralConn(buf, dbctype, conn);
1251                         }
1252
1253                         /*
1254                          * Output tablespace if it isn't the default.  For default, it
1255                          * uses the default from the template database.  If tablespace is
1256                          * specified and tablespace creation failed earlier, (e.g. no such
1257                          * directory), the database creation will fail too.  One solution
1258                          * would be to use 'SET default_tablespace' like we do in pg_dump
1259                          * for setting non-default database locations.
1260                          */
1261                         if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces)
1262                                 appendPQExpBuffer(buf, " TABLESPACE = %s",
1263                                                                   fmtId(dbtablespace));
1264
1265                         if (strcmp(dbconnlimit, "-1") != 0)
1266                                 appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
1267                                                                   dbconnlimit);
1268
1269                         appendPQExpBuffer(buf, ";\n");
1270
1271                         if (strcmp(dbistemplate, "t") == 0)
1272                         {
1273                                 appendPQExpBuffer(buf, "UPDATE pg_catalog.pg_database SET datistemplate = 't' WHERE datname = ");
1274                                 appendStringLiteralConn(buf, dbname, conn);
1275                                 appendPQExpBuffer(buf, ";\n");
1276                         }
1277
1278                         if (binary_upgrade)
1279                         {
1280                                 appendPQExpBuffer(buf, "-- For binary upgrade, set datfrozenxid.\n");
1281                                 appendPQExpBuffer(buf, "UPDATE pg_catalog.pg_database "
1282                                                                   "SET datfrozenxid = '%u' "
1283                                                                   "WHERE datname = ",
1284                                                                   dbfrozenxid);
1285                                 appendStringLiteralConn(buf, dbname, conn);
1286                                 appendPQExpBuffer(buf, ";\n");
1287                         }
1288                 }
1289
1290                 if (!skip_acls &&
1291                         !buildACLCommands(fdbname, NULL, "DATABASE", dbacl, dbowner,
1292                                                           server_version, buf))
1293                 {
1294                         fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
1295                                         progname, dbacl, fdbname);
1296                         PQfinish(conn);
1297                         exit(1);
1298                 }
1299
1300                 fprintf(OPF, "%s", buf->data);
1301
1302                 if (server_version >= 70300)
1303                         dumpDatabaseConfig(conn, dbname);
1304
1305                 free(fdbname);
1306         }
1307
1308         PQclear(res);
1309         destroyPQExpBuffer(buf);
1310
1311         fprintf(OPF, "\n\n");
1312 }
1313
1314
1315 /*
1316  * Dump database-specific configuration
1317  */
1318 static void
1319 dumpDatabaseConfig(PGconn *conn, const char *dbname)
1320 {
1321         PQExpBuffer buf = createPQExpBuffer();
1322         int                     count = 1;
1323
1324         for (;;)
1325         {
1326                 PGresult   *res;
1327
1328                 printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
1329                 appendStringLiteralConn(buf, dbname, conn);
1330                 appendPQExpBuffer(buf, ";");
1331
1332                 res = executeQuery(conn, buf->data);
1333                 if (!PQgetisnull(res, 0, 0))
1334                 {
1335                         makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
1336                                                                    "DATABASE", dbname);
1337                         PQclear(res);
1338                         count++;
1339                 }
1340                 else
1341                 {
1342                         PQclear(res);
1343                         break;
1344                 }
1345         }
1346
1347         destroyPQExpBuffer(buf);
1348 }
1349
1350
1351
1352 /*
1353  * Dump user-specific configuration
1354  */
1355 static void
1356 dumpUserConfig(PGconn *conn, const char *username)
1357 {
1358         PQExpBuffer buf = createPQExpBuffer();
1359         int                     count = 1;
1360
1361         for (;;)
1362         {
1363                 PGresult   *res;
1364
1365                 if (server_version >= 80100)
1366                         printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count);
1367                 else
1368                         printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count);
1369                 appendStringLiteralConn(buf, username, conn);
1370
1371                 res = executeQuery(conn, buf->data);
1372                 if (PQntuples(res) == 1 &&
1373                         !PQgetisnull(res, 0, 0))
1374                 {
1375                         makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
1376                                                                    "ROLE", username);
1377                         PQclear(res);
1378                         count++;
1379                 }
1380                 else
1381                 {
1382                         PQclear(res);
1383                         break;
1384                 }
1385         }
1386
1387         destroyPQExpBuffer(buf);
1388 }
1389
1390
1391
1392 /*
1393  * Helper function for dumpXXXConfig().
1394  */
1395 static void
1396 makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
1397                                            const char *type, const char *name)
1398 {
1399         char       *pos;
1400         char       *mine;
1401         PQExpBuffer buf = createPQExpBuffer();
1402
1403         mine = strdup(arrayitem);
1404         pos = strchr(mine, '=');
1405         if (pos == NULL)
1406                 return;
1407
1408         *pos = 0;
1409         appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name));
1410         appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine));
1411
1412         /*
1413          * Some GUC variable names are 'LIST' type and hence must not be quoted.
1414          */
1415         if (pg_strcasecmp(mine, "DateStyle") == 0
1416                 || pg_strcasecmp(mine, "search_path") == 0)
1417                 appendPQExpBuffer(buf, "%s", pos + 1);
1418         else
1419                 appendStringLiteralConn(buf, pos + 1, conn);
1420         appendPQExpBuffer(buf, ";\n");
1421
1422         fprintf(OPF, "%s", buf->data);
1423         destroyPQExpBuffer(buf);
1424         free(mine);
1425 }
1426
1427
1428
1429 /*
1430  * Dump contents of databases.
1431  */
1432 static void
1433 dumpDatabases(PGconn *conn)
1434 {
1435         PGresult   *res;
1436         int                     i;
1437
1438         if (server_version >= 70100)
1439                 res = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1");
1440         else
1441                 res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1");
1442
1443         for (i = 0; i < PQntuples(res); i++)
1444         {
1445                 int                     ret;
1446
1447                 char       *dbname = PQgetvalue(res, i, 0);
1448
1449                 if (verbose)
1450                         fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname);
1451
1452                 fprintf(OPF, "\\connect %s\n\n", fmtId(dbname));
1453
1454                 if (filename)
1455                         fclose(OPF);
1456
1457                 ret = runPgDump(dbname);
1458                 if (ret != 0)
1459                 {
1460                         fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname);
1461                         exit(1);
1462                 }
1463
1464                 if (filename)
1465                 {
1466                         OPF = fopen(filename, PG_BINARY_A);
1467                         if (!OPF)
1468                         {
1469                                 fprintf(stderr, _("%s: could not re-open the output file \"%s\": %s\n"),
1470                                                 progname, filename, strerror(errno));
1471                                 exit(1);
1472                         }
1473                 }
1474
1475         }
1476
1477         PQclear(res);
1478 }
1479
1480
1481
1482 /*
1483  * Run pg_dump on dbname.
1484  */
1485 static int
1486 runPgDump(const char *dbname)
1487 {
1488         PQExpBuffer cmd = createPQExpBuffer();
1489         int                     ret;
1490
1491         appendPQExpBuffer(cmd, SYSTEMQUOTE "\"%s\" %s", pg_dump_bin,
1492                                           pgdumpopts->data);
1493
1494         /*
1495          * If we have a filename, use the undocumented plain-append pg_dump
1496          * format.
1497          */
1498         if (filename)
1499                 appendPQExpBuffer(cmd, " -Fa ");
1500         else
1501                 appendPQExpBuffer(cmd, " -Fp ");
1502
1503         doShellQuoting(cmd, dbname);
1504
1505         appendPQExpBuffer(cmd, "%s", SYSTEMQUOTE);
1506
1507         if (verbose)
1508                 fprintf(stderr, _("%s: running \"%s\"\n"), progname, cmd->data);
1509
1510         fflush(stdout);
1511         fflush(stderr);
1512
1513         ret = system(cmd->data);
1514
1515         destroyPQExpBuffer(cmd);
1516
1517         return ret;
1518 }
1519
1520
1521 /*
1522  * Make a database connection with the given parameters.  An
1523  * interactive password prompt is automatically issued if required.
1524  *
1525  * If fail_on_error is false, we return NULL without printing any message
1526  * on failure, but preserve any prompted password for the next try.
1527  */
1528 static PGconn *
1529 connectDatabase(const char *dbname, const char *pghost, const char *pgport,
1530            const char *pguser, enum trivalue prompt_password, bool fail_on_error)
1531 {
1532         PGconn     *conn;
1533         bool            new_pass;
1534         const char *remoteversion_str;
1535         int                     my_version;
1536         static char *password = NULL;
1537
1538         if (prompt_password == TRI_YES && !password)
1539                 password = simple_prompt("Password: ", 100, false);
1540
1541         /*
1542          * Start the connection.  Loop until we have a password if requested by
1543          * backend.
1544          */
1545         do
1546         {
1547                 new_pass = false;
1548                 conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, pguser, password);
1549
1550                 if (!conn)
1551                 {
1552                         fprintf(stderr, _("%s: could not connect to database \"%s\"\n"),
1553                                         progname, dbname);
1554                         exit(1);
1555                 }
1556
1557                 if (PQstatus(conn) == CONNECTION_BAD &&
1558                         PQconnectionNeedsPassword(conn) &&
1559                         password == NULL &&
1560                         prompt_password != TRI_NO)
1561                 {
1562                         PQfinish(conn);
1563                         password = simple_prompt("Password: ", 100, false);
1564                         new_pass = true;
1565                 }
1566         } while (new_pass);
1567
1568         /* check to see that the backend connection was successfully made */
1569         if (PQstatus(conn) == CONNECTION_BAD)
1570         {
1571                 if (fail_on_error)
1572                 {
1573                         fprintf(stderr,
1574                                         _("%s: could not connect to database \"%s\": %s\n"),
1575                                         progname, dbname, PQerrorMessage(conn));
1576                         exit(1);
1577                 }
1578                 else
1579                 {
1580                         PQfinish(conn);
1581                         return NULL;
1582                 }
1583         }
1584
1585         remoteversion_str = PQparameterStatus(conn, "server_version");
1586         if (!remoteversion_str)
1587         {
1588                 fprintf(stderr, _("%s: could not get server version\n"), progname);
1589                 exit(1);
1590         }
1591         server_version = parse_version(remoteversion_str);
1592         if (server_version < 0)
1593         {
1594                 fprintf(stderr, _("%s: could not parse server version \"%s\"\n"),
1595                                 progname, remoteversion_str);
1596                 exit(1);
1597         }
1598
1599         my_version = parse_version(PG_VERSION);
1600         if (my_version < 0)
1601         {
1602                 fprintf(stderr, _("%s: could not parse version \"%s\"\n"),
1603                                 progname, PG_VERSION);
1604                 exit(1);
1605         }
1606
1607         /*
1608          * We allow the server to be back to 7.0, and up to any minor release of
1609          * our own major version.  (See also version check in pg_dump.c.)
1610          */
1611         if (my_version != server_version
1612                 && (server_version < 70000 ||
1613                         (server_version / 100) > (my_version / 100)))
1614         {
1615                 fprintf(stderr, _("server version: %s; %s version: %s\n"),
1616                                 remoteversion_str, progname, PG_VERSION);
1617                 fprintf(stderr, _("aborting because of server version mismatch\n"));
1618                 exit(1);
1619         }
1620
1621         /*
1622          * On 7.3 and later, make sure we are not fooled by non-system schemas in
1623          * the search path.
1624          */
1625         if (server_version >= 70300)
1626                 executeCommand(conn, "SET search_path = pg_catalog");
1627
1628         return conn;
1629 }
1630
1631
1632 /*
1633  * Run a query, return the results, exit program on failure.
1634  */
1635 static PGresult *
1636 executeQuery(PGconn *conn, const char *query)
1637 {
1638         PGresult   *res;
1639
1640         if (verbose)
1641                 fprintf(stderr, _("%s: executing %s\n"), progname, query);
1642
1643         res = PQexec(conn, query);
1644         if (!res ||
1645                 PQresultStatus(res) != PGRES_TUPLES_OK)
1646         {
1647                 fprintf(stderr, _("%s: query failed: %s"),
1648                                 progname, PQerrorMessage(conn));
1649                 fprintf(stderr, _("%s: query was: %s\n"),
1650                                 progname, query);
1651                 PQfinish(conn);
1652                 exit(1);
1653         }
1654
1655         return res;
1656 }
1657
1658 /*
1659  * As above for a SQL command (which returns nothing).
1660  */
1661 static void
1662 executeCommand(PGconn *conn, const char *query)
1663 {
1664         PGresult   *res;
1665
1666         if (verbose)
1667                 fprintf(stderr, _("%s: executing %s\n"), progname, query);
1668
1669         res = PQexec(conn, query);
1670         if (!res ||
1671                 PQresultStatus(res) != PGRES_COMMAND_OK)
1672         {
1673                 fprintf(stderr, _("%s: query failed: %s"),
1674                                 progname, PQerrorMessage(conn));
1675                 fprintf(stderr, _("%s: query was: %s\n"),
1676                                 progname, query);
1677                 PQfinish(conn);
1678                 exit(1);
1679         }
1680
1681         PQclear(res);
1682 }
1683
1684
1685 /*
1686  * dumpTimestamp
1687  */
1688 static void
1689 dumpTimestamp(char *msg)
1690 {
1691         char            buf[256];
1692         time_t          now = time(NULL);
1693
1694         /*
1695          * We don't print the timezone on Win32, because the names are long and
1696          * localized, which means they may contain characters in various random
1697          * encodings; this has been seen to cause encoding errors when reading the
1698          * dump script.
1699          */
1700         if (strftime(buf, sizeof(buf),
1701 #ifndef WIN32
1702                                  "%Y-%m-%d %H:%M:%S %Z",
1703 #else
1704                                  "%Y-%m-%d %H:%M:%S",
1705 #endif
1706                                  localtime(&now)) != 0)
1707                 fprintf(OPF, "-- %s %s\n\n", msg, buf);
1708 }
1709
1710
1711 /*
1712  * Append the given string to the shell command being built in the buffer,
1713  * with suitable shell-style quoting.
1714  */
1715 static void
1716 doShellQuoting(PQExpBuffer buf, const char *str)
1717 {
1718         const char *p;
1719
1720 #ifndef WIN32
1721         appendPQExpBufferChar(buf, '\'');
1722         for (p = str; *p; p++)
1723         {
1724                 if (*p == '\'')
1725                         appendPQExpBuffer(buf, "'\"'\"'");
1726                 else
1727                         appendPQExpBufferChar(buf, *p);
1728         }
1729         appendPQExpBufferChar(buf, '\'');
1730 #else                                                   /* WIN32 */
1731
1732         appendPQExpBufferChar(buf, '"');
1733         for (p = str; *p; p++)
1734         {
1735                 if (*p == '"')
1736                         appendPQExpBuffer(buf, "\\\"");
1737                 else
1738                         appendPQExpBufferChar(buf, *p);
1739         }
1740         appendPQExpBufferChar(buf, '"');
1741 #endif   /* WIN32 */
1742 }