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