PostgreSQL Source Code  git master
vacuumdb.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * vacuumdb
4  *
5  * Portions Copyright (c) 1996-2022, 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 <limits.h>
16 
17 #include "catalog/pg_class_d.h"
18 #include "common.h"
19 #include "common/connect.h"
20 #include "common/logging.h"
21 #include "fe_utils/cancel.h"
22 #include "fe_utils/option_utils.h"
23 #include "fe_utils/parallel_slot.h"
24 #include "fe_utils/query_utils.h"
25 #include "fe_utils/simple_list.h"
26 #include "fe_utils/string_utils.h"
27 
28 
29 /* vacuum options controlled by user flags */
30 typedef struct vacuumingOptions
31 {
33  bool verbose;
35  bool full;
36  bool freeze;
41  int parallel_workers; /* >= 0 indicates user specified the
42  * parallel degree, otherwise -1 */
48 
49 /* object filter options */
50 typedef enum
51 {
52  OBJFILTER_NONE = 0, /* no filter used */
53  OBJFILTER_ALL_DBS = (1 << 0), /* -a | --all */
54  OBJFILTER_DATABASE = (1 << 1), /* -d | --dbname */
55  OBJFILTER_TABLE = (1 << 2), /* -t | --table */
56  OBJFILTER_SCHEMA = (1 << 3), /* -n | --schema */
57  OBJFILTER_SCHEMA_EXCLUDE = (1 << 4) /* -N | --exclude-schema */
59 
61 
62 static void vacuum_one_database(ConnParams *cparams,
63  vacuumingOptions *vacopts,
64  int stage,
65  SimpleStringList *objects,
66  int concurrentCons,
67  const char *progname, bool echo, bool quiet);
68 
69 static void vacuum_all_databases(ConnParams *cparams,
70  vacuumingOptions *vacopts,
71  bool analyze_in_stages,
72  int concurrentCons,
73  const char *progname, bool echo, bool quiet);
74 
75 static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
76  vacuumingOptions *vacopts, const char *table);
77 
78 static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
79  const char *table);
80 
81 static void help(const char *progname);
82 
83 void check_objfilter(void);
84 
85 /* For analyze-in-stages mode */
86 #define ANALYZE_NO_STAGE -1
87 #define ANALYZE_NUM_STAGES 3
88 
89 
90 int
91 main(int argc, char *argv[])
92 {
93  static struct option long_options[] = {
94  {"host", required_argument, NULL, 'h'},
95  {"port", required_argument, NULL, 'p'},
96  {"username", required_argument, NULL, 'U'},
97  {"no-password", no_argument, NULL, 'w'},
98  {"password", no_argument, NULL, 'W'},
99  {"echo", no_argument, NULL, 'e'},
100  {"quiet", no_argument, NULL, 'q'},
101  {"dbname", required_argument, NULL, 'd'},
102  {"analyze", no_argument, NULL, 'z'},
103  {"analyze-only", no_argument, NULL, 'Z'},
104  {"freeze", no_argument, NULL, 'F'},
105  {"all", no_argument, NULL, 'a'},
106  {"table", required_argument, NULL, 't'},
107  {"full", no_argument, NULL, 'f'},
108  {"verbose", no_argument, NULL, 'v'},
109  {"jobs", required_argument, NULL, 'j'},
110  {"parallel", required_argument, NULL, 'P'},
111  {"schema", required_argument, NULL, 'n'},
112  {"exclude-schema", required_argument, NULL, 'N'},
113  {"maintenance-db", required_argument, NULL, 2},
114  {"analyze-in-stages", no_argument, NULL, 3},
115  {"disable-page-skipping", no_argument, NULL, 4},
116  {"skip-locked", no_argument, NULL, 5},
117  {"min-xid-age", required_argument, NULL, 6},
118  {"min-mxid-age", required_argument, NULL, 7},
119  {"no-index-cleanup", no_argument, NULL, 8},
120  {"force-index-cleanup", no_argument, NULL, 9},
121  {"no-truncate", no_argument, NULL, 10},
122  {"no-process-toast", no_argument, NULL, 11},
123  {NULL, 0, NULL, 0}
124  };
125 
126  const char *progname;
127  int optindex;
128  int c;
129  const char *dbname = NULL;
130  const char *maintenance_db = NULL;
131  char *host = NULL;
132  char *port = NULL;
133  char *username = NULL;
134  enum trivalue prompt_password = TRI_DEFAULT;
135  ConnParams cparams;
136  bool echo = false;
137  bool quiet = false;
138  vacuumingOptions vacopts;
139  bool analyze_in_stages = false;
140  SimpleStringList objects = {NULL, NULL};
141  int concurrentCons = 1;
142  int tbl_count = 0;
143 
144  /* initialize options */
145  memset(&vacopts, 0, sizeof(vacopts));
146  vacopts.parallel_workers = -1;
147  vacopts.no_index_cleanup = false;
148  vacopts.force_index_cleanup = false;
149  vacopts.do_truncate = true;
150  vacopts.process_toast = true;
151 
152  pg_logging_init(argv[0]);
153  progname = get_progname(argv[0]);
154  set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
155 
156  handle_help_version_opts(argc, argv, "vacuumdb", help);
157 
158  while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:P:n:N:", long_options, &optindex)) != -1)
159  {
160  switch (c)
161  {
162  case 'h':
163  host = pg_strdup(optarg);
164  break;
165  case 'p':
166  port = pg_strdup(optarg);
167  break;
168  case 'U':
170  break;
171  case 'w':
172  prompt_password = TRI_NO;
173  break;
174  case 'W':
175  prompt_password = TRI_YES;
176  break;
177  case 'e':
178  echo = true;
179  break;
180  case 'q':
181  quiet = true;
182  break;
183  case 'd':
186  break;
187  case 'z':
188  vacopts.and_analyze = true;
189  break;
190  case 'Z':
191  vacopts.analyze_only = true;
192  break;
193  case 'F':
194  vacopts.freeze = true;
195  break;
196  case 'a':
198  break;
199  case 't':
200  {
203  tbl_count++;
204  break;
205  }
206  case 'f':
207  vacopts.full = true;
208  break;
209  case 'v':
210  vacopts.verbose = true;
211  break;
212  case 'j':
213  if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
214  &concurrentCons))
215  exit(1);
216  break;
217  case 'P':
218  if (!option_parse_int(optarg, "-P/--parallel", 0, INT_MAX,
219  &vacopts.parallel_workers))
220  exit(1);
221  break;
222  case 'n':
223  {
226  break;
227  }
228  case 'N':
229  {
232  break;
233  }
234  case 2:
235  maintenance_db = pg_strdup(optarg);
236  break;
237  case 3:
238  analyze_in_stages = vacopts.analyze_only = true;
239  break;
240  case 4:
241  vacopts.disable_page_skipping = true;
242  break;
243  case 5:
244  vacopts.skip_locked = true;
245  break;
246  case 6:
247  if (!option_parse_int(optarg, "--min-xid-age", 1, INT_MAX,
248  &vacopts.min_xid_age))
249  exit(1);
250  break;
251  case 7:
252  if (!option_parse_int(optarg, "--min-mxid-age", 1, INT_MAX,
253  &vacopts.min_mxid_age))
254  exit(1);
255  break;
256  case 8:
257  vacopts.no_index_cleanup = true;
258  break;
259  case 9:
260  vacopts.force_index_cleanup = true;
261  break;
262  case 10:
263  vacopts.do_truncate = false;
264  break;
265  case 11:
266  vacopts.process_toast = false;
267  break;
268  default:
269  /* getopt_long already emitted a complaint */
270  pg_log_error_hint("Try \"%s --help\" for more information.", progname);
271  exit(1);
272  }
273  }
274 
275  /*
276  * Non-option argument specifies database name as long as it wasn't
277  * already specified with -d / --dbname
278  */
279  if (optind < argc && dbname == NULL)
280  {
282  dbname = argv[optind];
283  optind++;
284  }
285 
286  if (optind < argc)
287  {
288  pg_log_error("too many command-line arguments (first is \"%s\")",
289  argv[optind]);
290  pg_log_error_hint("Try \"%s --help\" for more information.", progname);
291  exit(1);
292  }
293 
294  /*
295  * Validate the combination of filters specified in the command-line
296  * options.
297  */
298  check_objfilter();
299 
300  if (vacopts.analyze_only)
301  {
302  if (vacopts.full)
303  pg_fatal("cannot use the \"%s\" option when performing only analyze",
304  "full");
305  if (vacopts.freeze)
306  pg_fatal("cannot use the \"%s\" option when performing only analyze",
307  "freeze");
308  if (vacopts.disable_page_skipping)
309  pg_fatal("cannot use the \"%s\" option when performing only analyze",
310  "disable-page-skipping");
311  if (vacopts.no_index_cleanup)
312  pg_fatal("cannot use the \"%s\" option when performing only analyze",
313  "no-index-cleanup");
314  if (vacopts.force_index_cleanup)
315  pg_fatal("cannot use the \"%s\" option when performing only analyze",
316  "force-index-cleanup");
317  if (!vacopts.do_truncate)
318  pg_fatal("cannot use the \"%s\" option when performing only analyze",
319  "no-truncate");
320  if (!vacopts.process_toast)
321  pg_fatal("cannot use the \"%s\" option when performing only analyze",
322  "no-process-toast");
323  /* allow 'and_analyze' with 'analyze_only' */
324  }
325 
326  /* Prohibit full and analyze_only options with parallel option */
327  if (vacopts.parallel_workers >= 0)
328  {
329  if (vacopts.analyze_only)
330  pg_fatal("cannot use the \"%s\" option when performing only analyze",
331  "parallel");
332  if (vacopts.full)
333  pg_fatal("cannot use the \"%s\" option when performing full vacuum",
334  "parallel");
335  }
336 
337  /* Prohibit --no-index-cleanup and --force-index-cleanup together */
338  if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
339  pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
340  "no-index-cleanup", "force-index-cleanup");
341 
342  /* fill cparams except for dbname, which is set below */
343  cparams.pghost = host;
344  cparams.pgport = port;
345  cparams.pguser = username;
346  cparams.prompt_password = prompt_password;
347  cparams.override_dbname = NULL;
348 
349  setup_cancel_handler(NULL);
350 
351  /* Avoid opening extra connections. */
352  if (tbl_count && (concurrentCons > tbl_count))
353  concurrentCons = tbl_count;
354 
356  {
357  cparams.dbname = maintenance_db;
358 
359  vacuum_all_databases(&cparams, &vacopts,
360  analyze_in_stages,
361  concurrentCons,
362  progname, echo, quiet);
363  }
364  else
365  {
366  if (dbname == NULL)
367  {
368  if (getenv("PGDATABASE"))
369  dbname = getenv("PGDATABASE");
370  else if (getenv("PGUSER"))
371  dbname = getenv("PGUSER");
372  else
374  }
375 
376  cparams.dbname = dbname;
377 
378  if (analyze_in_stages)
379  {
380  int stage;
381 
382  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
383  {
384  vacuum_one_database(&cparams, &vacopts,
385  stage,
386  &objects,
387  concurrentCons,
388  progname, echo, quiet);
389  }
390  }
391  else
392  vacuum_one_database(&cparams, &vacopts,
394  &objects,
395  concurrentCons,
396  progname, echo, quiet);
397  }
398 
399  exit(0);
400 }
401 
402 /*
403  * Verify that the filters used at command line are compatible.
404  */
405 void
407 {
408  if ((objfilter & OBJFILTER_ALL_DBS) &&
410  pg_fatal("cannot vacuum all databases and a specific one at the same time");
411 
412  if ((objfilter & OBJFILTER_ALL_DBS) &&
414  pg_fatal("cannot vacuum specific table(s) in all databases");
415 
416  if ((objfilter & OBJFILTER_ALL_DBS) &&
418  pg_fatal("cannot vacuum specific schema(s) in all databases");
419 
420  if ((objfilter & OBJFILTER_ALL_DBS) &&
422  pg_fatal("cannot exclude specific schema(s) in all databases");
423 
424  if ((objfilter & OBJFILTER_TABLE) &&
426  pg_fatal("cannot vacuum all tables in schema(s) and specific table(s) at the same time");
427 
428  if ((objfilter & OBJFILTER_TABLE) &&
430  pg_fatal("cannot vacuum specific table(s) and exclude schema(s) at the same time");
431 
432  if ((objfilter & OBJFILTER_SCHEMA) &&
434  pg_fatal("cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
435 }
436 
437 /*
438  * vacuum_one_database
439  *
440  * Process tables in the given database. If the 'tables' list is empty,
441  * process all tables in the database.
442  *
443  * Note that this function is only concerned with running exactly one stage
444  * when in analyze-in-stages mode; caller must iterate on us if necessary.
445  *
446  * If concurrentCons is > 1, multiple connections are used to vacuum tables
447  * in parallel. In this case and if the table list is empty, we first obtain
448  * a list of tables from the database.
449  */
450 static void
452  vacuumingOptions *vacopts,
453  int stage,
454  SimpleStringList *objects,
455  int concurrentCons,
456  const char *progname, bool echo, bool quiet)
457 {
458  PQExpBufferData sql;
460  PQExpBufferData catalog_query;
461  PGresult *res;
462  PGconn *conn;
463  SimpleStringListCell *cell;
465  SimpleStringList dbtables = {NULL, NULL};
466  int i;
467  int ntups;
468  bool failed = false;
469  bool objects_listed = false;
470  bool has_where = false;
471  const char *initcmd;
472  const char *stage_commands[] = {
473  "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
474  "SET default_statistics_target=10; RESET vacuum_cost_delay;",
475  "RESET default_statistics_target;"
476  };
477  const char *stage_messages[] = {
478  gettext_noop("Generating minimal optimizer statistics (1 target)"),
479  gettext_noop("Generating medium optimizer statistics (10 targets)"),
480  gettext_noop("Generating default (full) optimizer statistics")
481  };
482 
483  Assert(stage == ANALYZE_NO_STAGE ||
484  (stage >= 0 && stage < ANALYZE_NUM_STAGES));
485 
486  conn = connectDatabase(cparams, progname, echo, false, true);
487 
488  if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
489  {
490  PQfinish(conn);
491  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
492  "disable-page-skipping", "9.6");
493  }
494 
495  if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
496  {
497  PQfinish(conn);
498  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
499  "no-index-cleanup", "12");
500  }
501 
502  if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
503  {
504  PQfinish(conn);
505  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
506  "force-index-cleanup", "12");
507  }
508 
509  if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
510  {
511  PQfinish(conn);
512  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
513  "no-truncate", "12");
514  }
515 
516  if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
517  {
518  PQfinish(conn);
519  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
520  "no-process-toast", "14");
521  }
522 
523  if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
524  {
525  PQfinish(conn);
526  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
527  "skip-locked", "12");
528  }
529 
530  if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
531  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
532  "--min-xid-age", "9.6");
533 
534  if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
535  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
536  "--min-mxid-age", "9.6");
537 
538  if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
539  pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
540  "--parallel", "13");
541 
542  if (!quiet)
543  {
544  if (stage != ANALYZE_NO_STAGE)
545  printf(_("%s: processing database \"%s\": %s\n"),
546  progname, PQdb(conn), _(stage_messages[stage]));
547  else
548  printf(_("%s: vacuuming database \"%s\"\n"),
549  progname, PQdb(conn));
550  fflush(stdout);
551  }
552 
553  /*
554  * Prepare the list of tables to process by querying the catalogs.
555  *
556  * Since we execute the constructed query with the default search_path
557  * (which could be unsafe), everything in this query MUST be fully
558  * qualified.
559  *
560  * First, build a WITH clause for the catalog query if any tables were
561  * specified, with a set of values made of relation names and their
562  * optional set of columns. This is used to match any provided column
563  * lists with the generated qualified identifiers and to filter for the
564  * tables provided via --table. If a listed table does not exist, the
565  * catalog query will fail.
566  */
567  initPQExpBuffer(&catalog_query);
568  for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
569  {
570  char *just_table = NULL;
571  const char *just_columns = NULL;
572 
573  if (!objects_listed)
574  {
575  appendPQExpBufferStr(&catalog_query,
576  "WITH listed_objects (object_oid, column_list) "
577  "AS (\n VALUES (");
578  objects_listed = true;
579  }
580  else
581  appendPQExpBufferStr(&catalog_query, ",\n (");
582 
584  {
585  appendStringLiteralConn(&catalog_query, cell->val, conn);
586  appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace, ");
587  }
588 
590  {
591  /*
592  * Split relation and column names given by the user, this is used
593  * to feed the CTE with values on which are performed pre-run
594  * validity checks as well. For now these happen only on the
595  * relation name.
596  */
598  &just_table, &just_columns);
599 
600  appendStringLiteralConn(&catalog_query, just_table, conn);
601  appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
602  }
603 
604  if (just_columns && just_columns[0] != '\0')
605  appendStringLiteralConn(&catalog_query, just_columns, conn);
606  else
607  appendPQExpBufferStr(&catalog_query, "NULL");
608 
609  appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
610 
611  pg_free(just_table);
612  }
613 
614  /* Finish formatting the CTE */
615  if (objects_listed)
616  appendPQExpBufferStr(&catalog_query, "\n)\n");
617 
618  appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
619 
620  if (objects_listed)
621  appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
622 
623  appendPQExpBufferStr(&catalog_query,
624  " FROM pg_catalog.pg_class c\n"
625  " JOIN pg_catalog.pg_namespace ns"
626  " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
627  " LEFT JOIN pg_catalog.pg_class t"
628  " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
629 
630  /* Used to match the tables or schemas listed by the user */
631  if (objects_listed)
632  {
633  appendPQExpBufferStr(&catalog_query, " JOIN listed_objects"
634  " ON listed_objects.object_oid ");
635 
637  appendPQExpBufferStr(&catalog_query, "OPERATOR(pg_catalog.!=) ");
638  else
639  appendPQExpBufferStr(&catalog_query, "OPERATOR(pg_catalog.=) ");
640 
642  appendPQExpBufferStr(&catalog_query, "c.oid\n");
643  else
644  appendPQExpBufferStr(&catalog_query, "ns.oid\n");
645  }
646 
647  /*
648  * If no tables were listed, filter for the relevant relation types. If
649  * tables were given via --table, don't bother filtering by relation type.
650  * Instead, let the server decide whether a given relation can be
651  * processed in which case the user will know about it.
652  */
653  if ((objfilter & OBJFILTER_TABLE) == 0)
654  {
655  appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
656  CppAsString2(RELKIND_RELATION) ", "
657  CppAsString2(RELKIND_MATVIEW) "])\n");
658  has_where = true;
659  }
660 
661  /*
662  * For --min-xid-age and --min-mxid-age, the age of the relation is the
663  * greatest of the ages of the main relation and its associated TOAST
664  * table. The commands generated by vacuumdb will also process the TOAST
665  * table for the relation if necessary, so it does not need to be
666  * considered separately.
667  */
668  if (vacopts->min_xid_age != 0)
669  {
670  appendPQExpBuffer(&catalog_query,
671  " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
672  " pg_catalog.age(t.relfrozenxid)) "
673  " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
674  " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
675  " '0'::pg_catalog.xid\n",
676  has_where ? "AND" : "WHERE", vacopts->min_xid_age);
677  has_where = true;
678  }
679 
680  if (vacopts->min_mxid_age != 0)
681  {
682  appendPQExpBuffer(&catalog_query,
683  " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
684  " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
685  " '%d'::pg_catalog.int4\n"
686  " AND c.relminmxid OPERATOR(pg_catalog.!=)"
687  " '0'::pg_catalog.xid\n",
688  has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
689  has_where = true;
690  }
691 
692  /*
693  * Execute the catalog query. We use the default search_path for this
694  * query for consistency with table lookups done elsewhere by the user.
695  */
696  appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
697  executeCommand(conn, "RESET search_path;", echo);
698  res = executeQuery(conn, catalog_query.data, echo);
699  termPQExpBuffer(&catalog_query);
701 
702  /*
703  * If no rows are returned, there are no matching tables, so we are done.
704  */
705  ntups = PQntuples(res);
706  if (ntups == 0)
707  {
708  PQclear(res);
709  PQfinish(conn);
710  return;
711  }
712 
713  /*
714  * Build qualified identifiers for each table, including the column list
715  * if given.
716  */
718  for (i = 0; i < ntups; i++)
719  {
722  PQgetvalue(res, i, 0)));
723 
724  if (objects_listed && !PQgetisnull(res, i, 2))
726 
727  simple_string_list_append(&dbtables, buf.data);
729  }
731  PQclear(res);
732 
733  /*
734  * Ensure concurrentCons is sane. If there are more connections than
735  * vacuumable relations, we don't need to use them all.
736  */
737  if (concurrentCons > ntups)
738  concurrentCons = ntups;
739  if (concurrentCons <= 0)
740  concurrentCons = 1;
741 
742  /*
743  * All slots need to be prepared to run the appropriate analyze stage, if
744  * caller requested that mode. We have to prepare the initial connection
745  * ourselves before setting up the slots.
746  */
747  if (stage == ANALYZE_NO_STAGE)
748  initcmd = NULL;
749  else
750  {
751  initcmd = stage_commands[stage];
752  executeCommand(conn, initcmd, echo);
753  }
754 
755  /*
756  * Setup the database connections. We reuse the connection we already have
757  * for the first slot. If not in parallel mode, the first slot in the
758  * array contains the connection.
759  */
760  sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
762 
763  initPQExpBuffer(&sql);
764 
765  cell = dbtables.head;
766  do
767  {
768  const char *tabname = cell->val;
769  ParallelSlot *free_slot;
770 
771  if (CancelRequested)
772  {
773  failed = true;
774  goto finish;
775  }
776 
777  free_slot = ParallelSlotsGetIdle(sa, NULL);
778  if (!free_slot)
779  {
780  failed = true;
781  goto finish;
782  }
783 
785  vacopts, tabname);
786 
787  /*
788  * Execute the vacuum. All errors are handled in processQueryResult
789  * through ParallelSlotsGetIdle.
790  */
792  run_vacuum_command(free_slot->connection, sql.data,
793  echo, tabname);
794 
795  cell = cell->next;
796  } while (cell != NULL);
797 
799  failed = true;
800 
801 finish:
803  pg_free(sa);
804 
805  termPQExpBuffer(&sql);
806 
807  if (failed)
808  exit(1);
809 }
810 
811 /*
812  * Vacuum/analyze all connectable databases.
813  *
814  * In analyze-in-stages mode, we process all databases in one stage before
815  * moving on to the next stage. That ensure minimal stats are available
816  * quickly everywhere before generating more detailed ones.
817  */
818 static void
820  vacuumingOptions *vacopts,
821  bool analyze_in_stages,
822  int concurrentCons,
823  const char *progname, bool echo, bool quiet)
824 {
825  PGconn *conn;
826  PGresult *result;
827  int stage;
828  int i;
829 
830  conn = connectMaintenanceDatabase(cparams, progname, echo);
831  result = executeQuery(conn,
832  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
833  echo);
834  PQfinish(conn);
835 
836  if (analyze_in_stages)
837  {
838  /*
839  * When analyzing all databases in stages, we analyze them all in the
840  * fastest stage first, so that initial statistics become available
841  * for all of them as soon as possible.
842  *
843  * This means we establish several times as many connections, but
844  * that's a secondary consideration.
845  */
846  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
847  {
848  for (i = 0; i < PQntuples(result); i++)
849  {
850  cparams->override_dbname = PQgetvalue(result, i, 0);
851 
852  vacuum_one_database(cparams, vacopts,
853  stage,
854  NULL,
855  concurrentCons,
856  progname, echo, quiet);
857  }
858  }
859  }
860  else
861  {
862  for (i = 0; i < PQntuples(result); i++)
863  {
864  cparams->override_dbname = PQgetvalue(result, i, 0);
865 
866  vacuum_one_database(cparams, vacopts,
868  NULL,
869  concurrentCons,
870  progname, echo, quiet);
871  }
872  }
873 
874  PQclear(result);
875 }
876 
877 /*
878  * Construct a vacuum/analyze command to run based on the given options, in the
879  * given string buffer, which may contain previous garbage.
880  *
881  * The table name used must be already properly quoted. The command generated
882  * depends on the server version involved and it is semicolon-terminated.
883  */
884 static void
885 prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
886  vacuumingOptions *vacopts, const char *table)
887 {
888  const char *paren = " (";
889  const char *comma = ", ";
890  const char *sep = paren;
891 
892  resetPQExpBuffer(sql);
893 
894  if (vacopts->analyze_only)
895  {
896  appendPQExpBufferStr(sql, "ANALYZE");
897 
898  /* parenthesized grammar of ANALYZE is supported since v11 */
899  if (serverVersion >= 110000)
900  {
901  if (vacopts->skip_locked)
902  {
903  /* SKIP_LOCKED is supported since v12 */
904  Assert(serverVersion >= 120000);
905  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
906  sep = comma;
907  }
908  if (vacopts->verbose)
909  {
910  appendPQExpBuffer(sql, "%sVERBOSE", sep);
911  sep = comma;
912  }
913  if (sep != paren)
914  appendPQExpBufferChar(sql, ')');
915  }
916  else
917  {
918  if (vacopts->verbose)
919  appendPQExpBufferStr(sql, " VERBOSE");
920  }
921  }
922  else
923  {
924  appendPQExpBufferStr(sql, "VACUUM");
925 
926  /* parenthesized grammar of VACUUM is supported since v9.0 */
927  if (serverVersion >= 90000)
928  {
929  if (vacopts->disable_page_skipping)
930  {
931  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
932  Assert(serverVersion >= 90600);
933  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
934  sep = comma;
935  }
936  if (vacopts->no_index_cleanup)
937  {
938  /* "INDEX_CLEANUP FALSE" has been supported since v12 */
939  Assert(serverVersion >= 120000);
940  Assert(!vacopts->force_index_cleanup);
941  appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
942  sep = comma;
943  }
944  if (vacopts->force_index_cleanup)
945  {
946  /* "INDEX_CLEANUP TRUE" has been supported since v12 */
947  Assert(serverVersion >= 120000);
948  Assert(!vacopts->no_index_cleanup);
949  appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
950  sep = comma;
951  }
952  if (!vacopts->do_truncate)
953  {
954  /* TRUNCATE is supported since v12 */
955  Assert(serverVersion >= 120000);
956  appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
957  sep = comma;
958  }
959  if (!vacopts->process_toast)
960  {
961  /* PROCESS_TOAST is supported since v14 */
962  Assert(serverVersion >= 140000);
963  appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
964  sep = comma;
965  }
966  if (vacopts->skip_locked)
967  {
968  /* SKIP_LOCKED is supported since v12 */
969  Assert(serverVersion >= 120000);
970  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
971  sep = comma;
972  }
973  if (vacopts->full)
974  {
975  appendPQExpBuffer(sql, "%sFULL", sep);
976  sep = comma;
977  }
978  if (vacopts->freeze)
979  {
980  appendPQExpBuffer(sql, "%sFREEZE", sep);
981  sep = comma;
982  }
983  if (vacopts->verbose)
984  {
985  appendPQExpBuffer(sql, "%sVERBOSE", sep);
986  sep = comma;
987  }
988  if (vacopts->and_analyze)
989  {
990  appendPQExpBuffer(sql, "%sANALYZE", sep);
991  sep = comma;
992  }
993  if (vacopts->parallel_workers >= 0)
994  {
995  /* PARALLEL is supported since v13 */
996  Assert(serverVersion >= 130000);
997  appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
998  vacopts->parallel_workers);
999  sep = comma;
1000  }
1001  if (sep != paren)
1002  appendPQExpBufferChar(sql, ')');
1003  }
1004  else
1005  {
1006  if (vacopts->full)
1007  appendPQExpBufferStr(sql, " FULL");
1008  if (vacopts->freeze)
1009  appendPQExpBufferStr(sql, " FREEZE");
1010  if (vacopts->verbose)
1011  appendPQExpBufferStr(sql, " VERBOSE");
1012  if (vacopts->and_analyze)
1013  appendPQExpBufferStr(sql, " ANALYZE");
1014  }
1015  }
1016 
1017  appendPQExpBuffer(sql, " %s;", table);
1018 }
1019 
1020 /*
1021  * Send a vacuum/analyze command to the server, returning after sending the
1022  * command.
1023  *
1024  * Any errors during command execution are reported to stderr.
1025  */
1026 static void
1027 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
1028  const char *table)
1029 {
1030  bool status;
1031 
1032  if (echo)
1033  printf("%s\n", sql);
1034 
1035  status = PQsendQuery(conn, sql) == 1;
1036 
1037  if (!status)
1038  {
1039  if (table)
1040  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1041  table, PQdb(conn), PQerrorMessage(conn));
1042  else
1043  pg_log_error("vacuuming of database \"%s\" failed: %s",
1045  }
1046 }
1047 
1048 static void
1049 help(const char *progname)
1050 {
1051  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1052  printf(_("Usage:\n"));
1053  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1054  printf(_("\nOptions:\n"));
1055  printf(_(" -a, --all vacuum all databases\n"));
1056  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1057  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1058  printf(_(" -e, --echo show the commands being sent to the server\n"));
1059  printf(_(" -f, --full do full vacuuming\n"));
1060  printf(_(" -F, --freeze freeze row transaction information\n"));
1061  printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1062  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1063  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1064  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1065  printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1066  printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1067  printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1068  printf(_(" -n, --schema=PATTERN vacuum tables in the specified schema(s) only\n"));
1069  printf(_(" -N, --exclude-schema=PATTERN do not vacuum tables in the specified schema(s)\n"));
1070  printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1071  printf(_(" -q, --quiet don't write any messages\n"));
1072  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1073  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1074  printf(_(" -v, --verbose write a lot of output\n"));
1075  printf(_(" -V, --version output version information, then exit\n"));
1076  printf(_(" -z, --analyze update optimizer statistics\n"));
1077  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1078  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1079  " stages for faster results; no vacuum\n"));
1080  printf(_(" -?, --help show this help, then exit\n"));
1081  printf(_("\nConnection options:\n"));
1082  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1083  printf(_(" -p, --port=PORT database server port\n"));
1084  printf(_(" -U, --username=USERNAME user name to connect as\n"));
1085  printf(_(" -w, --no-password never prompt for password\n"));
1086  printf(_(" -W, --password force password prompt\n"));
1087  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1088  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1089  printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1090  printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1091 }
static void setup_cancel_handler(void)
Definition: parallel.c:608
void splitTableColumnsSpec(const char *spec, int encoding, char **table, const char **columns)
Definition: common.c:34
#define gettext_noop(x)
Definition: c.h:1135
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1153
#define CppAsString2(x)
Definition: c.h:311
volatile sig_atomic_t CancelRequested
Definition: cancel.c:59
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:460
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
#define _(x)
Definition: elog.c:90
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6733
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6589
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6743
int PQclientEncoding(const PGconn *conn)
Definition: fe-connect.c:6820
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4130
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3310
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3705
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3730
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1418
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
void pg_free(void *ptr)
Definition: fe_memutils.c:105
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:57
#define no_argument
Definition: getopt_long.h:24
#define required_argument
Definition: getopt_long.h:25
int i
Definition: isn.c:73
static void const char fflush(stdout)
Assert(fmt[strlen(fmt) - 1] !='\n')
exit(1)
void pg_logging_init(const char *argv0)
Definition: logging.c:83
#define pg_log_error(...)
Definition: logging.h:106
#define pg_log_error_hint(...)
Definition: logging.h:112
const char * progname
Definition: main.c:45
bool option_parse_int(const char *optarg, const char *optname, int min_range, int max_range, int *result)
Definition: option_utils.c:50
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
Definition: option_utils.c:24
ParallelSlot * ParallelSlotsGetIdle(ParallelSlotArray *sa, const char *dbname)
bool ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
ParallelSlotArray * ParallelSlotsSetup(int numslots, ConnParams *cparams, const char *progname, bool echo, const char *initcmd)
bool TableCommandResultHandler(PGresult *res, PGconn *conn, void *context)
void ParallelSlotsTerminate(ParallelSlotArray *sa)
void ParallelSlotsAdoptConn(ParallelSlotArray *sa, PGconn *conn)
static void ParallelSlotSetHandler(ParallelSlot *slot, ParallelSlotResultHandler handler, void *context)
Definition: parallel_slot.h:47
#define pg_fatal(...)
static PGconn * connectDatabase(const char *dbname, const char *connection_string, const char *pghost, const char *pgport, const char *pguser, trivalue prompt_password, bool fail_on_error)
Definition: pg_dumpall.c:1641
static void executeCommand(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1884
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1861
PGDLLIMPORT int optind
Definition: getopt.c:50
PGDLLIMPORT char * optarg
Definition: getopt.c:52
static int port
Definition: pg_regress.c:90
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:225
static char * buf
Definition: pg_test_fsync.c:67
const char * username
Definition: pgbench.c:305
const char * get_progname(const char *argv0)
Definition: path.c:574
#define printf(...)
Definition: port.h:244
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:90
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:146
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:265
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:378
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:367
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:129
char * c
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
char * dbname
Definition: streamutil.c:51
PGconn * conn
Definition: streamutil.c:54
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
Definition: string_utils.c:293
const char * fmtQualifiedId(const char *schema, const char *id)
Definition: string_utils.c:145
PGconn * connection
Definition: parallel_slot.h:23
char val[FLEXIBLE_ARRAY_MEMBER]
Definition: simple_list.h:37
struct SimpleStringListCell * next
Definition: simple_list.h:34
SimpleStringListCell * head
Definition: simple_list.h:42
const char * pguser
Definition: connect_utils.h:31
char * override_dbname
Definition: pg_backup.h:89
char * pgport
Definition: pg_backup.h:83
char * pghost
Definition: pg_backup.h:84
char * dbname
Definition: pg_backup.h:82
enum trivalue prompt_password
Definition: connect_utils.h:32
bool force_index_cleanup
Definition: vacuumdb.c:44
bool no_index_cleanup
Definition: vacuumdb.c:43
int parallel_workers
Definition: vacuumdb.c:41
bool disable_page_skipping
Definition: vacuumdb.c:37
bool skip_locked
Definition: vacuumdb.c:38
bool process_toast
Definition: vacuumdb.c:46
bool do_truncate
Definition: vacuumdb.c:45
bool and_analyze
Definition: vacuumdb.c:34
bool analyze_only
Definition: vacuumdb.c:32
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74
int main(int argc, char *argv[])
Definition: vacuumdb.c:91
void check_objfilter(void)
Definition: vacuumdb.c:406
static void help(const char *progname)
Definition: vacuumdb.c:1049
struct vacuumingOptions vacuumingOptions
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table)
Definition: vacuumdb.c:1027
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:87
VacObjFilter
Definition: vacuumdb.c:51
@ OBJFILTER_DATABASE
Definition: vacuumdb.c:54
@ OBJFILTER_ALL_DBS
Definition: vacuumdb.c:53
@ OBJFILTER_SCHEMA
Definition: vacuumdb.c:56
@ OBJFILTER_SCHEMA_EXCLUDE
Definition: vacuumdb.c:57
@ OBJFILTER_TABLE
Definition: vacuumdb.c:55
@ OBJFILTER_NONE
Definition: vacuumdb.c:52
static void vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, bool analyze_in_stages, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:819
static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion, vacuumingOptions *vacopts, const char *table)
Definition: vacuumdb.c:885
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:86
static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:451
VacObjFilter objfilter
Definition: vacuumdb.c:60
trivalue
Definition: vacuumlo.c:35
@ TRI_YES
Definition: vacuumlo.c:38
@ TRI_DEFAULT
Definition: vacuumlo.c:36
@ TRI_NO
Definition: vacuumlo.c:37