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