PostgreSQL Source Code  git master
vacuumdb.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * vacuumdb
4  *
5  * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
6  * Portions Copyright (c) 1994, Regents of the University of California
7  *
8  * src/bin/scripts/vacuumdb.c
9  *
10  *-------------------------------------------------------------------------
11  */
12 
13 #include "postgres_fe.h"
14 
15 #include "catalog/pg_class_d.h"
16 
17 #include "common.h"
18 #include "common/logging.h"
19 #include "fe_utils/connect.h"
20 #include "fe_utils/simple_list.h"
21 #include "fe_utils/string_utils.h"
22 #include "scripts_parallel.h"
23 
24 
25 /* vacuum options controlled by user flags */
26 typedef struct vacuumingOptions
27 {
29  bool verbose;
31  bool full;
32  bool freeze;
38 
39 
40 static void vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
41  int stage,
42  SimpleStringList *tables,
43  const char *host, const char *port,
44  const char *username, enum trivalue prompt_password,
45  int concurrentCons,
46  const char *progname, bool echo, bool quiet);
47 
48 static void vacuum_all_databases(vacuumingOptions *vacopts,
49  bool analyze_in_stages,
50  const char *maintenance_db,
51  const char *host, const char *port,
52  const char *username, enum trivalue prompt_password,
53  int concurrentCons,
54  const char *progname, bool echo, bool quiet);
55 
56 static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
57  vacuumingOptions *vacopts, const char *table);
58 
59 static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
60  const char *table);
61 
62 static void help(const char *progname);
63 
64 /* For analyze-in-stages mode */
65 #define ANALYZE_NO_STAGE -1
66 #define ANALYZE_NUM_STAGES 3
67 
68 
69 int
70 main(int argc, char *argv[])
71 {
72  static struct option long_options[] = {
73  {"host", required_argument, NULL, 'h'},
74  {"port", required_argument, NULL, 'p'},
75  {"username", required_argument, NULL, 'U'},
76  {"no-password", no_argument, NULL, 'w'},
77  {"password", no_argument, NULL, 'W'},
78  {"echo", no_argument, NULL, 'e'},
79  {"quiet", no_argument, NULL, 'q'},
80  {"dbname", required_argument, NULL, 'd'},
81  {"analyze", no_argument, NULL, 'z'},
82  {"analyze-only", no_argument, NULL, 'Z'},
83  {"freeze", no_argument, NULL, 'F'},
84  {"all", no_argument, NULL, 'a'},
85  {"table", required_argument, NULL, 't'},
86  {"full", no_argument, NULL, 'f'},
87  {"verbose", no_argument, NULL, 'v'},
88  {"jobs", required_argument, NULL, 'j'},
89  {"maintenance-db", required_argument, NULL, 2},
90  {"analyze-in-stages", no_argument, NULL, 3},
91  {"disable-page-skipping", no_argument, NULL, 4},
92  {"skip-locked", no_argument, NULL, 5},
93  {"min-xid-age", required_argument, NULL, 6},
94  {"min-mxid-age", required_argument, NULL, 7},
95  {NULL, 0, NULL, 0}
96  };
97 
98  const char *progname;
99  int optindex;
100  int c;
101  const char *dbname = NULL;
102  const char *maintenance_db = NULL;
103  char *host = NULL;
104  char *port = NULL;
105  char *username = NULL;
106  enum trivalue prompt_password = TRI_DEFAULT;
107  bool echo = false;
108  bool quiet = false;
109  vacuumingOptions vacopts;
110  bool analyze_in_stages = false;
111  bool alldb = false;
112  SimpleStringList tables = {NULL, NULL};
113  int concurrentCons = 1;
114  int tbl_count = 0;
115 
116  /* initialize options to all false */
117  memset(&vacopts, 0, sizeof(vacopts));
118 
119  pg_logging_init(argv[0]);
120  progname = get_progname(argv[0]);
121  set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
122 
123  handle_help_version_opts(argc, argv, "vacuumdb", help);
124 
125  while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:", long_options, &optindex)) != -1)
126  {
127  switch (c)
128  {
129  case 'h':
130  host = pg_strdup(optarg);
131  break;
132  case 'p':
133  port = pg_strdup(optarg);
134  break;
135  case 'U':
136  username = pg_strdup(optarg);
137  break;
138  case 'w':
139  prompt_password = TRI_NO;
140  break;
141  case 'W':
142  prompt_password = TRI_YES;
143  break;
144  case 'e':
145  echo = true;
146  break;
147  case 'q':
148  quiet = true;
149  break;
150  case 'd':
151  dbname = pg_strdup(optarg);
152  break;
153  case 'z':
154  vacopts.and_analyze = true;
155  break;
156  case 'Z':
157  vacopts.analyze_only = true;
158  break;
159  case 'F':
160  vacopts.freeze = true;
161  break;
162  case 'a':
163  alldb = true;
164  break;
165  case 't':
166  {
168  tbl_count++;
169  break;
170  }
171  case 'f':
172  vacopts.full = true;
173  break;
174  case 'v':
175  vacopts.verbose = true;
176  break;
177  case 'j':
178  concurrentCons = atoi(optarg);
179  if (concurrentCons <= 0)
180  {
181  pg_log_error("number of parallel jobs must be at least 1");
182  exit(1);
183  }
184  break;
185  case 2:
186  maintenance_db = pg_strdup(optarg);
187  break;
188  case 3:
189  analyze_in_stages = vacopts.analyze_only = true;
190  break;
191  case 4:
192  vacopts.disable_page_skipping = true;
193  break;
194  case 5:
195  vacopts.skip_locked = true;
196  break;
197  case 6:
198  vacopts.min_xid_age = atoi(optarg);
199  if (vacopts.min_xid_age <= 0)
200  {
201  pg_log_error("minimum transaction ID age must be at least 1");
202  exit(1);
203  }
204  break;
205  case 7:
206  vacopts.min_mxid_age = atoi(optarg);
207  if (vacopts.min_mxid_age <= 0)
208  {
209  pg_log_error("minimum multixact ID age must be at least 1");
210  exit(1);
211  }
212  break;
213  default:
214  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
215  exit(1);
216  }
217  }
218 
219  /*
220  * Non-option argument specifies database name as long as it wasn't
221  * already specified with -d / --dbname
222  */
223  if (optind < argc && dbname == NULL)
224  {
225  dbname = argv[optind];
226  optind++;
227  }
228 
229  if (optind < argc)
230  {
231  pg_log_error("too many command-line arguments (first is \"%s\")",
232  argv[optind]);
233  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
234  exit(1);
235  }
236 
237  if (vacopts.analyze_only)
238  {
239  if (vacopts.full)
240  {
241  pg_log_error("cannot use the \"%s\" option when performing only analyze",
242  "full");
243  exit(1);
244  }
245  if (vacopts.freeze)
246  {
247  pg_log_error("cannot use the \"%s\" option when performing only analyze",
248  "freeze");
249  exit(1);
250  }
251  if (vacopts.disable_page_skipping)
252  {
253  pg_log_error("cannot use the \"%s\" option when performing only analyze",
254  "disable-page-skipping");
255  exit(1);
256  }
257  /* allow 'and_analyze' with 'analyze_only' */
258  }
259 
261 
262  /* Avoid opening extra connections. */
263  if (tbl_count && (concurrentCons > tbl_count))
264  concurrentCons = tbl_count;
265 
266  if (alldb)
267  {
268  if (dbname)
269  {
270  pg_log_error("cannot vacuum all databases and a specific one at the same time");
271  exit(1);
272  }
273  if (tables.head != NULL)
274  {
275  pg_log_error("cannot vacuum specific table(s) in all databases");
276  exit(1);
277  }
278 
279  vacuum_all_databases(&vacopts,
280  analyze_in_stages,
281  maintenance_db,
282  host, port, username, prompt_password,
283  concurrentCons,
284  progname, echo, quiet);
285  }
286  else
287  {
288  if (dbname == NULL)
289  {
290  if (getenv("PGDATABASE"))
291  dbname = getenv("PGDATABASE");
292  else if (getenv("PGUSER"))
293  dbname = getenv("PGUSER");
294  else
295  dbname = get_user_name_or_exit(progname);
296  }
297 
298  if (analyze_in_stages)
299  {
300  int stage;
301 
302  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
303  {
304  vacuum_one_database(dbname, &vacopts,
305  stage,
306  &tables,
307  host, port, username, prompt_password,
308  concurrentCons,
309  progname, echo, quiet);
310  }
311  }
312  else
313  vacuum_one_database(dbname, &vacopts,
315  &tables,
316  host, port, username, prompt_password,
317  concurrentCons,
318  progname, echo, quiet);
319  }
320 
321  exit(0);
322 }
323 
324 /*
325  * vacuum_one_database
326  *
327  * Process tables in the given database. If the 'tables' list is empty,
328  * process all tables in the database.
329  *
330  * Note that this function is only concerned with running exactly one stage
331  * when in analyze-in-stages mode; caller must iterate on us if necessary.
332  *
333  * If concurrentCons is > 1, multiple connections are used to vacuum tables
334  * in parallel. In this case and if the table list is empty, we first obtain
335  * a list of tables from the database.
336  */
337 static void
339  int stage,
340  SimpleStringList *tables,
341  const char *host, const char *port,
342  const char *username, enum trivalue prompt_password,
343  int concurrentCons,
344  const char *progname, bool echo, bool quiet)
345 {
346  PQExpBufferData sql;
348  PQExpBufferData catalog_query;
349  PGresult *res;
350  PGconn *conn;
351  SimpleStringListCell *cell;
352  ParallelSlot *slots;
353  SimpleStringList dbtables = {NULL, NULL};
354  int i;
355  int ntups;
356  bool failed = false;
357  bool parallel = concurrentCons > 1;
358  bool tables_listed = false;
359  bool has_where = false;
360  const char *stage_commands[] = {
361  "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
362  "SET default_statistics_target=10; RESET vacuum_cost_delay;",
363  "RESET default_statistics_target;"
364  };
365  const char *stage_messages[] = {
366  gettext_noop("Generating minimal optimizer statistics (1 target)"),
367  gettext_noop("Generating medium optimizer statistics (10 targets)"),
368  gettext_noop("Generating default (full) optimizer statistics")
369  };
370 
371  Assert(stage == ANALYZE_NO_STAGE ||
372  (stage >= 0 && stage < ANALYZE_NUM_STAGES));
373 
374  conn = connectDatabase(dbname, host, port, username, prompt_password,
375  progname, echo, false, true);
376 
377  if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
378  {
379  PQfinish(conn);
380  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
381  "disable-page-skipping", "9.6");
382  exit(1);
383  }
384 
385  if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
386  {
387  PQfinish(conn);
388  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
389  "skip-locked", "12");
390  exit(1);
391  }
392 
393  if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
394  {
395  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
396  "--min-xid-age", "9.6");
397  exit(1);
398  }
399 
400  if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
401  {
402  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
403  "--min-mxid-age", "9.6");
404  exit(1);
405  }
406 
407  if (!quiet)
408  {
409  if (stage != ANALYZE_NO_STAGE)
410  printf(_("%s: processing database \"%s\": %s\n"),
411  progname, PQdb(conn), _(stage_messages[stage]));
412  else
413  printf(_("%s: vacuuming database \"%s\"\n"),
414  progname, PQdb(conn));
415  fflush(stdout);
416  }
417 
418  /*
419  * Prepare the list of tables to process by querying the catalogs.
420  *
421  * Since we execute the constructed query with the default search_path
422  * (which could be unsafe), everything in this query MUST be fully
423  * qualified.
424  *
425  * First, build a WITH clause for the catalog query if any tables were
426  * specified, with a set of values made of relation names and their
427  * optional set of columns. This is used to match any provided column
428  * lists with the generated qualified identifiers and to filter for the
429  * tables provided via --table. If a listed table does not exist, the
430  * catalog query will fail.
431  */
432  initPQExpBuffer(&catalog_query);
433  for (cell = tables ? tables->head : NULL; cell; cell = cell->next)
434  {
435  char *just_table;
436  const char *just_columns;
437 
438  /*
439  * Split relation and column names given by the user, this is used to
440  * feed the CTE with values on which are performed pre-run validity
441  * checks as well. For now these happen only on the relation name.
442  */
444  &just_table, &just_columns);
445 
446  if (!tables_listed)
447  {
448  appendPQExpBufferStr(&catalog_query,
449  "WITH listed_tables (table_oid, column_list) "
450  "AS (\n VALUES (");
451  tables_listed = true;
452  }
453  else
454  appendPQExpBufferStr(&catalog_query, ",\n (");
455 
456  appendStringLiteralConn(&catalog_query, just_table, conn);
457  appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
458 
459  if (just_columns && just_columns[0] != '\0')
460  appendStringLiteralConn(&catalog_query, just_columns, conn);
461  else
462  appendPQExpBufferStr(&catalog_query, "NULL");
463 
464  appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
465 
466  pg_free(just_table);
467  }
468 
469  /* Finish formatting the CTE */
470  if (tables_listed)
471  appendPQExpBufferStr(&catalog_query, "\n)\n");
472 
473  appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
474 
475  if (tables_listed)
476  appendPQExpBufferStr(&catalog_query, ", listed_tables.column_list");
477 
478  appendPQExpBufferStr(&catalog_query,
479  " FROM pg_catalog.pg_class c\n"
480  " JOIN pg_catalog.pg_namespace ns"
481  " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
482  " LEFT JOIN pg_catalog.pg_class t"
483  " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
484 
485  /* Used to match the tables listed by the user */
486  if (tables_listed)
487  appendPQExpBufferStr(&catalog_query, " JOIN listed_tables"
488  " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n");
489 
490  /*
491  * If no tables were listed, filter for the relevant relation types. If
492  * tables were given via --table, don't bother filtering by relation type.
493  * Instead, let the server decide whether a given relation can be
494  * processed in which case the user will know about it.
495  */
496  if (!tables_listed)
497  {
498  appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
499  CppAsString2(RELKIND_RELATION) ", "
500  CppAsString2(RELKIND_MATVIEW) "])\n");
501  has_where = true;
502  }
503 
504  /*
505  * For --min-xid-age and --min-mxid-age, the age of the relation is the
506  * greatest of the ages of the main relation and its associated TOAST
507  * table. The commands generated by vacuumdb will also process the TOAST
508  * table for the relation if necessary, so it does not need to be
509  * considered separately.
510  */
511  if (vacopts->min_xid_age != 0)
512  {
513  appendPQExpBuffer(&catalog_query,
514  " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
515  " pg_catalog.age(t.relfrozenxid)) "
516  " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
517  " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
518  " '0'::pg_catalog.xid\n",
519  has_where ? "AND" : "WHERE", vacopts->min_xid_age);
520  has_where = true;
521  }
522 
523  if (vacopts->min_mxid_age != 0)
524  {
525  appendPQExpBuffer(&catalog_query,
526  " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
527  " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
528  " '%d'::pg_catalog.int4\n"
529  " AND c.relminmxid OPERATOR(pg_catalog.!=)"
530  " '0'::pg_catalog.xid\n",
531  has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
532  has_where = true;
533  }
534 
535  /*
536  * Execute the catalog query. We use the default search_path for this
537  * query for consistency with table lookups done elsewhere by the user.
538  */
539  appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
540  executeCommand(conn, "RESET search_path;", echo);
541  res = executeQuery(conn, catalog_query.data, echo);
542  termPQExpBuffer(&catalog_query);
544 
545  /*
546  * If no rows are returned, there are no matching tables, so we are done.
547  */
548  ntups = PQntuples(res);
549  if (ntups == 0)
550  {
551  PQclear(res);
552  PQfinish(conn);
553  return;
554  }
555 
556  /*
557  * Build qualified identifiers for each table, including the column list
558  * if given.
559  */
560  initPQExpBuffer(&buf);
561  for (i = 0; i < ntups; i++)
562  {
564  fmtQualifiedId(PQgetvalue(res, i, 1),
565  PQgetvalue(res, i, 0)));
566 
567  if (tables_listed && !PQgetisnull(res, i, 2))
568  appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
569 
570  simple_string_list_append(&dbtables, buf.data);
571  resetPQExpBuffer(&buf);
572  }
573  termPQExpBuffer(&buf);
574  PQclear(res);
575 
576  /*
577  * If there are more connections than vacuumable relations, we don't need
578  * to use them all.
579  */
580  if (parallel)
581  {
582  if (concurrentCons > ntups)
583  concurrentCons = ntups;
584  if (concurrentCons <= 1)
585  parallel = false;
586  }
587 
588  /*
589  * Setup the database connections. We reuse the connection we already have
590  * for the first slot. If not in parallel mode, the first slot in the
591  * array contains the connection.
592  */
593  if (concurrentCons <= 0)
594  concurrentCons = 1;
595 
596  slots = ParallelSlotsSetup(dbname, host, port, username, prompt_password,
597  progname, echo, conn, concurrentCons);
598 
599  /*
600  * Prepare all the connections to run the appropriate analyze stage, if
601  * caller requested that mode.
602  */
603  if (stage != ANALYZE_NO_STAGE)
604  {
605  int j;
606 
607  /* We already emitted the message above */
608 
609  for (j = 0; j < concurrentCons; j++)
610  executeCommand((slots + j)->connection,
611  stage_commands[stage], echo);
612  }
613 
614  initPQExpBuffer(&sql);
615 
616  cell = dbtables.head;
617  do
618  {
619  const char *tabname = cell->val;
620  ParallelSlot *free_slot;
621 
622  if (CancelRequested)
623  {
624  failed = true;
625  goto finish;
626  }
627 
628  free_slot = ParallelSlotsGetIdle(slots, concurrentCons);
629  if (!free_slot)
630  {
631  failed = true;
632  goto finish;
633  }
634 
636  vacopts, tabname);
637 
638  /*
639  * Execute the vacuum. All errors are handled in processQueryResult
640  * through ParallelSlotsGetIdle.
641  */
642  run_vacuum_command(free_slot->connection, sql.data,
643  echo, tabname);
644 
645  cell = cell->next;
646  } while (cell != NULL);
647 
648  if (!ParallelSlotsWaitCompletion(slots, concurrentCons))
649  failed = true;
650 
651 finish:
652  ParallelSlotsTerminate(slots, concurrentCons);
653  pg_free(slots);
654 
655  termPQExpBuffer(&sql);
656 
657  if (failed)
658  exit(1);
659 }
660 
661 /*
662  * Vacuum/analyze all connectable databases.
663  *
664  * In analyze-in-stages mode, we process all databases in one stage before
665  * moving on to the next stage. That ensure minimal stats are available
666  * quickly everywhere before generating more detailed ones.
667  */
668 static void
670  bool analyze_in_stages,
671  const char *maintenance_db, const char *host,
672  const char *port, const char *username,
673  enum trivalue prompt_password,
674  int concurrentCons,
675  const char *progname, bool echo, bool quiet)
676 {
677  PGconn *conn;
678  PGresult *result;
680  int stage;
681  int i;
682 
683  conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
684  prompt_password, progname, echo);
685  result = executeQuery(conn,
686  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
687  echo);
688  PQfinish(conn);
689 
690  initPQExpBuffer(&connstr);
691  if (analyze_in_stages)
692  {
693  /*
694  * When analyzing all databases in stages, we analyze them all in the
695  * fastest stage first, so that initial statistics become available
696  * for all of them as soon as possible.
697  *
698  * This means we establish several times as many connections, but
699  * that's a secondary consideration.
700  */
701  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
702  {
703  for (i = 0; i < PQntuples(result); i++)
704  {
705  resetPQExpBuffer(&connstr);
706  appendPQExpBufferStr(&connstr, "dbname=");
707  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
708 
709  vacuum_one_database(connstr.data, vacopts,
710  stage,
711  NULL,
712  host, port, username, prompt_password,
713  concurrentCons,
714  progname, echo, quiet);
715  }
716  }
717  }
718  else
719  {
720  for (i = 0; i < PQntuples(result); i++)
721  {
722  resetPQExpBuffer(&connstr);
723  appendPQExpBufferStr(&connstr, "dbname=");
724  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
725 
726  vacuum_one_database(connstr.data, vacopts,
728  NULL,
729  host, port, username, prompt_password,
730  concurrentCons,
731  progname, echo, quiet);
732  }
733  }
734  termPQExpBuffer(&connstr);
735 
736  PQclear(result);
737 }
738 
739 /*
740  * Construct a vacuum/analyze command to run based on the given options, in the
741  * given string buffer, which may contain previous garbage.
742  *
743  * The table name used must be already properly quoted. The command generated
744  * depends on the server version involved and it is semicolon-terminated.
745  */
746 static void
747 prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
748  vacuumingOptions *vacopts, const char *table)
749 {
750  const char *paren = " (";
751  const char *comma = ", ";
752  const char *sep = paren;
753 
754  resetPQExpBuffer(sql);
755 
756  if (vacopts->analyze_only)
757  {
758  appendPQExpBufferStr(sql, "ANALYZE");
759 
760  /* parenthesized grammar of ANALYZE is supported since v11 */
761  if (serverVersion >= 110000)
762  {
763  if (vacopts->skip_locked)
764  {
765  /* SKIP_LOCKED is supported since v12 */
766  Assert(serverVersion >= 120000);
767  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
768  sep = comma;
769  }
770  if (vacopts->verbose)
771  {
772  appendPQExpBuffer(sql, "%sVERBOSE", sep);
773  sep = comma;
774  }
775  if (sep != paren)
776  appendPQExpBufferChar(sql, ')');
777  }
778  else
779  {
780  if (vacopts->verbose)
781  appendPQExpBufferStr(sql, " VERBOSE");
782  }
783  }
784  else
785  {
786  appendPQExpBufferStr(sql, "VACUUM");
787 
788  /* parenthesized grammar of VACUUM is supported since v9.0 */
789  if (serverVersion >= 90000)
790  {
791  if (vacopts->disable_page_skipping)
792  {
793  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
794  Assert(serverVersion >= 90600);
795  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
796  sep = comma;
797  }
798  if (vacopts->skip_locked)
799  {
800  /* SKIP_LOCKED is supported since v12 */
801  Assert(serverVersion >= 120000);
802  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
803  sep = comma;
804  }
805  if (vacopts->full)
806  {
807  appendPQExpBuffer(sql, "%sFULL", sep);
808  sep = comma;
809  }
810  if (vacopts->freeze)
811  {
812  appendPQExpBuffer(sql, "%sFREEZE", sep);
813  sep = comma;
814  }
815  if (vacopts->verbose)
816  {
817  appendPQExpBuffer(sql, "%sVERBOSE", sep);
818  sep = comma;
819  }
820  if (vacopts->and_analyze)
821  {
822  appendPQExpBuffer(sql, "%sANALYZE", sep);
823  sep = comma;
824  }
825  if (sep != paren)
826  appendPQExpBufferChar(sql, ')');
827  }
828  else
829  {
830  if (vacopts->full)
831  appendPQExpBufferStr(sql, " FULL");
832  if (vacopts->freeze)
833  appendPQExpBufferStr(sql, " FREEZE");
834  if (vacopts->verbose)
835  appendPQExpBufferStr(sql, " VERBOSE");
836  if (vacopts->and_analyze)
837  appendPQExpBufferStr(sql, " ANALYZE");
838  }
839  }
840 
841  appendPQExpBuffer(sql, " %s;", table);
842 }
843 
844 /*
845  * Send a vacuum/analyze command to the server, returning after sending the
846  * command.
847  *
848  * Any errors during command execution are reported to stderr.
849  */
850 static void
851 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
852  const char *table)
853 {
854  bool status;
855 
856  if (echo)
857  printf("%s\n", sql);
858 
859  status = PQsendQuery(conn, sql) == 1;
860 
861  if (!status)
862  {
863  if (table)
864  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
865  table, PQdb(conn), PQerrorMessage(conn));
866  else
867  pg_log_error("vacuuming of database \"%s\" failed: %s",
868  PQdb(conn), PQerrorMessage(conn));
869  }
870 }
871 
872 static void
873 help(const char *progname)
874 {
875  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
876  printf(_("Usage:\n"));
877  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
878  printf(_("\nOptions:\n"));
879  printf(_(" -a, --all vacuum all databases\n"));
880  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
881  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
882  printf(_(" -e, --echo show the commands being sent to the server\n"));
883  printf(_(" -f, --full do full vacuuming\n"));
884  printf(_(" -F, --freeze freeze row transaction information\n"));
885  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
886  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
887  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
888  printf(_(" -q, --quiet don't write any messages\n"));
889  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
890  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
891  printf(_(" -v, --verbose write a lot of output\n"));
892  printf(_(" -V, --version output version information, then exit\n"));
893  printf(_(" -z, --analyze update optimizer statistics\n"));
894  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
895  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
896  " stages for faster results; no vacuum\n"));
897  printf(_(" -?, --help show this help, then exit\n"));
898  printf(_("\nConnection options:\n"));
899  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
900  printf(_(" -p, --port=PORT database server port\n"));
901  printf(_(" -U, --username=USERNAME user name to connect as\n"));
902  printf(_(" -w, --no-password never prompt for password\n"));
903  printf(_(" -W, --password force password prompt\n"));
904  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
905  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
906  printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
907 }
PGconn * connectMaintenanceDatabase(const char *maintenance_db, const char *pghost, const char *pgport, const char *pguser, enum trivalue prompt_password, const char *progname, bool echo)
Definition: common.c:160
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1879
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6596
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3164
bool skip_locked
Definition: vacuumdb.c:34
static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion, vacuumingOptions *vacopts, const char *table)
Definition: vacuumdb.c:747
const char * get_progname(const char *argv0)
Definition: path.c:453
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
#define pg_log_error(...)
Definition: logging.h:79
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:57
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
void pg_logging_init(const char *argv0)
Definition: logging.c:39
struct vacuumingOptions vacuumingOptions
#define gettext_noop(x)
Definition: c.h:1117
void appendConnStrVal(PQExpBuffer buf, const char *str)
Definition: string_utils.c:547
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4098
#define printf(...)
Definition: port.h:198
static void setup_cancel_handler(void)
Definition: parallel.c:618
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:65
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6586
const char * progname
Definition: pg_standby.c:37
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2770
#define fprintf
Definition: port.h:196
int PQclientEncoding(const PGconn *conn)
Definition: fe-connect.c:6656
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)
Definition: vacuumdb.c:338
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1235
#define required_argument
Definition: getopt_long.h:25
int optind
Definition: getopt.c:50
PGconn * conn
Definition: streamutil.c:56
int main(int argc, char *argv[])
Definition: vacuumdb.c:70
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
static void executeCommand(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1902
bool ParallelSlotsWaitCompletion(ParallelSlot *slots, int numslots)
char * c
static char * buf
Definition: pg_test_fsync.c:68
struct SimpleStringListCell * next
Definition: simple_list.h:34
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
bool and_analyze
Definition: vacuumdb.c:30
PGconn * connection
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table)
Definition: vacuumdb.c:851
void ParallelSlotsTerminate(ParallelSlot *slots, int numslots)
static int port
Definition: pg_regress.c:92
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
Definition: common.c:40
ParallelSlot * ParallelSlotsGetIdle(ParallelSlot *slots, int numslots)
trivalue
Definition: vacuumlo.c:34
static void help(const char *progname)
Definition: vacuumdb.c:873
#define CppAsString2(x)
Definition: c.h:224
#define no_argument
Definition: getopt_long.h:24
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1135
static void vacuum_all_databases(vacuumingOptions *vacopts, bool analyze_in_stages, const char *maintenance_db, const char *host, const char *port, const char *username, enum trivalue prompt_password, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:669
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
ParallelSlot * ParallelSlotsSetup(const char *dbname, const char *host, const char *port, const char *username, bool prompt_password, const char *progname, bool echo, PGconn *conn, int numslots)
static char * username
Definition: initdb.c:133
void PQclear(PGresult *res)
Definition: fe-exec.c:695
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6446
#define Assert(condition)
Definition: c.h:732
static PGconn * connectDatabase(const char *dbname, const char *connstr, const char *pghost, const char *pgport, const char *pguser, trivalue prompt_password, bool fail_on_error)
Definition: pg_dumpall.c:1635
SimpleStringListCell * head
Definition: simple_list.h:42
char * dbname
Definition: streamutil.c:52
const char * fmtQualifiedId(const char *schema, const char *id)
Definition: string_utils.c:147
void pg_free(void *ptr)
Definition: fe_memutils.c:105
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:66
bool disable_page_skipping
Definition: vacuumdb.c:33
bool CancelRequested
Definition: common.c:29
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
Definition: string_utils.c:295
bool analyze_only
Definition: vacuumdb.c:28
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:565
void splitTableColumnsSpec(const char *spec, int encoding, char **table, const char **columns)
Definition: common.c:340
char * optarg
Definition: getopt.c:52
int i
char val[FLEXIBLE_ARRAY_MEMBER]
Definition: simple_list.h:37
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:227
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3189
#define _(x)
Definition: elog.c:84
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
static char * connstr
Definition: pg_dumpall.c:62
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74