1/*-------------------------------------------------------------------------
2 *
3 * reindexdb
4 *
5 * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
6 *
7 * src/bin/scripts/reindexdb.c
8 *
9 *-------------------------------------------------------------------------
10 */
11
12#include "postgres_fe.h"
13#include "common.h"
14#include "common/logging.h"
15#include "fe_utils/simple_list.h"
16#include "fe_utils/string_utils.h"
17
18
19static void reindex_one_database(const char *name, const char *dbname,
20 const char *type, const char *host,
21 const char *port, const char *username,
22 enum trivalue prompt_password, const char *progname,
23 bool echo, bool verbose, bool concurrently);
24static void reindex_all_databases(const char *maintenance_db,
25 const char *host, const char *port,
26 const char *username, enum trivalue prompt_password,
27 const char *progname, bool echo,
28 bool quiet, bool verbose, bool concurrently);
29static void reindex_system_catalogs(const char *dbname,
30 const char *host, const char *port,
31 const char *username, enum trivalue prompt_password,
32 const char *progname, bool echo, bool verbose,
33 bool concurrently);
34static void help(const char *progname);
35
36int
37main(int argc, char *argv[])
38{
39 static struct option long_options[] = {
40 {"host", required_argument, NULL, 'h'},
41 {"port", required_argument, NULL, 'p'},
42 {"username", required_argument, NULL, 'U'},
43 {"no-password", no_argument, NULL, 'w'},
44 {"password", no_argument, NULL, 'W'},
45 {"echo", no_argument, NULL, 'e'},
46 {"quiet", no_argument, NULL, 'q'},
47 {"schema", required_argument, NULL, 'S'},
48 {"dbname", required_argument, NULL, 'd'},
49 {"all", no_argument, NULL, 'a'},
50 {"system", no_argument, NULL, 's'},
51 {"table", required_argument, NULL, 't'},
52 {"index", required_argument, NULL, 'i'},
53 {"verbose", no_argument, NULL, 'v'},
54 {"concurrently", no_argument, NULL, 1},
55 {"maintenance-db", required_argument, NULL, 2},
56 {NULL, 0, NULL, 0}
57 };
58
59 const char *progname;
60 int optindex;
61 int c;
62
63 const char *dbname = NULL;
64 const char *maintenance_db = NULL;
65 const char *host = NULL;
66 const char *port = NULL;
67 const char *username = NULL;
68 enum trivalue prompt_password = TRI_DEFAULT;
69 bool syscatalog = false;
70 bool alldb = false;
71 bool echo = false;
72 bool quiet = false;
73 bool verbose = false;
74 bool concurrently = false;
75 SimpleStringList indexes = {NULL, NULL};
76 SimpleStringList tables = {NULL, NULL};
77 SimpleStringList schemas = {NULL, NULL};
78
79 pg_logging_init(argv[0]);
80 progname = get_progname(argv[0]);
81 set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
82
83 handle_help_version_opts(argc, argv, "reindexdb", help);
84
85 /* process command-line options */
86 while ((c = getopt_long(argc, argv, "h:p:U:wWeqS:d:ast:i:v", long_options, &optindex)) != -1)
87 {
88 switch (c)
89 {
90 case 'h':
91 host = pg_strdup(optarg);
92 break;
93 case 'p':
94 port = pg_strdup(optarg);
95 break;
96 case 'U':
97 username = pg_strdup(optarg);
98 break;
99 case 'w':
100 prompt_password = TRI_NO;
101 break;
102 case 'W':
103 prompt_password = TRI_YES;
104 break;
105 case 'e':
106 echo = true;
107 break;
108 case 'q':
109 quiet = true;
110 break;
111 case 'S':
112 simple_string_list_append(&schemas, optarg);
113 break;
114 case 'd':
115 dbname = pg_strdup(optarg);
116 break;
117 case 'a':
118 alldb = true;
119 break;
120 case 's':
121 syscatalog = true;
122 break;
123 case 't':
124 simple_string_list_append(&tables, optarg);
125 break;
126 case 'i':
127 simple_string_list_append(&indexes, optarg);
128 break;
129 case 'v':
130 verbose = true;
131 break;
132 case 1:
133 concurrently = true;
134 break;
135 case 2:
136 maintenance_db = pg_strdup(optarg);
137 break;
138 default:
139 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
140 exit(1);
141 }
142 }
143
144 /*
145 * Non-option argument specifies database name as long as it wasn't
146 * already specified with -d / --dbname
147 */
148 if (optind < argc && dbname == NULL)
149 {
150 dbname = argv[optind];
151 optind++;
152 }
153
154 if (optind < argc)
155 {
156 pg_log_error("too many command-line arguments (first is \"%s\")",
157 argv[optind]);
158 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
159 exit(1);
160 }
161
162 setup_cancel_handler();
163
164 if (alldb)
165 {
166 if (dbname)
167 {
168 pg_log_error("cannot reindex all databases and a specific one at the same time");
169 exit(1);
170 }
171 if (syscatalog)
172 {
173 pg_log_error("cannot reindex all databases and system catalogs at the same time");
174 exit(1);
175 }
176 if (schemas.head != NULL)
177 {
178 pg_log_error("cannot reindex specific schema(s) in all databases");
179 exit(1);
180 }
181 if (tables.head != NULL)
182 {
183 pg_log_error("cannot reindex specific table(s) in all databases");
184 exit(1);
185 }
186 if (indexes.head != NULL)
187 {
188 pg_log_error("cannot reindex specific index(es) in all databases");
189 exit(1);
190 }
191
192 reindex_all_databases(maintenance_db, host, port, username,
193 prompt_password, progname, echo, quiet, verbose, concurrently);
194 }
195 else if (syscatalog)
196 {
197 if (schemas.head != NULL)
198 {
199 pg_log_error("cannot reindex specific schema(s) and system catalogs at the same time");
200 exit(1);
201 }
202 if (tables.head != NULL)
203 {
204 pg_log_error("cannot reindex specific table(s) and system catalogs at the same time");
205 exit(1);
206 }
207 if (indexes.head != NULL)
208 {
209 pg_log_error("cannot reindex specific index(es) and system catalogs at the same time");
210 exit(1);
211 }
212
213 if (dbname == NULL)
214 {
215 if (getenv("PGDATABASE"))
216 dbname = getenv("PGDATABASE");
217 else if (getenv("PGUSER"))
218 dbname = getenv("PGUSER");
219 else
220 dbname = get_user_name_or_exit(progname);
221 }
222
223 reindex_system_catalogs(dbname, host, port, username, prompt_password,
224 progname, echo, verbose, concurrently);
225 }
226 else
227 {
228 if (dbname == NULL)
229 {
230 if (getenv("PGDATABASE"))
231 dbname = getenv("PGDATABASE");
232 else if (getenv("PGUSER"))
233 dbname = getenv("PGUSER");
234 else
235 dbname = get_user_name_or_exit(progname);
236 }
237
238 if (schemas.head != NULL)
239 {
240 SimpleStringListCell *cell;
241
242 for (cell = schemas.head; cell; cell = cell->next)
243 {
244 reindex_one_database(cell->val, dbname, "SCHEMA", host, port,
245 username, prompt_password, progname, echo, verbose, concurrently);
246 }
247 }
248
249 if (indexes.head != NULL)
250 {
251 SimpleStringListCell *cell;
252
253 for (cell = indexes.head; cell; cell = cell->next)
254 {
255 reindex_one_database(cell->val, dbname, "INDEX", host, port,
256 username, prompt_password, progname, echo, verbose, concurrently);
257 }
258 }
259 if (tables.head != NULL)
260 {
261 SimpleStringListCell *cell;
262
263 for (cell = tables.head; cell; cell = cell->next)
264 {
265 reindex_one_database(cell->val, dbname, "TABLE", host, port,
266 username, prompt_password, progname, echo, verbose, concurrently);
267 }
268 }
269
270 /*
271 * reindex database only if neither index nor table nor schema is
272 * specified
273 */
274 if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
275 reindex_one_database(NULL, dbname, "DATABASE", host, port,
276 username, prompt_password, progname, echo, verbose, concurrently);
277 }
278
279 exit(0);
280}
281
282static void
283reindex_one_database(const char *name, const char *dbname, const char *type,
284 const char *host, const char *port, const char *username,
285 enum trivalue prompt_password, const char *progname, bool echo,
286 bool verbose, bool concurrently)
287{
288 PQExpBufferData sql;
289
290 PGconn *conn;
291
292 conn = connectDatabase(dbname, host, port, username, prompt_password,
293 progname, echo, false, false);
294
295 if (concurrently && PQserverVersion(conn) < 120000)
296 {
297 PQfinish(conn);
298 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
299 "concurrently", "12");
300 exit(1);
301 }
302
303 initPQExpBuffer(&sql);
304
305 appendPQExpBufferStr(&sql, "REINDEX ");
306
307 if (verbose)
308 appendPQExpBufferStr(&sql, "(VERBOSE) ");
309
310 appendPQExpBufferStr(&sql, type);
311 appendPQExpBufferChar(&sql, ' ');
312 if (concurrently)
313 appendPQExpBufferStr(&sql, "CONCURRENTLY ");
314 if (strcmp(type, "TABLE") == 0 ||
315 strcmp(type, "INDEX") == 0)
316 appendQualifiedRelation(&sql, name, conn, progname, echo);
317 else if (strcmp(type, "SCHEMA") == 0)
318 appendPQExpBufferStr(&sql, name);
319 else if (strcmp(type, "DATABASE") == 0)
320 appendPQExpBufferStr(&sql, fmtId(PQdb(conn)));
321 appendPQExpBufferChar(&sql, ';');
322
323 if (!executeMaintenanceCommand(conn, sql.data, echo))
324 {
325 if (strcmp(type, "TABLE") == 0)
326 pg_log_error("reindexing of table \"%s\" in database \"%s\" failed: %s",
327 name, PQdb(conn), PQerrorMessage(conn));
328 else if (strcmp(type, "INDEX") == 0)
329 pg_log_error("reindexing of index \"%s\" in database \"%s\" failed: %s",
330 name, PQdb(conn), PQerrorMessage(conn));
331 else if (strcmp(type, "SCHEMA") == 0)
332 pg_log_error("reindexing of schema \"%s\" in database \"%s\" failed: %s",
333 name, PQdb(conn), PQerrorMessage(conn));
334 else
335 pg_log_error("reindexing of database \"%s\" failed: %s",
336 PQdb(conn), PQerrorMessage(conn));
337 PQfinish(conn);
338 exit(1);
339 }
340
341 PQfinish(conn);
342 termPQExpBuffer(&sql);
343}
344
345static void
346reindex_all_databases(const char *maintenance_db,
347 const char *host, const char *port,
348 const char *username, enum trivalue prompt_password,
349 const char *progname, bool echo, bool quiet, bool verbose,
350 bool concurrently)
351{
352 PGconn *conn;
353 PGresult *result;
354 PQExpBufferData connstr;
355 int i;
356
357 conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
358 prompt_password, progname, echo);
359 result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
360 PQfinish(conn);
361
362 initPQExpBuffer(&connstr);
363 for (i = 0; i < PQntuples(result); i++)
364 {
365 char *dbname = PQgetvalue(result, i, 0);
366
367 if (!quiet)
368 {
369 printf(_("%s: reindexing database \"%s\"\n"), progname, dbname);
370 fflush(stdout);
371 }
372
373 resetPQExpBuffer(&connstr);
374 appendPQExpBuffer(&connstr, "dbname=");
375 appendConnStrVal(&connstr, dbname);
376
377 reindex_one_database(NULL, connstr.data, "DATABASE", host,
378 port, username, prompt_password,
379 progname, echo, verbose, concurrently);
380 }
381 termPQExpBuffer(&connstr);
382
383 PQclear(result);
384}
385
386static void
387reindex_system_catalogs(const char *dbname, const char *host, const char *port,
388 const char *username, enum trivalue prompt_password,
389 const char *progname, bool echo, bool verbose, bool concurrently)
390{
391 PGconn *conn;
392 PQExpBufferData sql;
393
394 conn = connectDatabase(dbname, host, port, username, prompt_password,
395 progname, echo, false, false);
396
397 initPQExpBuffer(&sql);
398
399 appendPQExpBuffer(&sql, "REINDEX");
400
401 if (verbose)
402 appendPQExpBuffer(&sql, " (VERBOSE)");
403
404 appendPQExpBufferStr(&sql, " SYSTEM ");
405 if (concurrently)
406 appendPQExpBuffer(&sql, "CONCURRENTLY ");
407 appendPQExpBufferStr(&sql, fmtId(PQdb(conn)));
408 appendPQExpBufferChar(&sql, ';');
409
410 if (!executeMaintenanceCommand(conn, sql.data, echo))
411 {
412 pg_log_error("reindexing of system catalogs failed: %s",
413 PQerrorMessage(conn));
414 PQfinish(conn);
415 exit(1);
416 }
417 PQfinish(conn);
418 termPQExpBuffer(&sql);
419}
420
421static void
422help(const char *progname)
423{
424 printf(_("%s reindexes a PostgreSQL database.\n\n"), progname);
425 printf(_("Usage:\n"));
426 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
427 printf(_("\nOptions:\n"));
428 printf(_(" -a, --all reindex all databases\n"));
429 printf(_(" --concurrently reindex concurrently\n"));
430 printf(_(" -d, --dbname=DBNAME database to reindex\n"));
431 printf(_(" -e, --echo show the commands being sent to the server\n"));
432 printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
433 printf(_(" -q, --quiet don't write any messages\n"));
434 printf(_(" -s, --system reindex system catalogs\n"));
435 printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
436 printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
437 printf(_(" -v, --verbose write a lot of output\n"));
438 printf(_(" -V, --version output version information, then exit\n"));
439 printf(_(" -?, --help show this help, then exit\n"));
440 printf(_("\nConnection options:\n"));
441 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
442 printf(_(" -p, --port=PORT database server port\n"));
443 printf(_(" -U, --username=USERNAME user name to connect as\n"));
444 printf(_(" -w, --no-password never prompt for password\n"));
445 printf(_(" -W, --password force password prompt\n"));
446 printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
447 printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
448 printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
449}
450