vacuumdb.c 9.06 KB
Newer Older
1 2 3 4
/*-------------------------------------------------------------------------
 *
 * vacuumdb
 *
5
 * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
6 7
 * Portions Copyright (c) 1994, Regents of the University of California
 *
8
 * $PostgreSQL: pgsql/src/bin/scripts/vacuumdb.c,v 1.38 2010/06/11 23:58:24 tgl Exp $
9 10 11 12 13 14 15 16
 *
 *-------------------------------------------------------------------------
 */

#include "postgres_fe.h"
#include "common.h"


17
static void vacuum_one_database(const char *dbname, bool full, bool verbose,
18
					bool and_analyze, bool analyze_only, bool freeze,
Bruce Momjian's avatar
Bruce Momjian committed
19
					const char *table, const char *host, const char *port,
20
					const char *username, enum trivalue prompt_password,
21
					const char *progname, bool echo);
22
static void vacuum_all_databases(bool full, bool verbose, bool and_analyze,
23
					 bool analyze_only, bool freeze,
24
					 const char *host, const char *port,
25
					 const char *username, enum trivalue prompt_password,
Bruce Momjian's avatar
Bruce Momjian committed
26
					 const char *progname, bool echo, bool quiet);
27 28 29 30 31 32 33 34 35 36 37

static void help(const char *progname);


int
main(int argc, char *argv[])
{
	static struct option long_options[] = {
		{"host", required_argument, NULL, 'h'},
		{"port", required_argument, NULL, 'p'},
		{"username", required_argument, NULL, 'U'},
38
		{"no-password", no_argument, NULL, 'w'},
39 40 41 42 43
		{"password", no_argument, NULL, 'W'},
		{"echo", no_argument, NULL, 'e'},
		{"quiet", no_argument, NULL, 'q'},
		{"dbname", required_argument, NULL, 'd'},
		{"analyze", no_argument, NULL, 'z'},
44
		{"analyze-only", no_argument, NULL, 'Z'},
45
		{"freeze", no_argument, NULL, 'F'},
46 47 48 49 50 51 52
		{"all", no_argument, NULL, 'a'},
		{"table", required_argument, NULL, 't'},
		{"full", no_argument, NULL, 'f'},
		{"verbose", no_argument, NULL, 'v'},
		{NULL, 0, NULL, 0}
	};

53
	const char *progname;
54 55 56 57 58 59 60
	int			optindex;
	int			c;

	const char *dbname = NULL;
	char	   *host = NULL;
	char	   *port = NULL;
	char	   *username = NULL;
61
	enum trivalue prompt_password = TRI_DEFAULT;
62 63
	bool		echo = false;
	bool		quiet = false;
Bruce Momjian's avatar
Bruce Momjian committed
64
	bool		and_analyze = false;
65
	bool		analyze_only = false;
66
	bool		freeze = false;
67 68 69 70 71 72
	bool		alldb = false;
	char	   *table = NULL;
	bool		full = false;
	bool		verbose = false;

	progname = get_progname(argv[0]);
73
	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
74

75 76
	handle_help_version_opts(argc, argv, "vacuumdb", help);

77
	while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fv", long_options, &optindex)) != -1)
78 79 80 81 82 83 84 85 86 87 88 89
	{
		switch (c)
		{
			case 'h':
				host = optarg;
				break;
			case 'p':
				port = optarg;
				break;
			case 'U':
				username = optarg;
				break;
90 91 92
			case 'w':
				prompt_password = TRI_NO;
				break;
93
			case 'W':
94
				prompt_password = TRI_YES;
95 96 97 98 99 100 101 102 103 104 105
				break;
			case 'e':
				echo = true;
				break;
			case 'q':
				quiet = true;
				break;
			case 'd':
				dbname = optarg;
				break;
			case 'z':
Bruce Momjian's avatar
Bruce Momjian committed
106 107
				and_analyze = true;
				break;
108
			case 'Z':
109
				analyze_only = true;
110
				break;
111 112 113
			case 'F':
				freeze = true;
				break;
114 115 116 117 118 119 120 121 122 123 124 125 126
			case 'a':
				alldb = true;
				break;
			case 't':
				table = optarg;
				break;
			case 'f':
				full = true;
				break;
			case 'v':
				verbose = true;
				break;
			default:
127
				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
128 129 130 131 132 133 134 135 136 137 138 139
				exit(1);
		}
	}

	switch (argc - optind)
	{
		case 0:
			break;
		case 1:
			dbname = argv[optind];
			break;
		default:
Bruce Momjian's avatar
Bruce Momjian committed
140
			fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
141
					progname, argv[optind + 1]);
142
			fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
143 144 145
			exit(1);
	}

