Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * vacuumdb
4 : *
5 : * Portions Copyright (c) 1996-2025, 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 "common.h"
18 : #include "common/logging.h"
19 : #include "fe_utils/option_utils.h"
20 : #include "vacuuming.h"
21 :
22 : static void help(const char *progname);
23 : static void check_objfilter(bits32 objfilter);
24 :
25 :
26 : int
27 196 : main(int argc, char *argv[])
28 : {
29 : static struct option long_options[] = {
30 : {"host", required_argument, NULL, 'h'},
31 : {"port", required_argument, NULL, 'p'},
32 : {"username", required_argument, NULL, 'U'},
33 : {"no-password", no_argument, NULL, 'w'},
34 : {"password", no_argument, NULL, 'W'},
35 : {"echo", no_argument, NULL, 'e'},
36 : {"quiet", no_argument, NULL, 'q'},
37 : {"dbname", required_argument, NULL, 'd'},
38 : {"analyze", no_argument, NULL, 'z'},
39 : {"analyze-only", no_argument, NULL, 'Z'},
40 : {"freeze", no_argument, NULL, 'F'},
41 : {"all", no_argument, NULL, 'a'},
42 : {"table", required_argument, NULL, 't'},
43 : {"full", no_argument, NULL, 'f'},
44 : {"verbose", no_argument, NULL, 'v'},
45 : {"jobs", required_argument, NULL, 'j'},
46 : {"parallel", required_argument, NULL, 'P'},
47 : {"schema", required_argument, NULL, 'n'},
48 : {"exclude-schema", required_argument, NULL, 'N'},
49 : {"maintenance-db", required_argument, NULL, 2},
50 : {"analyze-in-stages", no_argument, NULL, 3},
51 : {"disable-page-skipping", no_argument, NULL, 4},
52 : {"skip-locked", no_argument, NULL, 5},
53 : {"min-xid-age", required_argument, NULL, 6},
54 : {"min-mxid-age", required_argument, NULL, 7},
55 : {"no-index-cleanup", no_argument, NULL, 8},
56 : {"force-index-cleanup", no_argument, NULL, 9},
57 : {"no-truncate", no_argument, NULL, 10},
58 : {"no-process-toast", no_argument, NULL, 11},
59 : {"no-process-main", no_argument, NULL, 12},
60 : {"buffer-usage-limit", required_argument, NULL, 13},
61 : {"missing-stats-only", no_argument, NULL, 14},
62 : {NULL, 0, NULL, 0}
63 : };
64 :
65 : const char *progname;
66 : int optindex;
67 : int c;
68 196 : const char *dbname = NULL;
69 196 : const char *maintenance_db = NULL;
70 : ConnParams cparams;
71 196 : bool echo = false;
72 196 : bool quiet = false;
73 : vacuumingOptions vacopts;
74 196 : SimpleStringList objects = {NULL, NULL};
75 196 : int concurrentCons = 1;
76 196 : unsigned int tbl_count = 0;
77 : int ret;
78 :
79 : /* initialize options */
80 196 : memset(&vacopts, 0, sizeof(vacopts));
81 196 : vacopts.objfilter = 0; /* no filter */
82 196 : vacopts.parallel_workers = -1;
83 196 : vacopts.buffer_usage_limit = NULL;
84 196 : vacopts.no_index_cleanup = false;
85 196 : vacopts.force_index_cleanup = false;
86 196 : vacopts.do_truncate = true;
87 196 : vacopts.process_main = true;
88 196 : vacopts.process_toast = true;
89 :
90 : /* the same for connection parameters */
91 196 : memset(&cparams, 0, sizeof(cparams));
92 196 : cparams.prompt_password = TRI_DEFAULT;
93 :
94 196 : pg_logging_init(argv[0]);
95 196 : progname = get_progname(argv[0]);
96 196 : set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
97 :
98 196 : handle_help_version_opts(argc, argv, "vacuumdb", help);
99 :
100 534 : while ((c = getopt_long(argc, argv, "ad:efFh:j:n:N:p:P:qt:U:vwWzZ",
101 534 : long_options, &optindex)) != -1)
102 : {
103 384 : switch (c)
104 : {
105 50 : case 'a':
106 50 : vacopts.objfilter |= OBJFILTER_ALL_DBS;
107 50 : break;
108 4 : case 'd':
109 4 : vacopts.objfilter |= OBJFILTER_DATABASE;
110 4 : dbname = pg_strdup(optarg);
111 4 : break;
112 2 : case 'e':
113 2 : echo = true;
114 2 : break;
115 2 : case 'f':
116 2 : vacopts.full = true;
117 2 : break;
118 18 : case 'F':
119 18 : vacopts.freeze = true;
120 18 : break;
121 32 : case 'h':
122 32 : cparams.pghost = pg_strdup(optarg);
123 32 : break;
124 2 : case 'j':
125 2 : if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
126 : &concurrentCons))
127 0 : exit(1);
128 2 : break;
129 12 : case 'n':
130 12 : vacopts.objfilter |= OBJFILTER_SCHEMA;
131 12 : simple_string_list_append(&objects, optarg);
132 12 : break;
133 12 : case 'N':
134 12 : vacopts.objfilter |= OBJFILTER_SCHEMA_EXCLUDE;
135 12 : simple_string_list_append(&objects, optarg);
136 12 : break;
137 32 : case 'p':
138 32 : cparams.pgport = pg_strdup(optarg);
139 32 : break;
140 6 : case 'P':
141 6 : if (!option_parse_int(optarg, "-P/--parallel", 0, INT_MAX,
142 : &vacopts.parallel_workers))
143 2 : exit(1);
144 4 : break;
145 0 : case 'q':
146 0 : quiet = true;
147 0 : break;
148 48 : case 't':
149 48 : vacopts.objfilter |= OBJFILTER_TABLE;
150 48 : simple_string_list_append(&objects, optarg);
151 48 : tbl_count++;
152 48 : break;
153 32 : case 'U':
154 32 : cparams.pguser = pg_strdup(optarg);
155 32 : break;
156 0 : case 'v':
157 0 : vacopts.verbose = true;
158 0 : break;
159 0 : case 'w':
160 0 : cparams.prompt_password = TRI_NO;
161 0 : break;
162 0 : case 'W':
163 0 : cparams.prompt_password = TRI_YES;
164 0 : break;
165 24 : case 'z':
166 24 : vacopts.and_analyze = true;
167 24 : break;
168 42 : case 'Z':
169 : /* if analyze-in-stages is given, don't override it */
170 42 : if (vacopts.mode != MODE_ANALYZE_IN_STAGES)
171 42 : vacopts.mode = MODE_ANALYZE;
172 42 : break;
173 0 : case 2:
174 0 : maintenance_db = pg_strdup(optarg);
175 0 : break;
176 12 : case 3:
177 12 : vacopts.mode = MODE_ANALYZE_IN_STAGES;
178 12 : break;
179 4 : case 4:
180 4 : vacopts.disable_page_skipping = true;
181 4 : break;
182 4 : case 5:
183 4 : vacopts.skip_locked = true;
184 4 : break;
185 4 : case 6:
186 4 : if (!option_parse_int(optarg, "--min-xid-age", 1, INT_MAX,
187 : &vacopts.min_xid_age))
188 2 : exit(1);
189 2 : break;
190 4 : case 7:
191 4 : if (!option_parse_int(optarg, "--min-mxid-age", 1, INT_MAX,
192 : &vacopts.min_mxid_age))
193 2 : exit(1);
194 2 : break;
195 4 : case 8:
196 4 : vacopts.no_index_cleanup = true;
197 4 : break;
198 0 : case 9:
199 0 : vacopts.force_index_cleanup = true;
200 0 : break;
201 4 : case 10:
202 4 : vacopts.do_truncate = false;
203 4 : break;
204 4 : case 11:
205 4 : vacopts.process_toast = false;
206 4 : break;
207 4 : case 12:
208 4 : vacopts.process_main = false;
209 4 : break;
210 0 : case 13:
211 0 : vacopts.buffer_usage_limit = escape_quotes(optarg);
212 0 : break;
213 20 : case 14:
214 20 : vacopts.missing_stats_only = true;
215 20 : break;
216 2 : default:
217 : /* getopt_long already emitted a complaint */
218 2 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
219 2 : exit(1);
220 : }
221 : }
222 :
223 : /*
224 : * Non-option argument specifies database name as long as it wasn't
225 : * already specified with -d / --dbname
226 : */
227 150 : if (optind < argc && dbname == NULL)
228 : {
229 100 : vacopts.objfilter |= OBJFILTER_DATABASE;
230 100 : dbname = argv[optind];
231 100 : optind++;
232 : }
233 :
234 150 : if (optind < argc)
235 : {
236 0 : pg_log_error("too many command-line arguments (first is \"%s\")",
237 : argv[optind]);
238 0 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
239 0 : exit(1);
240 : }
241 :
242 : /*
243 : * Validate the combination of filters specified in the command-line
244 : * options.
245 : */
246 150 : check_objfilter(vacopts.objfilter);
247 :
248 140 : if (vacopts.mode == MODE_ANALYZE ||
249 98 : vacopts.mode == MODE_ANALYZE_IN_STAGES)
250 : {
251 54 : if (vacopts.full)
252 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
253 : "full");
254 54 : if (vacopts.freeze)
255 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
256 : "freeze");
257 54 : if (vacopts.disable_page_skipping)
258 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
259 : "disable-page-skipping");
260 52 : if (vacopts.no_index_cleanup)
261 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
262 : "no-index-cleanup");
263 50 : if (vacopts.force_index_cleanup)
264 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
265 : "force-index-cleanup");
266 50 : if (!vacopts.do_truncate)
267 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
268 : "no-truncate");
269 48 : if (!vacopts.process_main)
270 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
271 : "no-process-main");
272 46 : if (!vacopts.process_toast)
273 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
274 : "no-process-toast");
275 : /* allow 'and_analyze' with 'analyze_only' */
276 : }
277 :
278 : /* Prohibit full and analyze_only options with parallel option */
279 130 : if (vacopts.parallel_workers >= 0)
280 : {
281 4 : if (vacopts.mode == MODE_ANALYZE ||
282 4 : vacopts.mode == MODE_ANALYZE_IN_STAGES)
283 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
284 : "parallel");
285 4 : if (vacopts.full)
286 0 : pg_fatal("cannot use the \"%s\" option when performing full vacuum",
287 : "parallel");
288 : }
289 :
290 : /* Prohibit --no-index-cleanup and --force-index-cleanup together */
291 130 : if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
292 0 : pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
293 : "no-index-cleanup", "force-index-cleanup");
294 :
295 : /*
296 : * buffer-usage-limit is not allowed with VACUUM FULL unless ANALYZE is
297 : * included too.
298 : */
299 130 : if (vacopts.buffer_usage_limit && vacopts.full && !vacopts.and_analyze)
300 0 : pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
301 : "buffer-usage-limit", "full");
302 :
303 : /*
304 : * Prohibit --missing-stats-only without --analyze-only or
305 : * --analyze-in-stages.
306 : */
307 130 : if (vacopts.missing_stats_only && (vacopts.mode != MODE_ANALYZE &&
308 8 : vacopts.mode != MODE_ANALYZE_IN_STAGES))
309 0 : pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
310 : "missing-stats-only", "analyze-only", "analyze-in-stages");
311 :
312 130 : ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
313 : &objects, tbl_count,
314 : concurrentCons,
315 : progname, echo, quiet);
316 126 : exit(ret);
317 : }
318 :
319 : /*
320 : * Verify that the filters used at command line are compatible.
321 : */
322 : void
323 150 : check_objfilter(bits32 objfilter)
324 : {
325 150 : if ((objfilter & OBJFILTER_ALL_DBS) &&
326 50 : (objfilter & OBJFILTER_DATABASE))
327 4 : pg_fatal("cannot vacuum all databases and a specific one at the same time");
328 :
329 146 : if ((objfilter & OBJFILTER_TABLE) &&
330 44 : (objfilter & OBJFILTER_SCHEMA))
331 2 : pg_fatal("cannot vacuum all tables in schema(s) and specific table(s) at the same time");
332 :
333 144 : if ((objfilter & OBJFILTER_TABLE) &&
334 42 : (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
335 2 : pg_fatal("cannot vacuum specific table(s) and exclude schema(s) at the same time");
336 :
337 142 : if ((objfilter & OBJFILTER_SCHEMA) &&
338 8 : (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
339 2 : pg_fatal("cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
340 140 : }
341 :
342 :
343 : static void
344 2 : help(const char *progname)
345 : {
346 2 : printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
347 2 : printf(_("Usage:\n"));
348 2 : printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
349 2 : printf(_("\nOptions:\n"));
350 2 : printf(_(" -a, --all vacuum all databases\n"));
351 2 : printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
352 2 : printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
353 2 : printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
354 2 : printf(_(" -e, --echo show the commands being sent to the server\n"));
355 2 : printf(_(" -f, --full do full vacuuming\n"));
356 2 : printf(_(" -F, --freeze freeze row transaction information\n"));
357 2 : printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
358 2 : printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
359 2 : printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
360 2 : printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
361 2 : printf(_(" --missing-stats-only only analyze relations with missing statistics\n"));
362 2 : printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
363 2 : printf(_(" --no-process-main skip the main relation\n"));
364 2 : printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
365 2 : printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
366 2 : printf(_(" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
367 2 : printf(_(" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
368 2 : printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
369 2 : printf(_(" -q, --quiet don't write any messages\n"));
370 2 : printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
371 2 : printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
372 2 : printf(_(" -v, --verbose write a lot of output\n"));
373 2 : printf(_(" -V, --version output version information, then exit\n"));
374 2 : printf(_(" -z, --analyze update optimizer statistics\n"));
375 2 : printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
376 2 : printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
377 : " stages for faster results; no vacuum\n"));
378 2 : printf(_(" -?, --help show this help, then exit\n"));
379 2 : printf(_("\nConnection options:\n"));
380 2 : printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
381 2 : printf(_(" -p, --port=PORT database server port\n"));
382 2 : printf(_(" -U, --username=USERNAME user name to connect as\n"));
383 2 : printf(_(" -w, --no-password never prompt for password\n"));
384 2 : printf(_(" -W, --password force password prompt\n"));
385 2 : printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
386 2 : printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
387 2 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
388 2 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
389 2 : }
|
