src/bin/scripts/vacuumdb.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * vacuumdb
   4  *
   5  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
   6  * Portions Copyright (c) 1994, Regents of the University of California
   7  *
   8  * $PostgreSQL$
   9  *
  10  *-------------------------------------------------------------------------
  11  */
  12
  13 #include "postgres_fe.h"
  14 #include "common.h"
  15
  16
  17 static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze,
  18                                         bool freeze, const char *table,
  19                                         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 analyze, bool freeze,
  23                                          const char *host, const char *port,
  24                                          const char *username, enum trivalue prompt_password,
  25                                          const char *progname, bool echo, bool quiet);
  26
  27 static void help(const char *progname);
  28
  29
  30 int
  31 main(int argc, char *argv[])
  32 {
  33         static struct option long_options[] = {
  34                 {"host", required_argument, NULL, 'h'},
  35                 {"port", required_argument, NULL, 'p'},
  36                 {"username", required_argument, NULL, 'U'},
  37                 {"no-password", no_argument, NULL, 'w'},
  38                 {"password", no_argument, NULL, 'W'},
  39                 {"echo", no_argument, NULL, 'e'},
  40                 {"quiet", no_argument, NULL, 'q'},
  41                 {"dbname", required_argument, NULL, 'd'},
  42                 {"analyze", no_argument, NULL, 'z'},
  43                 {"freeze", no_argument, NULL, 'F'},
  44                 {"all", no_argument, NULL, 'a'},
  45                 {"table", required_argument, NULL, 't'},
  46                 {"full", no_argument, NULL, 'f'},
  47                 {"verbose", no_argument, NULL, 'v'},
  48                 {NULL, 0, NULL, 0}
  49         };
  50
  51         const char *progname;
  52         int                     optindex;
  53         int                     c;
  54
  55         const char *dbname = NULL;
  56         char       *host = NULL;
  57         char       *port = NULL;
  58         char       *username = NULL;
  59         enum trivalue prompt_password = TRI_DEFAULT;
  60         bool            echo = false;
  61         bool            quiet = false;
  62         bool            analyze = false;
  63         bool            freeze = false;
  64         bool            alldb = false;
  65         char       *table = NULL;
  66         bool            full = false;
  67         bool            verbose = false;
  68
  69         progname = get_progname(argv[0]);
  70         set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
  71
  72         handle_help_version_opts(argc, argv, "vacuumdb", help);
  73
  74         while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zaFt:fv", long_options, &optindex)) != -1)
  75         {
  76                 switch (c)
  77                 {
  78                         case 'h':
  79                                 host = optarg;
  80                                 break;
  81                         case 'p':
  82                                 port = optarg;
  83                                 break;
  84                         case 'U':
  85                                 username = optarg;
  86                                 break;
  87                         case 'w':
  88                                 prompt_password = TRI_NO;
  89                                 break;
  90                         case 'W':
  91                                 prompt_password = TRI_YES;
  92                                 break;
  93                         case 'e':
  94                                 echo = true;
  95                                 break;
  96                         case 'q':
  97                                 quiet = true;
  98                                 break;
  99                         case 'd':
 100                                 dbname = optarg;
 101                                 break;
 102                         case 'z':
 103                                 analyze = true;
 104                                 break;
 105                         case 'F':
 106                                 freeze = true;
 107                                 break;
 108                         case 'a':
 109                                 alldb = true;
 110                                 break;
 111                         case 't':
 112                                 table = optarg;
 113                                 break;
 114                         case 'f':
 115                                 full = true;
 116                                 break;
 117                         case 'v':
 118                                 verbose = true;
 119                                 break;
 120                         default:
 121                                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 122                                 exit(1);
 123                 }
 124         }
 125
 126         switch (argc - optind)
 127         {
 128                 case 0:
 129                         break;
 130                 case 1:
 131                         dbname = argv[optind];
 132                         break;
 133                 default:
 134                         fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
 135                                         progname, argv[optind + 1]);
 136                         fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 137                         exit(1);
 138         }
 139
 140         setup_cancel_handler();
 141
 142         if (alldb)
 143         {
 144                 if (dbname)
 145                 {
 146                         fprintf(stderr, _("%s: cannot vacuum all databases and a specific one at the same time\n"),
 147                                         progname);
 148                         exit(1);
 149                 }
 150                 if (table)
 151                 {
 152                         fprintf(stderr, _("%s: cannot vacuum a specific table in all databases\n"),
 153                                         progname);
 154                         exit(1);
 155                 }
 156
 157                 vacuum_all_databases(full, verbose, analyze, freeze,
 158                                                          host, port, username, prompt_password,
 159                                                          progname, echo, quiet);
 160         }
 161         else
 162         {
 163                 if (dbname == NULL)
 164                 {
 165                         if (getenv("PGDATABASE"))
 166                                 dbname = getenv("PGDATABASE");
 167                         else if (getenv("PGUSER"))
 168                                 dbname = getenv("PGUSER");
 169                         else
 170                                 dbname = get_user_name(progname);
 171                 }
 172
 173                 vacuum_one_database(dbname, full, verbose, analyze, freeze, table,
 174                                                         host, port, username, prompt_password,
 175                                                         progname, echo);
 176         }
 177
 178         exit(0);
 179 }
 180
 181
 182 static void
 183 vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze,
 184                                         bool freeze, const char *table,
 185                                         const char *host, const char *port,
 186                                         const char *username, enum trivalue prompt_password,
 187                                         const char *progname, bool echo)
 188 {
 189         PQExpBufferData sql;
 190
 191         PGconn     *conn;
 192
 193         initPQExpBuffer(&sql);
 194
 195         appendPQExpBuffer(&sql, "VACUUM");
 196         if (full)
 197                 appendPQExpBuffer(&sql, " FULL");
 198         if (verbose)
 199                 appendPQExpBuffer(&sql, " VERBOSE");
 200         if (freeze)
 201                 appendPQExpBuffer(&sql, " FREEZE");
 202         if (analyze)
 203                 appendPQExpBuffer(&sql, " ANALYZE");
 204         if (table)
 205                 appendPQExpBuffer(&sql, " %s", table);
 206         appendPQExpBuffer(&sql, ";\n");
 207
 208         conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
 209         if (!executeMaintenanceCommand(conn, sql.data, echo))
 210         {
 211                 if (table)
 212                         fprintf(stderr, _("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
 213                                         progname, table, dbname, PQerrorMessage(conn));
 214                 else
 215                         fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
 216                                         progname, dbname, PQerrorMessage(conn));
 217                 PQfinish(conn);
 218                 exit(1);
 219         }
 220         PQfinish(conn);
 221         termPQExpBuffer(&sql);
 222 }
 223
 224
 225 static void
 226 vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze,
 227                                          const char *host, const char *port,
 228                                          const char *username, enum trivalue prompt_password,
 229                                          const char *progname, bool echo, bool quiet)
 230 {
 231         PGconn     *conn;
 232         PGresult   *result;
 233         int                     i;
 234
 235         conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
 236         result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
 237         PQfinish(conn);
 238
 239         for (i = 0; i < PQntuples(result); i++)
 240         {
 241                 char       *dbname = PQgetvalue(result, i, 0);
 242
 243                 if (!quiet)
 244                 {
 245                         printf(_("%s: vacuuming database \"%s\"\n"), progname, dbname);
 246                         fflush(stdout);
 247                 }
 248
 249                 vacuum_one_database(dbname, full, verbose, analyze, freeze, NULL,
 250                                                         host, port, username, prompt_password,
 251                                                         progname, echo);
 252         }
 253
 254         PQclear(result);
 255 }
 256
 257
 258 static void
 259 help(const char *progname)
 260 {
 261         printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
 262         printf(_("Usage:\n"));
 263         printf(_("  %s [OPTION]... [DBNAME]\n"), progname);
 264         printf(_("\nOptions:\n"));
 265         printf(_("  -a, --all                       vacuum all databases\n"));
 266         printf(_("  -d, --dbname=DBNAME             database to vacuum\n"));
 267         printf(_("  -e, --echo                      show the commands being sent to the server\n"));
 268         printf(_("  -f, --full                      do full vacuuming\n"));
 269         printf(_("  -F, --freeze                    freeze row transaction information\n"));
 270         printf(_("  -q, --quiet                     don't write any messages\n"));
 271         printf(_("  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table only\n"));
 272         printf(_("  -v, --verbose                   write a lot of output\n"));
 273         printf(_("  -z, --analyze                   update optimizer hints\n"));
 274         printf(_("  --help                          show this help, then exit\n"));
 275         printf(_("  --version                       output version information, then exit\n"));
 276         printf(_("\nConnection options:\n"));
 277         printf(_("  -h, --host=HOSTNAME       database server host or socket directory\n"));
 278         printf(_("  -p, --port=PORT           database server port\n"));
 279         printf(_("  -U, --username=USERNAME   user name to connect as\n"));
 280         printf(_("  -w, --no-password         never prompt for password\n"));
 281         printf(_("  -W, --password            force password prompt\n"));
 282         printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
 283         printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
 284 }