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