/* * vacuum_one_database * * Process tables in the given database. If the 'tables' list is empty, * process all tables in the database. * * Note that this function is only concerned with running exactly one stage * when in analyze-in-stages mode; caller must iterate on us if necessary. * * If concurrentCons is > 1, multiple connections are used to vacuum tables * in parallel. In this case and if the table list is empty, we first obtain * a list of tables from the database. */ static void vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, const char *host, const char *port, const char *username, enum trivalue prompt_password, int concurrentCons, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; PGconn *conn; SimpleStringListCell *cell; ParallelSlot *slots = NULL; SimpleStringList dbtables = {NULL, NULL}; int i; bool result = 0; bool parallel = concurrentCons > 1; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", "RESET default_statistics_target;" }; const char *stage_messages[] = { gettext_noop("Generating minimal optimizer statistics (1 target)"), gettext_noop("Generating medium optimizer statistics (10 targets)"), gettext_noop("Generating default (full) optimizer statistics") }; Assert(stage == ANALYZE_NO_STAGE || (stage >= 0 && stage < ANALYZE_NUM_STAGES)); if (!quiet) { if (stage != ANALYZE_NO_STAGE) printf(_("%s: processing database \"%s\": %s\n"), progname, dbname, stage_messages[stage]); else printf(_("%s: vacuuming database \"%s\"\n"), progname, dbname); fflush(stdout); } conn = connectDatabase(dbname, host, port, username, prompt_password, progname, false); initPQExpBuffer(&sql); /* * If a table list is not provided and we're using multiple connections, * prepare the list of tables by querying the catalogs. */ if (parallel && (!tables || !tables->head)) { PQExpBufferData buf; PGresult *res; int ntups; int i; initPQExpBuffer(&buf); res = executeQuery(conn, "SELECT c.relname, ns.nspname FROM pg_class c, pg_namespace ns\n" " WHERE relkind IN (\'r\', \'m\') AND c.relnamespace = ns.oid\n" " ORDER BY c.relpages DESC;", progname, echo); ntups = PQntuples(res); for (i = 0; i < ntups; i++) { appendPQExpBuffer(&buf, "%s", fmtQualifiedId(PQserverVersion(conn), PQgetvalue(res, i, 1), PQgetvalue(res, i, 0))); simple_string_list_append(&dbtables, buf.data); resetPQExpBuffer(&buf); } termPQExpBuffer(&buf); tables = &dbtables; /* * If there are more connections than vacuumable relations, we don't * need to use them all. */ if (concurrentCons > ntups) concurrentCons = ntups; if (concurrentCons <= 1) parallel = false; } /* * Setup the database connections. We reuse the connection we already have * for the first slot. If not in parallel mode, the first slot in the * array contains the connection. */ slots = (ParallelSlot *) pg_malloc(sizeof(ParallelSlot) * concurrentCons); init_slot(slots, conn); if (parallel) { for (i = 1; i < concurrentCons; i++) { conn = connectDatabase(dbname, host, port, username, prompt_password, progname, false); init_slot(slots + i, conn); } } /* * Prepare all the connections to run the appropriate analyze stage, if * caller requested that mode. */ if (stage != ANALYZE_NO_STAGE) { int j; /* We already emitted the message above */ for (j = 0; j < concurrentCons; j++) executeCommand((slots + j)->connection, stage_commands[stage], progname, echo); } cell = tables ? tables->head : NULL; do { ParallelSlot *free_slot; const char *tabname = cell ? cell->val : NULL; prepare_vacuum_command(&sql, conn, vacopts, tabname); if (CancelRequested) { result = -1; goto finish; } /* * Get the connection slot to use. If in parallel mode, here we wait * for one connection to become available if none already is. In * non-parallel mode we simply use the only slot we have, which we * know to be free. */ if (parallel) { /* * Get a free slot, waiting until one becomes free if none * currently is. */ free_slot = GetIdleSlot(slots, concurrentCons, dbname, progname); if (!free_slot) { result = -1; goto finish; } free_slot->isFree = false; } else free_slot = slots; run_vacuum_command(free_slot->connection, sql.data, echo, dbname, tabname, progname, parallel); if (cell) cell = cell->next; } while (cell != NULL); if (parallel) { int j; for (j = 0; j < concurrentCons; j++) { /* wait for all connection to return the results */ if (!GetQueryResult((slots + j)->connection, dbname, progname)) goto finish; (slots + j)->isFree = true; } } finish: for (i = 0; i < concurrentCons; i++) DisconnectDatabase(slots + i); pfree(slots); termPQExpBuffer(&sql); if (result == -1) exit(1); }
int main(int argc, char *argv[]) { static struct option long_options[] = { {"host", required_argument, NULL, 'h'}, {"port", required_argument, NULL, 'p'}, {"username", required_argument, NULL, 'U'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"quiet", no_argument, NULL, 'q'}, {"dbname", required_argument, NULL, 'd'}, {"analyze", no_argument, NULL, 'z'}, {"analyze-only", no_argument, NULL, 'Z'}, {"freeze", no_argument, NULL, 'F'}, {"all", no_argument, NULL, 'a'}, {"table", required_argument, NULL, 't'}, {"full", no_argument, NULL, 'f'}, {"verbose", no_argument, NULL, 'v'}, {"jobs", required_argument, NULL, 'j'}, {"maintenance-db", required_argument, NULL, 2}, {"analyze-in-stages", no_argument, NULL, 3}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; const char *dbname = NULL; const char *maintenance_db = NULL; char *host = NULL; char *port = NULL; char *username = NULL; enum trivalue prompt_password = TRI_DEFAULT; bool echo = false; bool quiet = false; vacuumingOptions vacopts; bool analyze_in_stages = false; bool alldb = false; SimpleStringList tables = {NULL, NULL}; int concurrentCons = 1; int tbl_count = 0; /* initialize options to all false */ memset(&vacopts, 0, sizeof(vacopts)); progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "vacuumdb", help); while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:", long_options, &optindex)) != -1) { switch (c) { case 'h': host = pg_strdup(optarg); break; case 'p': port = pg_strdup(optarg); break; case 'U': username = pg_strdup(optarg); break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'q': quiet = true; break; case 'd': dbname = pg_strdup(optarg); break; case 'z': vacopts.and_analyze = true; break; case 'Z': vacopts.analyze_only = true; break; case 'F': vacopts.freeze = true; break; case 'a': alldb = true; break; case 't': { simple_string_list_append(&tables, optarg); tbl_count++; break; } case 'f': vacopts.full = true; break; case 'v': vacopts.verbose = true; break; case 'j': concurrentCons = atoi(optarg); if (concurrentCons <= 0) { fprintf(stderr, _("%s: number of parallel \"jobs\" must be at least 1\n"), progname); exit(1); } if (concurrentCons > FD_SETSIZE - 1) { fprintf(stderr, _("%s: too many parallel jobs requested (maximum: %d)\n"), progname, FD_SETSIZE - 1); exit(1); } break; case 2: maintenance_db = pg_strdup(optarg); break; case 3: analyze_in_stages = vacopts.analyze_only = true; break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } /* * Non-option argument specifies database name as long as it wasn't * already specified with -d / --dbname */ if (optind < argc && dbname == NULL) { dbname = argv[optind]; optind++; } if (optind < argc) { fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind]); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } if (vacopts.analyze_only) { if (vacopts.full) { fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"), progname, "full"); exit(1); } if (vacopts.freeze) { fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"), progname, "freeze"); exit(1); } /* allow 'and_analyze' with 'analyze_only' */ } setup_cancel_handler(); /* Avoid opening extra connections. */ if (tbl_count && (concurrentCons > tbl_count)) concurrentCons = tbl_count; if (alldb) { if (dbname) { fprintf(stderr, _("%s: cannot vacuum all databases and a specific one at the same time\n"), progname); exit(1); } if (tables.head != NULL) { fprintf(stderr, _("%s: cannot vacuum specific table(s) in all databases\n"), progname); exit(1); } vacuum_all_databases(&vacopts, analyze_in_stages, maintenance_db, host, port, username, prompt_password, concurrentCons, progname, echo, quiet); } else { if (dbname == NULL) { if (getenv("PGDATABASE")) dbname = getenv("PGDATABASE"); else if (getenv("PGUSER")) dbname = getenv("PGUSER"); else dbname = get_user_name_or_exit(progname); } if (analyze_in_stages) { int stage; for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++) { vacuum_one_database(dbname, &vacopts, stage, &tables, host, port, username, prompt_password, concurrentCons, progname, echo, quiet); } } else vacuum_one_database(dbname, &vacopts, ANALYZE_NO_STAGE, &tables, host, port, username, prompt_password, concurrentCons, progname, echo, quiet); } exit(0); }
int main(int argc, char *argv[]) { static struct option long_options[] = { {"host", required_argument, NULL, 'h'}, {"port", required_argument, NULL, 'p'}, {"username", required_argument, NULL, 'U'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"quiet", no_argument, NULL, 'q'}, {"dbname", required_argument, NULL, 'd'}, {"all", no_argument, NULL, 'a'}, {"table", required_argument, NULL, 't'}, {"verbose", no_argument, NULL, 'v'}, {"maintenance-db", required_argument, NULL, 2}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; const char *dbname = NULL; const char *maintenance_db = NULL; char *host = NULL; char *port = NULL; char *username = NULL; enum trivalue prompt_password = TRI_DEFAULT; bool echo = false; bool quiet = false; bool alldb = false; bool verbose = false; SimpleStringList tables = {NULL, NULL}; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "clusterdb", help); while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:at:v", long_options, &optindex)) != -1) { switch (c) { case 'h': host = pg_strdup(optarg); break; case 'p': port = pg_strdup(optarg); break; case 'U': username = pg_strdup(optarg); break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'q': quiet = true; break; case 'd': dbname = pg_strdup(optarg); break; case 'a': alldb = true; break; case 't': simple_string_list_append(&tables, optarg); break; case 'v': verbose = true; break; case 2: maintenance_db = pg_strdup(optarg); break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } /* * Non-option argument specifies database name as long as it wasn't * already specified with -d / --dbname */ if (optind < argc && dbname == NULL) { dbname = argv[optind]; optind++; } if (optind < argc) { fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind]); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } setup_cancel_handler(); if (alldb) { if (dbname) { fprintf(stderr, _("%s: cannot cluster all databases and a specific one at the same time\n"), progname); exit(1); } if (tables.head != NULL) { fprintf(stderr, _("%s: cannot cluster specific table(s) in all databases\n"), progname); exit(1); } cluster_all_databases(verbose, maintenance_db, host, port, username, prompt_password, progname, echo, quiet); } else { if (dbname == NULL) { if (getenv("PGDATABASE")) dbname = getenv("PGDATABASE"); else if (getenv("PGUSER")) dbname = getenv("PGUSER"); else dbname = get_user_name_or_exit(progname); } if (tables.head != NULL) { SimpleStringListCell *cell; for (cell = tables.head; cell; cell = cell->next) { cluster_one_database(dbname, verbose, cell->val, host, port, username, prompt_password, progname, echo); } } else cluster_one_database(dbname, verbose, NULL, host, port, username, prompt_password, progname, echo); } exit(0); }
int main(int argc, char **argv) { RestoreOptions *opts; int c; int exit_code; int numWorkers = 1; Archive *AH; char *inputFileSpec; static int disable_triggers = 0; static int if_exists = 0; static int no_data_for_failed_tables = 0; static int outputNoTablespaces = 0; static int use_setsessauth = 0; static int no_security_labels = 0; struct option cmdopts[] = { {"clean", 0, NULL, 'c'}, {"create", 0, NULL, 'C'}, {"data-only", 0, NULL, 'a'}, {"dbname", 1, NULL, 'd'}, {"exit-on-error", 0, NULL, 'e'}, {"file", 1, NULL, 'f'}, {"format", 1, NULL, 'F'}, {"function", 1, NULL, 'P'}, {"host", 1, NULL, 'h'}, {"ignore-version", 0, NULL, 'i'}, {"index", 1, NULL, 'I'}, {"jobs", 1, NULL, 'j'}, {"list", 0, NULL, 'l'}, {"no-privileges", 0, NULL, 'x'}, {"no-acl", 0, NULL, 'x'}, {"no-owner", 0, NULL, 'O'}, {"no-reconnect", 0, NULL, 'R'}, {"port", 1, NULL, 'p'}, {"no-password", 0, NULL, 'w'}, {"password", 0, NULL, 'W'}, {"schema", 1, NULL, 'n'}, {"schema-only", 0, NULL, 's'}, {"superuser", 1, NULL, 'S'}, {"table", 1, NULL, 't'}, {"trigger", 1, NULL, 'T'}, {"use-list", 1, NULL, 'L'}, {"username", 1, NULL, 'U'}, {"verbose", 0, NULL, 'v'}, {"single-transaction", 0, NULL, '1'}, /* * the following options don't have an equivalent short option letter */ {"disable-triggers", no_argument, &disable_triggers, 1}, {"if-exists", no_argument, &if_exists, 1}, {"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1}, {"no-tablespaces", no_argument, &outputNoTablespaces, 1}, {"role", required_argument, NULL, 2}, {"section", required_argument, NULL, 3}, {"use-set-session-authorization", no_argument, &use_setsessauth, 1}, {"no-security-labels", no_argument, &no_security_labels, 1}, {NULL, 0, NULL, 0} }; set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_dump")); init_parallel_dump_utils(); opts = NewRestoreOptions(); progname = get_progname(argv[0]); if (argc > 1) { if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0) { usage(progname); exit_nicely(0); } if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0) { puts("pg_restore (PostgreSQL) " PG_VERSION); exit_nicely(0); } } while ((c = getopt_long(argc, argv, "acCd:ef:F:h:iI:j:lL:n:Op:P:RsS:t:T:U:vwWx1", cmdopts, NULL)) != -1) { switch (c) { case 'a': /* Dump data only */ opts->dataOnly = 1; break; case 'c': /* clean (i.e., drop) schema prior to create */ opts->dropSchema = 1; break; case 'C': opts->createDB = 1; break; case 'd': opts->dbname = pg_strdup(optarg); break; case 'e': opts->exit_on_error = true; break; case 'f': /* output file name */ opts->filename = pg_strdup(optarg); break; case 'F': if (strlen(optarg) != 0) opts->formatName = pg_strdup(optarg); break; case 'h': if (strlen(optarg) != 0) opts->pghost = pg_strdup(optarg); break; case 'i': /* ignored, deprecated option */ break; case 'j': /* number of restore jobs */ numWorkers = atoi(optarg); break; case 'l': /* Dump the TOC summary */ opts->tocSummary = 1; break; case 'L': /* input TOC summary file name */ opts->tocFile = pg_strdup(optarg); break; case 'n': /* Dump data for this schema only */ simple_string_list_append(&opts->schemaNames, optarg); break; case 'O': opts->noOwner = 1; break; case 'p': if (strlen(optarg) != 0) opts->pgport = pg_strdup(optarg); break; case 'R': /* no-op, still accepted for backwards compatibility */ break; case 'P': /* Function */ opts->selTypes = 1; opts->selFunction = 1; simple_string_list_append(&opts->functionNames, optarg); break; case 'I': /* Index */ opts->selTypes = 1; opts->selIndex = 1; simple_string_list_append(&opts->indexNames, optarg); break; case 'T': /* Trigger */ opts->selTypes = 1; opts->selTrigger = 1; simple_string_list_append(&opts->triggerNames, optarg); break; case 's': /* dump schema only */ opts->schemaOnly = 1; break; case 'S': /* Superuser username */ if (strlen(optarg) != 0) opts->superuser = pg_strdup(optarg); break; case 't': /* Dump data for this table only */ opts->selTypes = 1; opts->selTable = 1; simple_string_list_append(&opts->tableNames, optarg); break; case 'U': opts->username = pg_strdup(optarg); break; case 'v': /* verbose */ opts->verbose = 1; break; case 'w': opts->promptPassword = TRI_NO; break; case 'W': opts->promptPassword = TRI_YES; break; case 'x': /* skip ACL dump */ opts->aclsSkip = 1; break; case '1': /* Restore data in a single transaction */ opts->single_txn = true; opts->exit_on_error = true; break; case 0: /* * This covers the long options without a short equivalent. */ break; case 2: /* SET ROLE */ opts->use_role = pg_strdup(optarg); break; case 3: /* section */ set_dump_section(optarg, &(opts->dumpSections)); break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit_nicely(1); } } /* Get file name from command line */ if (optind < argc) inputFileSpec = argv[optind++]; else inputFileSpec = NULL; /* Complain if any arguments remain */ if (optind < argc) { fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind]); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit_nicely(1); } /* Should get at most one of -d and -f, else user is confused */ if (opts->dbname) { if (opts->filename) { fprintf(stderr, _("%s: options -d/--dbname and -f/--file cannot be used together\n"), progname); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit_nicely(1); } opts->useDB = 1; } if (opts->dataOnly && opts->schemaOnly) { fprintf(stderr, _("%s: options -s/--schema-only and -a/--data-only cannot be used together\n"), progname); exit_nicely(1); } if (opts->dataOnly && opts->dropSchema) { fprintf(stderr, _("%s: options -c/--clean and -a/--data-only cannot be used together\n"), progname); exit_nicely(1); } /* Can't do single-txn mode with multiple connections */ if (opts->single_txn && numWorkers > 1) { fprintf(stderr, _("%s: cannot specify both --single-transaction and multiple jobs\n"), progname); exit_nicely(1); } opts->disable_triggers = disable_triggers; opts->noDataForFailedTables = no_data_for_failed_tables; opts->noTablespace = outputNoTablespaces; opts->use_setsessauth = use_setsessauth; opts->no_security_labels = no_security_labels; if (if_exists && !opts->dropSchema) { fprintf(stderr, _("%s: option --if-exists requires -c/--clean option\n"), progname); exit_nicely(1); } opts->if_exists = if_exists; if (opts->formatName) { switch (opts->formatName[0]) { case 'c': case 'C': opts->format = archCustom; break; case 'd': case 'D': opts->format = archDirectory; break; case 't': case 'T': opts->format = archTar; break; default: write_msg(NULL, "unrecognized archive format \"%s\"; please specify \"c\", \"d\", or \"t\"\n", opts->formatName); exit_nicely(1); } } AH = OpenArchive(inputFileSpec, opts->format); /* * We don't have a connection yet but that doesn't matter. The connection * is initialized to NULL and if we terminate through exit_nicely() while * it's still NULL, the cleanup function will just be a no-op. */ on_exit_close_archive(AH); /* Let the archiver know how noisy to be */ AH->verbose = opts->verbose; /* * Whether to keep submitting sql commands as "pg_restore ... | psql ... " */ AH->exit_on_error = opts->exit_on_error; if (opts->tocFile) SortTocFromFile(AH, opts); /* See comments in pg_dump.c */ #ifdef WIN32 if (numWorkers > MAXIMUM_WAIT_OBJECTS) { fprintf(stderr, _("%s: maximum number of parallel jobs is %d\n"), progname, MAXIMUM_WAIT_OBJECTS); exit(1); } #endif AH->numWorkers = numWorkers; if (opts->tocSummary) PrintTOCSummary(AH, opts); else { SetArchiveRestoreOptions(AH, opts); RestoreArchive(AH); } /* done, print a summary of ignored errors */ if (AH->n_errors) fprintf(stderr, _("WARNING: errors ignored on restore: %d\n"), AH->n_errors); /* AH may be freed in CloseArchive? */ exit_code = AH->n_errors ? 1 : 0; CloseArchive(AH); return exit_code; }
int main(int argc, char *argv[]) { static struct option long_options[] = { {"host", required_argument, NULL, 'h'}, {"port", required_argument, NULL, 'p'}, {"username", required_argument, NULL, 'U'}, {"role", required_argument, NULL, 'g'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"createdb", no_argument, NULL, 'd'}, {"no-createdb", no_argument, NULL, 'D'}, {"superuser", no_argument, NULL, 's'}, {"no-superuser", no_argument, NULL, 'S'}, {"createrole", no_argument, NULL, 'r'}, {"no-createrole", no_argument, NULL, 'R'}, {"inherit", no_argument, NULL, 'i'}, {"no-inherit", no_argument, NULL, 'I'}, {"login", no_argument, NULL, 'l'}, {"no-login", no_argument, NULL, 'L'}, {"replication", no_argument, NULL, 1}, {"no-replication", no_argument, NULL, 2}, {"interactive", no_argument, NULL, 3}, /* adduser is obsolete, undocumented spelling of superuser */ {"adduser", no_argument, NULL, 'a'}, {"no-adduser", no_argument, NULL, 'A'}, {"connection-limit", required_argument, NULL, 'c'}, {"pwprompt", no_argument, NULL, 'P'}, {"encrypted", no_argument, NULL, 'E'}, {"unencrypted", no_argument, NULL, 'N'}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; const char *newuser = NULL; char *host = NULL; char *port = NULL; char *username = NULL; SimpleStringList roles = {NULL, NULL}; enum trivalue prompt_password = TRI_DEFAULT; bool echo = false; bool interactive = false; char *conn_limit = NULL; bool pwprompt = false; char *newpassword = NULL; /* Tri-valued variables. */ enum trivalue createdb = TRI_DEFAULT, superuser = TRI_DEFAULT, createrole = TRI_DEFAULT, inherit = TRI_DEFAULT, login = TRI_DEFAULT, replication = TRI_DEFAULT, encrypted = TRI_DEFAULT; PQExpBufferData sql; PGconn *conn; PGresult *result; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "createuser", help); while ((c = getopt_long(argc, argv, "h:p:U:g:wWedDsSaArRiIlLc:PEN", long_options, &optindex)) != -1) { switch (c) { case 'h': host = pg_strdup(optarg); break; case 'p': port = pg_strdup(optarg); break; case 'U': username = pg_strdup(optarg); break; case 'g': simple_string_list_append(&roles, optarg); break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'd': createdb = TRI_YES; break; case 'D': createdb = TRI_NO; break; case 's': case 'a': superuser = TRI_YES; break; case 'S': case 'A': superuser = TRI_NO; break; case 'r': createrole = TRI_YES; break; case 'R': createrole = TRI_NO; break; case 'i': inherit = TRI_YES; break; case 'I': inherit = TRI_NO; break; case 'l': login = TRI_YES; break; case 'L': login = TRI_NO; break; case 'c': conn_limit = pg_strdup(optarg); break; case 'P': pwprompt = true; break; case 'E': encrypted = TRI_YES; break; case 'N': encrypted = TRI_NO; break; case 1: replication = TRI_YES; break; case 2: replication = TRI_NO; break; case 3: interactive = true; break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } switch (argc - optind) { case 0: break; case 1: newuser = argv[optind]; break; default: fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind + 1]); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } if (newuser == NULL) { if (interactive) newuser = simple_prompt("Enter name of role to add: ", 128, true); else { if (getenv("PGUSER")) newuser = getenv("PGUSER"); else newuser = get_user_name_or_exit(progname); } } if (pwprompt) { char *pw1, *pw2; pw1 = simple_prompt("Enter password for new role: ", 100, false); pw2 = simple_prompt("Enter it again: ", 100, false); if (strcmp(pw1, pw2) != 0) { fprintf(stderr, _("Passwords didn't match.\n")); exit(1); } newpassword = pw1; free(pw2); } if (superuser == 0) { if (interactive && yesno_prompt("Shall the new role be a superuser?")) superuser = TRI_YES; else superuser = TRI_NO; } if (superuser == TRI_YES) { /* Not much point in trying to restrict a superuser */ createdb = TRI_YES; createrole = TRI_YES; } if (createdb == 0) { if (interactive && yesno_prompt("Shall the new role be allowed to create databases?")) createdb = TRI_YES; else createdb = TRI_NO; } if (createrole == 0) { if (interactive && yesno_prompt("Shall the new role be allowed to create more new roles?")) createrole = TRI_YES; else createrole = TRI_NO; } if (inherit == 0) inherit = TRI_YES; if (login == 0) login = TRI_YES; conn = connectDatabase("postgres", host, port, username, prompt_password, progname, false); initPQExpBuffer(&sql); printfPQExpBuffer(&sql, "CREATE ROLE %s", fmtId(newuser)); if (newpassword) { if (encrypted == TRI_YES) appendPQExpBufferStr(&sql, " ENCRYPTED"); if (encrypted == TRI_NO) appendPQExpBufferStr(&sql, " UNENCRYPTED"); appendPQExpBufferStr(&sql, " PASSWORD "); if (encrypted != TRI_NO) { char *encrypted_password; encrypted_password = PQencryptPassword(newpassword, newuser); if (!encrypted_password) { fprintf(stderr, _("Password encryption failed.\n")); exit(1); } appendStringLiteralConn(&sql, encrypted_password, conn); PQfreemem(encrypted_password); } else appendStringLiteralConn(&sql, newpassword, conn); } if (superuser == TRI_YES) appendPQExpBufferStr(&sql, " SUPERUSER"); if (superuser == TRI_NO) appendPQExpBufferStr(&sql, " NOSUPERUSER"); if (createdb == TRI_YES) appendPQExpBufferStr(&sql, " CREATEDB"); if (createdb == TRI_NO) appendPQExpBufferStr(&sql, " NOCREATEDB"); if (createrole == TRI_YES) appendPQExpBufferStr(&sql, " CREATEROLE"); if (createrole == TRI_NO) appendPQExpBufferStr(&sql, " NOCREATEROLE"); if (inherit == TRI_YES) appendPQExpBufferStr(&sql, " INHERIT"); if (inherit == TRI_NO) appendPQExpBufferStr(&sql, " NOINHERIT"); if (login == TRI_YES) appendPQExpBufferStr(&sql, " LOGIN"); if (login == TRI_NO) appendPQExpBufferStr(&sql, " NOLOGIN"); if (replication == TRI_YES) appendPQExpBufferStr(&sql, " REPLICATION"); if (replication == TRI_NO) appendPQExpBufferStr(&sql, " NOREPLICATION"); if (conn_limit != NULL) appendPQExpBuffer(&sql, " CONNECTION LIMIT %s", conn_limit); if (roles.head != NULL) { SimpleStringListCell *cell; appendPQExpBufferStr(&sql, " IN ROLE "); for (cell = roles.head; cell; cell = cell->next) { if (cell->next) appendPQExpBuffer(&sql, "%s,", fmtId(cell->val)); else appendPQExpBuffer(&sql, "%s", fmtId(cell->val)); } } appendPQExpBufferStr(&sql, ";\n"); if (echo) printf("%s", sql.data); result = PQexec(conn, sql.data); if (PQresultStatus(result) != PGRES_COMMAND_OK) { fprintf(stderr, _("%s: creation of new role failed: %s"), progname, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQclear(result); PQfinish(conn); exit(0); }
int main(int argc, char *argv[]) { static struct option long_options[] = { {"host", required_argument, NULL, 'h'}, {"port", required_argument, NULL, 'p'}, {"username", required_argument, NULL, 'U'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"quiet", no_argument, NULL, 'q'}, {"schema", required_argument, NULL, 'S'}, {"dbname", required_argument, NULL, 'd'}, {"all", no_argument, NULL, 'a'}, {"system", no_argument, NULL, 's'}, {"table", required_argument, NULL, 't'}, {"index", required_argument, NULL, 'i'}, {"verbose", no_argument, NULL, 'v'}, {"maintenance-db", required_argument, NULL, 2}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; const char *dbname = NULL; const char *maintenance_db = NULL; const char *host = NULL; const char *port = NULL; const char *username = NULL; enum trivalue prompt_password = TRI_DEFAULT; bool syscatalog = false; bool alldb = false; bool echo = false; bool quiet = false; bool verbose = false; SimpleStringList indexes = {NULL, NULL}; SimpleStringList tables = {NULL, NULL}; SimpleStringList schemas = {NULL, NULL}; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "reindexdb", help); /* process command-line options */ while ((c = getopt_long(argc, argv, "h:p:U:wWeqS:d:ast:i:v", long_options, &optindex)) != -1) { switch (c) { case 'h': host = pg_strdup(optarg); break; case 'p': port = pg_strdup(optarg); break; case 'U': username = pg_strdup(optarg); break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'q': quiet = true; break; case 'S': simple_string_list_append(&schemas, optarg); break; case 'd': dbname = pg_strdup(optarg); break; case 'a': alldb = true; break; case 's': syscatalog = true; break; case 't': simple_string_list_append(&tables, optarg); break; case 'i': simple_string_list_append(&indexes, optarg); break; case 'v': verbose = true; break; case 2: maintenance_db = pg_strdup(optarg); break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } /* * Non-option argument specifies database name as long as it wasn't * already specified with -d / --dbname */ if (optind < argc && dbname == NULL) { dbname = argv[optind]; optind++; } if (optind < argc) { fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind]); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } setup_cancel_handler(); if (alldb) { if (dbname) { fprintf(stderr, _("%s: cannot reindex all databases and a specific one at the same time\n"), progname); exit(1); } if (syscatalog) { fprintf(stderr, _("%s: cannot reindex all databases and system catalogs at the same time\n"), progname); exit(1); } if (schemas.head != NULL) { fprintf(stderr, _("%s: cannot reindex specific schema(s) in all databases\n"), progname); exit(1); } if (tables.head != NULL) { fprintf(stderr, _("%s: cannot reindex specific table(s) in all databases\n"), progname); exit(1); } if (indexes.head != NULL) { fprintf(stderr, _("%s: cannot reindex specific index(es) in all databases\n"), progname); exit(1); } reindex_all_databases(maintenance_db, host, port, username, prompt_password, progname, echo, quiet, verbose); } else if (syscatalog) { if (schemas.head != NULL) { fprintf(stderr, _("%s: cannot reindex specific schema(s) and system catalogs at the same time\n"), progname); exit(1); } if (tables.head != NULL) { fprintf(stderr, _("%s: cannot reindex specific table(s) and system catalogs at the same time\n"), progname); exit(1); } if (indexes.head != NULL) { fprintf(stderr, _("%s: cannot reindex specific index(es) and system catalogs at the same time\n"), progname); exit(1); } if (dbname == NULL) { if (getenv("PGDATABASE")) dbname = getenv("PGDATABASE"); else if (getenv("PGUSER")) dbname = getenv("PGUSER"); else dbname = get_user_name_or_exit(progname); } reindex_system_catalogs(dbname, host, port, username, prompt_password, progname, echo, verbose); } else { if (dbname == NULL) { if (getenv("PGDATABASE")) dbname = getenv("PGDATABASE"); else if (getenv("PGUSER")) dbname = getenv("PGUSER"); else dbname = get_user_name_or_exit(progname); } if (schemas.head != NULL) { SimpleStringListCell *cell; for (cell = schemas.head; cell; cell = cell->next) { reindex_one_database(cell->val, dbname, "SCHEMA", host, port, username, prompt_password, progname, echo, verbose); } } if (indexes.head != NULL) { SimpleStringListCell *cell; for (cell = indexes.head; cell; cell = cell->next) { reindex_one_database(cell->val, dbname, "INDEX", host, port, username, prompt_password, progname, echo, verbose); } } if (tables.head != NULL) { SimpleStringListCell *cell; for (cell = tables.head; cell; cell = cell->next) { reindex_one_database(cell->val, dbname, "TABLE", host, port, username, prompt_password, progname, echo, verbose); } } /* * reindex database only if neither index nor table nor schema is * specified */ if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL) reindex_one_database(dbname, dbname, "DATABASE", host, port, username, prompt_password, progname, echo, verbose); } exit(0); }
/* * vacuum_one_database * * Process tables in the given database. If the 'tables' list is empty, * process all tables in the database. * * Note that this function is only concerned with running exactly one stage * when in analyze-in-stages mode; caller must iterate on us if necessary. * * If concurrentCons is > 1, multiple connections are used to vacuum tables * in parallel. In this case and if the table list is empty, we first obtain * a list of tables from the database. */ static void vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, const char *host, const char *port, const char *username, enum trivalue prompt_password, int concurrentCons, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; PQExpBufferData buf; PQExpBufferData catalog_query; PGresult *res; PGconn *conn; SimpleStringListCell *cell; ParallelSlot *slots; SimpleStringList dbtables = {NULL, NULL}; int i; int ntups; bool failed = false; bool parallel = concurrentCons > 1; bool tables_listed = false; bool has_where = false; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", "RESET default_statistics_target;" }; const char *stage_messages[] = { gettext_noop("Generating minimal optimizer statistics (1 target)"), gettext_noop("Generating medium optimizer statistics (10 targets)"), gettext_noop("Generating default (full) optimizer statistics") }; Assert(stage == ANALYZE_NO_STAGE || (stage >= 0 && stage < ANALYZE_NUM_STAGES)); conn = connectDatabase(dbname, host, port, username, prompt_password, progname, echo, false, true); if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600) { PQfinish(conn); fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"), progname, "disable-page-skipping"); exit(1); } if (vacopts->skip_locked && PQserverVersion(conn) < 120000) { PQfinish(conn); fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 12\n"), progname, "skip-locked"); exit(1); } if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600) { fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"), progname, "--min-xid-age"); exit(1); } if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600) { fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"), progname, "--min-mxid-age"); exit(1); } if (!quiet) { if (stage != ANALYZE_NO_STAGE) printf(_("%s: processing database \"%s\": %s\n"), progname, PQdb(conn), _(stage_messages[stage])); else printf(_("%s: vacuuming database \"%s\"\n"), progname, PQdb(conn)); fflush(stdout); } /* * Prepare the list of tables to process by querying the catalogs. * * Since we execute the constructed query with the default search_path * (which could be unsafe), everything in this query MUST be fully * qualified. * * First, build a WITH clause for the catalog query if any tables were * specified, with a set of values made of relation names and their * optional set of columns. This is used to match any provided column * lists with the generated qualified identifiers and to filter for the * tables provided via --table. If a listed table does not exist, the * catalog query will fail. */ initPQExpBuffer(&catalog_query); for (cell = tables ? tables->head : NULL; cell; cell = cell->next) { char *just_table; const char *just_columns; /* * Split relation and column names given by the user, this is used to * feed the CTE with values on which are performed pre-run validity * checks as well. For now these happen only on the relation name. */ splitTableColumnsSpec(cell->val, PQclientEncoding(conn), &just_table, &just_columns); if (!tables_listed) { appendPQExpBuffer(&catalog_query, "WITH listed_tables (table_oid, column_list) " "AS (\n VALUES ("); tables_listed = true; } else appendPQExpBuffer(&catalog_query, ",\n ("); appendStringLiteralConn(&catalog_query, just_table, conn); appendPQExpBuffer(&catalog_query, "::pg_catalog.regclass, "); if (just_columns && just_columns[0] != '\0') appendStringLiteralConn(&catalog_query, just_columns, conn); else appendPQExpBufferStr(&catalog_query, "NULL"); appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)"); pg_free(just_table); } /* Finish formatting the CTE */ if (tables_listed) appendPQExpBuffer(&catalog_query, "\n)\n"); appendPQExpBuffer(&catalog_query, "SELECT c.relname, ns.nspname"); if (tables_listed) appendPQExpBuffer(&catalog_query, ", listed_tables.column_list"); appendPQExpBuffer(&catalog_query, " FROM pg_catalog.pg_class c\n" " JOIN pg_catalog.pg_namespace ns" " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n" " LEFT JOIN pg_catalog.pg_class t" " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n"); /* Used to match the tables listed by the user */ if (tables_listed) appendPQExpBuffer(&catalog_query, " JOIN listed_tables" " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n"); /* * If no tables were listed, filter for the relevant relation types. If * tables were given via --table, don't bother filtering by relation type. * Instead, let the server decide whether a given relation can be * processed in which case the user will know about it. */ if (!tables_listed) { appendPQExpBuffer(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array[" CppAsString2(RELKIND_RELATION) ", " CppAsString2(RELKIND_MATVIEW) "])\n"); has_where = true; } /* * For --min-xid-age and --min-mxid-age, the age of the relation is the * greatest of the ages of the main relation and its associated TOAST * table. The commands generated by vacuumdb will also process the TOAST * table for the relation if necessary, so it does not need to be * considered separately. */ if (vacopts->min_xid_age != 0) { appendPQExpBuffer(&catalog_query, " %s GREATEST(pg_catalog.age(c.relfrozenxid)," " pg_catalog.age(t.relfrozenxid)) " " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n" " AND c.relfrozenxid OPERATOR(pg_catalog.!=)" " '0'::pg_catalog.xid\n", has_where ? "AND" : "WHERE", vacopts->min_xid_age); has_where = true; } if (vacopts->min_mxid_age != 0) { appendPQExpBuffer(&catalog_query, " %s GREATEST(pg_catalog.mxid_age(c.relminmxid)," " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)" " '%d'::pg_catalog.int4\n" " AND c.relminmxid OPERATOR(pg_catalog.!=)" " '0'::pg_catalog.xid\n", has_where ? "AND" : "WHERE", vacopts->min_mxid_age); has_where = true; } /* * Execute the catalog query. We use the default search_path for this * query for consistency with table lookups done elsewhere by the user. */ appendPQExpBuffer(&catalog_query, " ORDER BY c.relpages DESC;"); executeCommand(conn, "RESET search_path;", progname, echo); res = executeQuery(conn, catalog_query.data, progname, echo); termPQExpBuffer(&catalog_query); PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, progname, echo)); /* * If no rows are returned, there are no matching tables, so we are done. */ ntups = PQntuples(res); if (ntups == 0) { PQclear(res); PQfinish(conn); return; } /* * Build qualified identifiers for each table, including the column list * if given. */ initPQExpBuffer(&buf); for (i = 0; i < ntups; i++) { appendPQExpBufferStr(&buf, fmtQualifiedId(PQgetvalue(res, i, 1), PQgetvalue(res, i, 0))); if (tables_listed && !PQgetisnull(res, i, 2)) appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2)); simple_string_list_append(&dbtables, buf.data); resetPQExpBuffer(&buf); } termPQExpBuffer(&buf); PQclear(res); /* * If there are more connections than vacuumable relations, we don't need * to use them all. */ if (parallel) { if (concurrentCons > ntups) concurrentCons = ntups; if (concurrentCons <= 1) parallel = false; } /* * Setup the database connections. We reuse the connection we already have * for the first slot. If not in parallel mode, the first slot in the * array contains the connection. */ if (concurrentCons <= 0) concurrentCons = 1; slots = (ParallelSlot *) pg_malloc(sizeof(ParallelSlot) * concurrentCons); init_slot(slots, conn); if (parallel) { for (i = 1; i < concurrentCons; i++) { conn = connectDatabase(dbname, host, port, username, prompt_password, progname, echo, false, true); init_slot(slots + i, conn); } } /* * Prepare all the connections to run the appropriate analyze stage, if * caller requested that mode. */ if (stage != ANALYZE_NO_STAGE) { int j; /* We already emitted the message above */ for (j = 0; j < concurrentCons; j++) executeCommand((slots + j)->connection, stage_commands[stage], progname, echo); } initPQExpBuffer(&sql); cell = dbtables.head; do { const char *tabname = cell->val; ParallelSlot *free_slot; if (CancelRequested) { failed = true; goto finish; } /* * Get the connection slot to use. If in parallel mode, here we wait * for one connection to become available if none already is. In * non-parallel mode we simply use the only slot we have, which we * know to be free. */ if (parallel) { /* * Get a free slot, waiting until one becomes free if none * currently is. */ free_slot = GetIdleSlot(slots, concurrentCons, progname); if (!free_slot) { failed = true; goto finish; } free_slot->isFree = false; } else free_slot = slots; prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), vacopts, tabname); /* * Execute the vacuum. If not in parallel mode, this terminates the * program in case of an error. (The parallel case handles query * errors in ProcessQueryResult through GetIdleSlot.) */ run_vacuum_command(free_slot->connection, sql.data, echo, tabname, progname, parallel); cell = cell->next; } while (cell != NULL); if (parallel) { int j; /* wait for all connections to finish */ for (j = 0; j < concurrentCons; j++) { if (!GetQueryResult((slots + j)->connection, progname)) goto finish; } } finish: for (i = 0; i < concurrentCons; i++) DisconnectDatabase(slots + i); pfree(slots); termPQExpBuffer(&sql); if (failed) exit(1); }