PostgreSQL Source Code  git master
clusterdb.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * clusterdb
4  *
5  * Portions Copyright (c) 2002-2019, PostgreSQL Global Development Group
6  *
7  * src/bin/scripts/clusterdb.c
8  *
9  *-------------------------------------------------------------------------
10  */
11 
12 #include "postgres_fe.h"
13 #include "common.h"
14 #include "common/logging.h"
15 #include "fe_utils/cancel.h"
16 #include "fe_utils/simple_list.h"
17 #include "fe_utils/string_utils.h"
18 
19 
20 static void cluster_one_database(const char *dbname, bool verbose, const char *table,
21  const char *host, const char *port,
22  const char *username, enum trivalue prompt_password,
23  const char *progname, bool echo);
24 static void cluster_all_databases(bool verbose, 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, bool quiet);
28 
29 static void help(const char *progname);
30 
31 
32 int
33 main(int argc, char *argv[])
34 {
35  static struct option long_options[] = {
36  {"host", required_argument, NULL, 'h'},
37  {"port", required_argument, NULL, 'p'},
38  {"username", required_argument, NULL, 'U'},
39  {"no-password", no_argument, NULL, 'w'},
40  {"password", no_argument, NULL, 'W'},
41  {"echo", no_argument, NULL, 'e'},
42  {"quiet", no_argument, NULL, 'q'},
43  {"dbname", required_argument, NULL, 'd'},
44  {"all", no_argument, NULL, 'a'},
45  {"table", required_argument, NULL, 't'},
46  {"verbose", no_argument, NULL, 'v'},
47  {"maintenance-db", required_argument, NULL, 2},
48  {NULL, 0, NULL, 0}
49  };
50 
51  const char *progname;
52  int optindex;
53  int c;
54 
55  const char *dbname = NULL;
56  const char *maintenance_db = NULL;
57  char *host = NULL;
58  char *port = NULL;
59  char *username = NULL;
60  enum trivalue prompt_password = TRI_DEFAULT;
61  bool echo = false;
62  bool quiet = false;
63  bool alldb = false;
64  bool verbose = false;
65  SimpleStringList tables = {NULL, NULL};
66 
67  pg_logging_init(argv[0]);
68  progname = get_progname(argv[0]);
69  set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
70 
71  handle_help_version_opts(argc, argv, "clusterdb", help);
72 
73  while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:at:v", long_options, &optindex)) != -1)
74  {
75  switch (c)
76  {
77  case 'h':
78  host = pg_strdup(optarg);
79  break;
80  case 'p':
81  port = pg_strdup(optarg);
82  break;
83  case 'U':
84  username = pg_strdup(optarg);
85  break;
86  case 'w':
87  prompt_password = TRI_NO;
88  break;
89  case 'W':
90  prompt_password = TRI_YES;
91  break;
92  case 'e':
93  echo = true;
94  break;
95  case 'q':
96  quiet = true;
97  break;
98  case 'd':
99  dbname = pg_strdup(optarg);
100  break;
101  case 'a':
102  alldb = true;
103  break;
104  case 't':
106  break;
107  case 'v':
108  verbose = true;
109  break;
110  case 2:
111  maintenance_db = pg_strdup(optarg);
112  break;
113  default:
114  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
115  exit(1);
116  }
117  }
118 
119  /*
120  * Non-option argument specifies database name as long as it wasn't
121  * already specified with -d / --dbname
122  */
123  if (optind < argc && dbname == NULL)
124  {
125  dbname = argv[optind];
126  optind++;
127  }
128 
129  if (optind < argc)
130  {
131  pg_log_error("too many command-line arguments (first is \"%s\")",
132  argv[optind]);
133  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
134  exit(1);
135  }
136 
137  setup_cancel_handler(NULL);
138 
139  if (alldb)
140  {
141  if (dbname)
142  {
143  pg_log_error("cannot cluster all databases and a specific one at the same time");
144  exit(1);
145  }
146 
147  if (tables.head != NULL)
148  {
149  pg_log_error("cannot cluster specific table(s) in all databases");
150  exit(1);
151  }
152 
153  cluster_all_databases(verbose, maintenance_db, host, port, username, prompt_password,
154  progname, echo, quiet);
155  }
156  else
157  {
158  if (dbname == NULL)
159  {
160  if (getenv("PGDATABASE"))
161  dbname = getenv("PGDATABASE");
162  else if (getenv("PGUSER"))
163  dbname = getenv("PGUSER");
164  else
165  dbname = get_user_name_or_exit(progname);
166  }
167 
168  if (tables.head != NULL)
169  {
170  SimpleStringListCell *cell;
171 
172  for (cell = tables.head; cell; cell = cell->next)
173  {
174  cluster_one_database(dbname, verbose, cell->val,
175  host, port, username, prompt_password,
176  progname, echo);
177  }
178  }
179  else
180  cluster_one_database(dbname, verbose, NULL,
181  host, port, username, prompt_password,
182  progname, echo);
183  }
184 
185  exit(0);
186 }
187 
188 
189 static void
190 cluster_one_database(const char *dbname, bool verbose, const char *table,
191  const char *host, const char *port,
192  const char *username, enum trivalue prompt_password,
193  const char *progname, bool echo)
194 {
195  PQExpBufferData sql;
196 
197  PGconn *conn;
198 
199  conn = connectDatabase(dbname, host, port, username, prompt_password,
200  progname, echo, false, false);
201 
202  initPQExpBuffer(&sql);
203 
204  appendPQExpBufferStr(&sql, "CLUSTER");
205  if (verbose)
206  appendPQExpBufferStr(&sql, " VERBOSE");
207  if (table)
208  {
209  appendPQExpBufferChar(&sql, ' ');
210  appendQualifiedRelation(&sql, table, conn, echo);
211  }
212  appendPQExpBufferChar(&sql, ';');
213 
214  if (!executeMaintenanceCommand(conn, sql.data, echo))
215  {
216  if (table)
217  pg_log_error("clustering of table \"%s\" in database \"%s\" failed: %s",
218  table, PQdb(conn), PQerrorMessage(conn));
219  else
220  pg_log_error("clustering of database \"%s\" failed: %s",
221  PQdb(conn), PQerrorMessage(conn));
222  PQfinish(conn);
223  exit(1);
224  }
225  PQfinish(conn);
226  termPQExpBuffer(&sql);
227 }
228 
229 
230 static void
231 cluster_all_databases(bool verbose, const char *maintenance_db,
232  const char *host, const char *port,
233  const char *username, enum trivalue prompt_password,
234  const char *progname, bool echo, bool quiet)
235 {
236  PGconn *conn;
237  PGresult *result;
239  int i;
240 
241  conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
242  prompt_password, progname, echo);
243  result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", echo);
244  PQfinish(conn);
245 
246  initPQExpBuffer(&connstr);
247  for (i = 0; i < PQntuples(result); i++)
248  {
249  char *dbname = PQgetvalue(result, i, 0);
250 
251  if (!quiet)
252  {
253  printf(_("%s: clustering database \"%s\"\n"), progname, dbname);
254  fflush(stdout);
255  }
256 
257  resetPQExpBuffer(&connstr);
258  appendPQExpBufferStr(&connstr, "dbname=");
259  appendConnStrVal(&connstr, dbname);
260 
261  cluster_one_database(connstr.data, verbose, NULL,
262  host, port, username, prompt_password,
263  progname, echo);
264  }
265  termPQExpBuffer(&connstr);
266 
267  PQclear(result);
268 }
269 
270 
271 static void
272 help(const char *progname)
273 {
274  printf(_("%s clusters all previously clustered tables in a database.\n\n"), progname);
275  printf(_("Usage:\n"));
276  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
277  printf(_("\nOptions:\n"));
278  printf(_(" -a, --all cluster all databases\n"));
279  printf(_(" -d, --dbname=DBNAME database to cluster\n"));
280  printf(_(" -e, --echo show the commands being sent to the server\n"));
281  printf(_(" -q, --quiet don't write any messages\n"));
282  printf(_(" -t, --table=TABLE cluster specific table(s) only\n"));
283  printf(_(" -v, --verbose write a lot of output\n"));
284  printf(_(" -V, --version output version information, then exit\n"));
285  printf(_(" -?, --help show this help, then exit\n"));
286  printf(_("\nConnection options:\n"));
287  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
288  printf(_(" -p, --port=PORT database server port\n"));
289  printf(_(" -U, --username=USERNAME user name to connect as\n"));
290  printf(_(" -w, --no-password never prompt for password\n"));
291  printf(_(" -W, --password force password prompt\n"));
292  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
293  printf(_("\nRead the description of the SQL command CLUSTER for details.\n"));
294  printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
295 }
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 void cluster_all_databases(bool verbose, const char *maintenance_db, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo, bool quiet)
Definition: clusterdb.c:231
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1878
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6623
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
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:39
void appendConnStrVal(PQExpBuffer buf, const char *str)
Definition: string_utils.c:545
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4125
#define printf(...)
Definition: port.h:198
static void setup_cancel_handler(void)
Definition: parallel.c:617
const char * progname
Definition: pg_standby.c:36
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
#define fprintf
Definition: port.h:196
#define required_argument
Definition: getopt_long.h:25
int optind
Definition: getopt.c:50
PGconn * conn
Definition: streamutil.c:54
char * c
bool executeMaintenanceCommand(PGconn *conn, const char *query, bool echo)
Definition: common.c:255
struct SimpleStringListCell * next
Definition: simple_list.h:34
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
int main(int argc, char *argv[])
Definition: clusterdb.c:33
static void help(const char *progname)
Definition: clusterdb.c:272
static int port
Definition: pg_regress.c:91
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
Definition: common.c:33
static int verbose
trivalue
Definition: vacuumlo.c:33
#define no_argument
Definition: getopt_long.h:24
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1166
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
static char * username
Definition: initdb.c:133
static void cluster_one_database(const char *dbname, bool verbose, const char *table, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo)
Definition: clusterdb.c:190
void PQclear(PGresult *res)
Definition: fe-exec.c:694
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6473
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:1634
SimpleStringListCell * head
Definition: simple_list.h:42
char * dbname
Definition: streamutil.c:50
void appendQualifiedRelation(PQExpBuffer buf, const char *spec, PGconn *conn, bool echo)
Definition: common.c:368
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:565
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
#define _(x)
Definition: elog.c:87
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