146
	if (analyze_only)
Bruce Momjian's avatar
Bruce Momjian committed
147 148 149 150 151 152 153 154 155 156 157 158 159
	{
		if (full)
		{
			fprintf(stderr, _("%s: cannot use the \"full\" option when performing only analyze\n"),
					progname);
			exit(1);
		}
		if (freeze)
		{
			fprintf(stderr, _("%s: cannot use the \"freeze\" option when performing only analyze\n"),
					progname);
			exit(1);
		}
160
		/* allow 'and_analyze' with 'analyze_only' */
Bruce Momjian's avatar
Bruce Momjian committed
161 162
	}

163 164
	setup_cancel_handler();

165 166 167 168 169 170 171 172 173 174 175 176 177 178
	if (alldb)
	{
		if (dbname)
		{
			fprintf(stderr, _("%s: cannot vacuum all databases and a specific one at the same time\n"),
					progname);
			exit(1);
		}
		if (table)
		{
			fprintf(stderr, _("%s: cannot vacuum a specific table in all databases\n"),
					progname);
			exit(1);
		}
Bruce Momjian's avatar
Bruce Momjian committed
179

180
		vacuum_all_databases(full, verbose, and_analyze, analyze_only, freeze,
181
							 host, port, username, prompt_password,
182 183 184 185 186 187 188 189 190 191 192 193 194 195
							 progname, echo, quiet);
	}
	else
	{
		if (dbname == NULL)
		{
			if (getenv("PGDATABASE"))
				dbname = getenv("PGDATABASE");
			else if (getenv("PGUSER"))
				dbname = getenv("PGUSER");
			else
				dbname = get_user_name(progname);
		}

196
		vacuum_one_database(dbname, full, verbose, and_analyze, analyze_only,
Bruce Momjian's avatar
Bruce Momjian committed
197
							freeze, table,
198
							host, port, username, prompt_password,
199
							progname, echo);
200 201 202 203 204 205
	}

	exit(0);
}


206
static void
207
vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyze,
208
					bool analyze_only, bool freeze, const char *table,
209
					const char *host, const char *port,
210
					const char *username, enum trivalue prompt_password,
211
					const char *progname, bool echo)
