Skip to content

Commit 68281e0

Browse files
robertmhaasrobertmhaas
committed
Make command-line tools smarter about finding a DB to connect to.
If unable to connect to "postgres", try "template1". This allows things to work more smoothly in the case where the postgres database has been dropped. And just in case that's not good enough, also allow the user to specify a maintenance database to be used for the initial connection, to cover the case where neither postgres nor template1 is suitable.
1 parent 6ef4ae1 commit 68281e0

16 files changed

+188
-36
lines changed

‎doc/src/sgml/ref/clusterdb.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -229,6 +229,18 @@ PostgreSQL documentation
229229
</para>
230230
</listitem>
231231
</varlistentry>
232+
233+
<varlistentry>
234+
<term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></></term>
235+
<listitem>
236+
<para>
237+
Specifies the name of the database to connect to discover what other
238+
databases should be clustered. If not specified, the
239+
<literal>postgres</literal> database will be used,
240+
and if that does not exist, <literal>template1</literal> will be used.
241+
</para>
242+
</listitem>
243+
</varlistentry>
232244
</variablelist>
233245
</para>
234246
</refsect1>

‎doc/src/sgml/ref/createdb.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -276,6 +276,19 @@ PostgreSQL documentation
276276
</para>
277277
</listitem>
278278
</varlistentry>
279+
280+
<varlistentry>
281+
<term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></></term>
282+
<listitem>
283+
<para>
284+
Specifies the name of the database to connect to when creating the
285+
new database. If not specified, the <literal>postgres</literal>
286+
database will be used; if that does not exist (or if it is the name
287+
of the new database being created), <literal>template1</literal> will
288+
be used.
289+
</para>
290+
</listitem>
291+
</varlistentry>
279292
</variablelist>
280293
</para>
281294

‎doc/src/sgml/ref/dropdb.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -196,6 +196,18 @@ PostgreSQL documentation
196196
</para>
197197
</listitem>
198198
</varlistentry>
199+
200+
<varlistentry>
201+
<term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></></term>
202+
<listitem>
203+
<para>
204+
Specifies the name of the database to connect to in order to drop the
205+
target database. If not specified, the <literal>postgres</literal>
206+
database will be used; if that does not exist (or is the database
207+
being dropped), <literal>template1</literal> will be used.
208+
</para>
209+
</listitem>
210+
</varlistentry>
199211
</variablelist>
200212
</para>
201213
</refsect1>

‎doc/src/sgml/ref/reindexdb.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -243,6 +243,18 @@ PostgreSQL documentation
243243
</para>
244244
</listitem>
245245
</varlistentry>
246+
247+
<varlistentry>
248+
<term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></></term>
249+
<listitem>
250+
<para>
251+
Specifies the name of the database to connect to discover what other
252+
databases should be vacuumed. If not specified, the
253+
<literal>postgres</literal> database will be used,
254+
and if that does not exist, <literal>template1</literal> will be used.
255+
</para>
256+
</listitem>
257+
</varlistentry>
246258
</variablelist>
247259
</para>
248260
</refsect1>

‎doc/src/sgml/ref/vacuumdb.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -284,6 +284,18 @@ PostgreSQL documentation
284284
</para>
285285
</listitem>
286286
</varlistentry>
287+
288+
<varlistentry>
289+
<term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></></term>
290+
<listitem>
291+
<para>
292+
Specifies the name of the database to connect to discover what other
293+
databases should be vacuumed. If not specified, the
294+
<literal>postgres</literal> database will be used,
295+
and if that does not exist, <literal>template1</literal> will be used.
296+
</para>
297+
</listitem>
298+
</varlistentry>
287299
</variablelist>
288300
</para>
289301
</refsect1>

‎src/bin/scripts/clusterdb.c

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,8 @@ static void cluster_one_database(const char *dbname, bool verbose, const char *t
1818
const char *host, const char *port,
1919
const char *username, enum trivalue prompt_password,
2020
const char *progname, bool echo);
21-
static void cluster_all_databases(bool verbose, const char *host, const char *port,
21+
static void cluster_all_databases(bool verbose, const char *maintenance_db,
22+
const char *host, const char *port,
2223
const char *username, enum trivalue prompt_password,
2324
const char *progname, bool echo, bool quiet);
2425

@@ -40,6 +41,7 @@ main(int argc, char *argv[])
4041
{"all", no_argument, NULL, 'a'},
4142
{"table", required_argument, NULL, 't'},
4243
{"verbose", no_argument, NULL, 'v'},
44+
{"maintenance-db", required_argument, NULL, 2},
4345
{NULL, 0, NULL, 0}
4446
};
4547

@@ -48,6 +50,7 @@ main(int argc, char *argv[])
4850
int c;
4951

5052
const char *dbname = NULL;
53+
const char *maintenance_db = NULL;
5154
char *host = NULL;
5255
char *port = NULL;
5356
char *username = NULL;
@@ -100,6 +103,9 @@ main(int argc, char *argv[])
100103
case 'v':
101104
verbose = true;
102105
break;
106+
case 2:
107+
maintenance_db = optarg;
108+
break;
103109
default:
104110
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
105111
exit(1);
@@ -137,7 +143,7 @@ main(int argc, char *argv[])
137143
exit(1);
138144
}
139145

