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

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