212 213 214 215 216 217 218
{
	PQExpBufferData sql;

	PGconn	   *conn;

	initPQExpBuffer(&sql);

219 220
	conn = connectDatabase(dbname, host, port, username, prompt_password, progname);

221
	if (analyze_only)
222
	{
Bruce Momjian's avatar
Bruce Momjian committed
223
		appendPQExpBuffer(&sql, "ANALYZE");
224 225 226
		if (verbose)
			appendPQExpBuffer(&sql, " VERBOSE");
	}
Bruce Momjian's avatar
Bruce Momjian committed
227 228 229
	else
	{
		appendPQExpBuffer(&sql, "VACUUM");
230
		if (PQserverVersion(conn) >= 90000)
231 232 233 234 235 236 237
		{
			const char *paren = " (";
			const char *comma = ", ";
			const char *sep = paren;

			if (full)
			{
238
				appendPQExpBuffer(&sql, "%sFULL", sep);
239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269
				sep = comma;
			}
			if (freeze)
			{
				appendPQExpBuffer(&sql, "%sFREEZE", sep);
				sep = comma;
			}
			if (verbose)
			{
				appendPQExpBuffer(&sql, "%sVERBOSE", sep);
				sep = comma;
			}
			if (and_analyze)
			{
				appendPQExpBuffer(&sql, "%sANALYZE", sep);
				sep = comma;
			}
			if (sep != paren)
				appendPQExpBuffer(&sql, ")");
		}
		else
		{
			if (full)
				appendPQExpBuffer(&sql, " FULL");
			if (freeze)
				appendPQExpBuffer(&sql, " FREEZE");
			if (verbose)
				appendPQExpBuffer(&sql, " VERBOSE");
			if (and_analyze)
				appendPQExpBuffer(&sql, " ANALYZE");
		}
Bruce Momjian's avatar
Bruce Momjian committed
270
	}
271 272 273 274
	if (table)
		appendPQExpBuffer(&sql, " %s", table);
	appendPQExpBuffer(&sql, ";\n");

275
	if (!executeMaintenanceCommand(conn, sql.data, echo))
276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
	{
		if (table)
			fprintf(stderr, _("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
					progname, table, dbname, PQerrorMessage(conn));
		else
			fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
					progname, dbname, PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}
	PQfinish(conn);
	termPQExpBuffer(&sql);
}


291
static void
292
vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool analyze_only,
Bruce Momjian's avatar
Bruce Momjian committed
293
					 bool freeze, const char *host, const char *port,
294
					 const char *username, enum trivalue prompt_password,
Bruce Momjian's avatar
Bruce Momjian committed
295
					 const char *progname, bool echo, bool quiet)
296 297 298 299 300
{
	PGconn	   *conn;
	PGresult   *result;
	int			i;

301
	conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
302
	result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
303 304 305 306 307 308 309
	PQfinish(conn);

	for (i = 0; i < PQntuples(result); i++)
	{
		char	   *dbname = PQgetvalue(result, i, 0);

		if (!quiet)
310 311 312 313
		{
			printf(_("%s: vacuuming database \"%s\"\n"), progname, dbname);
			fflush(stdout);
		}
314

315
		vacuum_one_database(dbname, full, verbose, and_analyze, analyze_only,
Bruce Momjian's avatar
Bruce Momjian committed
316
						 freeze, NULL, host, port, username, prompt_password,
317
							progname, echo);
318 319 320 321 322 323 324 325 326 327 328 329 330 331 332
	}

	PQclear(result);
}


static void
help(const char *progname)
{
	printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
	printf(_("Usage:\n"));
	printf(_("  %s [OPTION]... [DBNAME]\n"), progname);
	printf(_("\nOptions:\n"));
	printf(_("  -a, --all                       vacuum all databases\n"));
	printf(_("  -d, --dbname=DBNAME             database to vacuum\n"));
333
	printf(_("  -e, --echo                      show the commands being sent to the server\n"));
334
	printf(_("  -f, --full                      do full vacuuming\n"));
335
	printf(_("  -F, --freeze                    freeze row transaction information\n"));
336
	printf(_("  -q, --quiet                     don't write any messages\n"));
337
	printf(_("  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table only\n"));
338
	printf(_("  -v, --verbose                   write a lot of output\n"));
339 340
	printf(_("  -z, --analyze                   update optimizer statistics\n"));
	printf(_("  -Z, --analyze-only              only update optimizer statistics\n"));
341 342 343 344 345 346
	printf(_("  --help                          show this help, then exit\n"));
	printf(_("  --version                       output version information, then exit\n"));
	printf(_("\nConnection options:\n"));
	printf(_("  -h, --host=HOSTNAME       database server host or socket directory\n"));
	printf(_("  -p, --port=PORT           database server port\n"));
	printf(_("  -U, --username=USERNAME   user name to connect as\n"));
347
	printf(_("  -w, --no-password         never prompt for password\n"));
348
	printf(_("  -W, --password            force password prompt\n"));
349 350 351
	printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
	printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
}