140-
cluster_all_databases(verbose, host, port, username, prompt_password,
146+
cluster_all_databases(verbose, maintenance_db, host, port, username, prompt_password,
141147
progname, echo, quiet);
142148
}
143149
else
@@ -180,7 +186,8 @@ cluster_one_database(const char *dbname, bool verbose, const char *table,
180186
appendPQExpBuffer(&sql, " %s", table);
181187
appendPQExpBuffer(&sql, ";\n");
182188

183-
conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
189+
conn = connectDatabase(dbname, host, port, username, prompt_password,
190+
progname, false);
184191
if (!executeMaintenanceCommand(conn, sql.data, echo))
185192
{
186193
if (table)
@@ -198,15 +205,17 @@ cluster_one_database(const char *dbname, bool verbose, const char *table,
198205

199206

200207
static void
201-
cluster_all_databases(bool verbose, const char *host, const char *port,
208+
cluster_all_databases(bool verbose, const char *maintenance_db,
209+
const char *host, const char *port,
202210
const char *username, enum trivalue prompt_password,
203211
const char *progname, bool echo, bool quiet)
204212
{
205213
PGconn *conn;
206214
PGresult *result;
207215
int i;
208216

209-
conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
217+
conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
218+
prompt_password, progname);
210219
result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
211220
PQfinish(conn);
212221

@@ -250,6 +259,7 @@ help(const char *progname)
250259
printf(_(" -U, --username=USERNAME user name to connect as\n"));
251260
printf(_(" -w, --no-password never prompt for password\n"));
252261
printf(_(" -W, --password force password prompt\n"));
262+
printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
253263
printf(_("\nRead the description of the SQL command CLUSTER for details.\n"));
254264
printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
255265
}

‎src/bin/scripts/common.c

Lines changed: 41 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -93,7 +93,7 @@ handle_help_version_opts(int argc, char *argv[],
9393
PGconn *
9494
connectDatabase(const char *dbname, const char *pghost, const char *pgport,
9595
const char *pguser, enum trivalue prompt_password,
96-
const char *progname)
96+
const char *progname, bool fail_ok)
9797
{
9898
PGconn *conn;
9999
char *password = NULL;
@@ -163,6 +163,11 @@ connectDatabase(const char *dbname, const char *pghost, const char *pgport,
163163
/* check to see that the backend connection was successfully made */
164164
if (PQstatus(conn) == CONNECTION_BAD)
165165
{
166+
if (fail_ok)
167+
{
168+
PQfinish(conn);
169+
return NULL;
170+
}
166171
fprintf(stderr, _("%s: could not connect to database %s: %s"),
167172
progname, dbname, PQerrorMessage(conn));
168173
exit(1);
@@ -171,6 +176,41 @@ connectDatabase(const char *dbname, const char *pghost, const char *pgport,
171176
return conn;
172177
}
173178

179+
/*
180+
* Try to connect to the appropriate maintenance database.
181+
*/
182+
PGconn *
183+
connectMaintenanceDatabase(const char *maintenance_db, const char *pghost,
184+
const char *pgport, const char *pguser,
185+
enum trivalue prompt_password,
186+
const char *progname)
187+
{
188+
PGconn *conn;
189+
190+
/* If a maintenance database name was specified, just connect to it. */
191+
if (maintenance_db)
192+
return connectDatabase(maintenance_db, pghost, pgport, pguser,
193+
prompt_password, progname, false);
194+
195+
/* Otherwise, try postgres first and then template1. */
196+
conn = connectDatabase("postgres", pghost, pgport, pguser, prompt_password,
197+
progname, true);
198+
if (!conn)
199+
conn = connectDatabase("template1", pghost, pgport, pguser,
200+
prompt_password, progname, true);
201+
202+
if (!conn)
203+
{
204+
fprintf(stderr, _("%s: could not connect to databases \"postgres\" or \"template1\"\n"
205+
"Please specify an alternative maintenance database.\n"),
206+
progname);
207+
fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
208+
progname);
209+
exit(1);
210+
}
211+
212+
return conn;
213+
}
174214

175215
/*
176216
* Run a query, return the results, exit program on failure.

‎src/bin/scripts/common.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,11 @@ extern void handle_help_version_opts(int argc, char *argv[],
3030

3131
extern PGconn *connectDatabase(const char *dbname, const char *pghost,
3232
const char *pgport, const char *pguser,
33+
enum trivalue prompt_password, const char *progname,
34+
bool fail_ok);
35+
36+
extern PGconn *connectMaintenanceDatabase(const char *maintenance_db,
37+
const char *pghost, const char *pgport, const char *pguser,
3338
enum trivalue prompt_password, const char *progname);
3439

3540
extern PGresult *executeQuery(PGconn *conn, const char *query,

‎src/bin/scripts/createdb.c

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ main(int argc, char *argv[])
3535
{"lc-collate", required_argument, NULL, 1},
3636
{"lc-ctype", required_argument, NULL, 2},
3737
{"locale", required_argument, NULL, 'l'},
38+
{"maintenance-db", required_argument, NULL, 3},
3839
{NULL, 0, NULL, 0}
3940
};
4041

@@ -43,6 +44,7 @@ main(int argc, char *argv[])
4344
int c;
4445

4546
const char *dbname = NULL;
47+
const char *maintenance_db = NULL;
4648
char *comment = NULL;
4749
char *host = NULL;
4850
char *port = NULL;
@@ -110,6 +112,9 @@ main(int argc, char *argv[])
110112
case 'l':
111113
locale = optarg;
112114
break;
115+
case 3:
116+
maintenance_db = optarg;
117+
break;
113118
default:
114119
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
115120
exit(1);
@@ -192,13 +197,12 @@ main(int argc, char *argv[])
192197

193198
appendPQExpBuffer(&sql, ";\n");
194199

195-
/*
196-
* Connect to the 'postgres' database by default, except have the
197-
* 'postgres' user use 'template1' so he can create the 'postgres'
198-
* database.
199-
*/
200-
conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
201-
host, port, username, prompt_password, progname);
200+
/* No point in trying to use postgres db when creating postgres db. */
201+
if (maintenance_db == NULL && strcmp(dbname, "postgres") == 0)
202+
maintenance_db = "template1";
203+
204+
conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
205+
prompt_password, progname);
202206

203207
if (echo)
204208
printf("%s", sql.data);
@@ -264,6 +268,7 @@ help(const char *progname)
264268
printf(_(" -U, --username=USERNAME user name to connect as\n"));
265269
printf(_(" -w, --no-password never prompt for password\n"));
266270
printf(_(" -W, --password force password prompt\n"));
271+
printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
267272
printf(_("\nBy default, a database with the same name as the current user is created.\n"));
268273
printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
269274
}

‎src/bin/scripts/createlang.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -132,7 +132,7 @@ main(int argc, char *argv[])
132132
static const bool translate_columns[] = {false, true};
133133

134134
conn = connectDatabase(dbname, host, port, username, prompt_password,
135-
progname);
135+
progname, false);
136136

137137
printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", "
138138
"(CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as \"%s\" "
@@ -169,7 +169,8 @@ main(int argc, char *argv[])
169169
if (*p >= 'A' && *p <= 'Z')
170170
*p += ('a' - 'A');
171171

172-
conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
172+
conn = connectDatabase(dbname, host, port, username, prompt_password,
173+
progname, false);
173174

174175
/*
175176
* Make sure the language isn't already installed

‎src/bin/scripts/createuser.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -230,7 +230,8 @@ main(int argc, char *argv[])
230230
if (login == 0)
231231
login = TRI_YES;
232232

233-
conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
233+
conn = connectDatabase("postgres", host, port, username, prompt_password,
234+
progname, false);
234235

235236
initPQExpBuffer(&sql);
236237

‎src/bin/scripts/dropdb.c

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ main(int argc, char *argv[])
3232
{"echo", no_argument, NULL, 'e'},
3333
{"interactive", no_argument, NULL, 'i'},
3434
{"if-exists", no_argument, &if_exists, 1},
35+
{"maintenance-db", required_argument, NULL, 2},
3536
{NULL, 0, NULL, 0}
3637
};
3738

@@ -40,6 +41,7 @@ main(int argc, char *argv[])
4041
int c;
4142

4243
char *dbname = NULL;
44+
char *maintenance_db = NULL;
4345
char *host = NULL;
4446
char *port = NULL;
4547
char *username = NULL;
@@ -85,6 +87,9 @@ main(int argc, char *argv[])
8587
case 0:
8688
/* this covers the long options */
8789
break;
90+
case 2:
91+
maintenance_db = optarg;
92+
break;
8893
default:
8994
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
9095
exit(1);
@@ -119,11 +124,11 @@ main(int argc, char *argv[])
119124
appendPQExpBuffer(&sql, "DROP DATABASE %s%s;\n",
120125
(if_exists ? "IF EXISTS " : ""), fmtId(dbname));
121126

122-
/*
123-
* Connect to the 'postgres' database by default, except have the
124-
* 'postgres' user use 'template1' so he can drop the 'postgres' database.
125-
*/
126-
conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
127+
/* Avoid trying to drop postgres db while we are connected to it. */
128+
if (maintenance_db == NULL && strcmp(dbname, "postgres") == 0)
129+
maintenance_db = "template1";
130+
131+
conn = connectMaintenanceDatabase(maintenance_db,
127132
host, port, username, prompt_password, progname);
128133

129134
if (echo)
@@ -161,5 +166,6 @@ help(const char *progname)
161166
printf(_(" -U, --username=USERNAME user name to connect as\n"));
162167
printf(_(" -w, --no-password never prompt for password\n"));
163168
printf(_(" -W, --password force password prompt\n"));
169+
printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
164170
printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
165171
}

0 commit comments

Comments
 (0)