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