granicus.if.org Git - postgresql/blob - src/bin/scripts/vacuumdb.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * vacuumdb
   4  *
   5  * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
   6  * Portions Copyright (c) 1994, Regents of the University of California
   7  *
   8  * src/bin/scripts/vacuumdb.c
   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,
  18                                         bool and_analyze, bool analyze_only, bool freeze,
  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 *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                 {"analyze", no_argument, NULL, 'z'},
  45                 {"analyze-only", no_argument, NULL, 'Z'},
  46                 {"freeze", no_argument, NULL, 'F'},
  47                 {"all", no_argument, NULL, 'a'},
  48                 {"table", required_argument, NULL, 't'},
  49                 {"full", no_argument, NULL, 'f'},
  50                 {"verbose", no_argument, NULL, 'v'},
  51                 {"maintenance-db", required_argument, NULL, 2},
  52                 {NULL, 0, NULL, 0}
  53         };
  54
  55         const char *progname;
  56         int                     optindex;
  57         int                     c;
  58
  59         const char *dbname = NULL;
  60         const char *maintenance_db = NULL;
  61         char       *host = NULL;
  62         char       *port = NULL;
  63         char       *username = NULL;
  64         enum trivalue prompt_password = TRI_DEFAULT;
  65         bool            echo = false;
  66         bool            quiet = false;
  67         bool            and_analyze = false;
  68         bool            analyze_only = false;
  69         bool            freeze = false;
  70         bool            alldb = false;
  71         char       *table = NULL;
  72         bool            full = false;
  73         bool            verbose = false;
  74
  75         progname = get_progname(argv[0]);
  76         set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
  77
  78         handle_help_version_opts(argc, argv, "vacuumdb", help);
  79
  80         while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fv", long_options, &optindex)) != -1)
  81         {
  82                 switch (c)
  83                 {
  84                         case 'h':
  85                                 host = pg_strdup(optarg);
  86                                 break;
  87                         case 'p':
  88                                 port = pg_strdup(optarg);
  89                                 break;
  90                         case 'U':
  91                                 username = pg_strdup(optarg);
  92                                 break;
  93                         case 'w':
  94                                 prompt_password = TRI_NO;
  95                                 break;
  96                         case 'W':
  97                                 prompt_password = TRI_YES;
  98                                 break;
  99                         case 'e':
 100                                 echo = true;
 101                                 break;
 102                         case 'q':
 103                                 quiet = true;
 104                                 break;
 105                         case 'd':
 106                                 dbname = pg_strdup(optarg);
 107                                 break;
 108                         case 'z':
 109                                 and_analyze = true;
 110                                 break;
 111                         case 'Z':
 112                                 analyze_only = true;
 113                                 break;
 114                         case 'F':
 115                                 freeze = true;
 116                                 break;
 117                         case 'a':
 118                                 alldb = true;
 119                                 break;
 120                         case 't':
 121                                 table = pg_strdup(optarg);
 122                                 break;
 123                         case 'f':
 124                                 full = true;
 125                                 break;
 126                         case 'v':
 127                                 verbose = true;
 128                                 break;
 129                         case 2:
 130                                 maintenance_db = pg_strdup(optarg);
 131                                 break;
 132                         default:
 133                                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 134                                 exit(1);
 135                 }
 136         }
 137
 138
 139         /*
 140          * Non-option argument specifies database name as long as it wasn't
 141          * already specified with -d / --dbname
 142          */
 143         if (optind < argc && dbname == NULL)
 144         {
 145                 dbname = argv[optind];
 146                 optind++;
 147         }
 148
 149         if (optind < argc)
 150         {
 151                 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
 152                                 progname, argv[optind]);
 153                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 154                 exit(1);
 155         }
 156
 157         if (analyze_only)
 158         {
 159                 if (full)
 160                 {
 161                         fprintf(stderr, _("%s: cannot use the \"full\" option when performing only analyze\n"),
 162                                         progname);
 163                         exit(1);
 164                 }
 165                 if (freeze)
 166                 {
 167                         fprintf(stderr, _("%s: cannot use the \"freeze\" option when performing only analyze\n"),
 168                                         progname);
 169                         exit(1);
 170                 }
 171                 /* allow 'and_analyze' with 'analyze_only' */
 172         }
 173
 174         setup_cancel_handler();
 175
 176         if (alldb)
 177         {
 178                 if (dbname)
 179                 {
 180                         fprintf(stderr, _("%s: cannot vacuum all databases and a specific one at the same time\n"),
 181                                         progname);
 182                         exit(1);
 183                 }
 184                 if (table)
 185                 {
 186                         fprintf(stderr, _("%s: cannot vacuum a specific table in all databases\n"),
 187                                         progname);
 188                         exit(1);
 189                 }
 190
 191                 vacuum_all_databases(full, verbose, and_analyze, analyze_only, freeze,
 192                                                          maintenance_db, host, port, username,
 193                                                          prompt_password, progname, echo, quiet);
 194         }
 195         else
 196         {
 197                 if (dbname == NULL)
 198                 {
 199                         if (getenv("PGDATABASE"))
 200                                 dbname = getenv("PGDATABASE");
 201                         else if (getenv("PGUSER"))
 202                                 dbname = getenv("PGUSER");
 203                         else
 204                                 dbname = get_user_name(progname);
 205                 }
 206
 207                 vacuum_one_database(dbname, full, verbose, and_analyze, analyze_only,
 208                                                         freeze, table,
 209                                                         host, port, username, prompt_password,
 210                                                         progname, echo);
 211         }
 212
 213         exit(0);
 214 }
 215
 216
 217 static void
 218 vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyze,
 219                                         bool analyze_only, bool freeze, const char *table,
 220                                         const char *host, const char *port,
 221                                         const char *username, enum trivalue prompt_password,
 222                                         const char *progname, bool echo)
 223 {
 224         PQExpBufferData sql;
 225
 226         PGconn     *conn;
 227
 228         initPQExpBuffer(&sql);
 229
 230         conn = connectDatabase(dbname, host, port, username, prompt_password,
 231                                                    progname, false);
 232
 233         if (analyze_only)
 234         {
 235                 appendPQExpBuffer(&sql, "ANALYZE");
 236                 if (verbose)
 237                         appendPQExpBuffer(&sql, " VERBOSE");
 238         }
 239         else
 240         {
 241                 appendPQExpBuffer(&sql, "VACUUM");
 242                 if (PQserverVersion(conn) >= 90000)
 243                 {
 244                         const char *paren = " (";
 245                         const char *comma = ", ";
 246                         const char *sep = paren;
 247
 248                         if (full)
 249                         {
 250                                 appendPQExpBuffer(&sql, "%sFULL", sep);
 251                                 sep = comma;
 252                         }
 253                         if (freeze)
 254                         {
 255                                 appendPQExpBuffer(&sql, "%sFREEZE", sep);
 256                                 sep = comma;
 257                         }
 258                         if (verbose)
 259                         {
 260                                 appendPQExpBuffer(&sql, "%sVERBOSE", sep);
 261                                 sep = comma;
 262                         }
 263                         if (and_analyze)
 264                         {
 265                                 appendPQExpBuffer(&sql, "%sANALYZE", sep);
 266                                 sep = comma;
 267                         }
 268                         if (sep != paren)
 269                                 appendPQExpBuffer(&sql, ")");
 270                 }
 271                 else
 272                 {
 273                         if (full)
 274                                 appendPQExpBuffer(&sql, " FULL");
 275                         if (freeze)
 276                                 appendPQExpBuffer(&sql, " FREEZE");
 277                         if (verbose)
 278                                 appendPQExpBuffer(&sql, " VERBOSE");
 279                         if (and_analyze)
 280                                 appendPQExpBuffer(&sql, " ANALYZE");
 281                 }
 282         }
 283         if (table)
 284                 appendPQExpBuffer(&sql, " %s", table);
 285         appendPQExpBuffer(&sql, ";\n");
 286
 287         if (!executeMaintenanceCommand(conn, sql.data, echo))
 288         {
 289                 if (table)
 290                         fprintf(stderr, _("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
 291                                         progname, table, dbname, PQerrorMessage(conn));
 292                 else
 293                         fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
 294                                         progname, dbname, PQerrorMessage(conn));
 295                 PQfinish(conn);
 296                 exit(1);
 297         }
 298         PQfinish(conn);
 299         termPQExpBuffer(&sql);
 300 }
 301
 302
 303 static void
 304 vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool analyze_only,
 305                                          bool freeze, const char *maintenance_db,
 306                                          const char *host, const char *port,
 307                                          const char *username, enum trivalue prompt_password,
 308                                          const char *progname, bool echo, bool quiet)
 309 {
 310         PGconn     *conn;
 311         PGresult   *result;
 312         int                     i;
 313
 314         conn = connectMaintenanceDatabase(maintenance_db, host, port,
 315                                                                           username, prompt_password, progname);
 316         result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
 317         PQfinish(conn);
 318
 319         for (i = 0; i < PQntuples(result); i++)
 320         {
 321                 char       *dbname = PQgetvalue(result, i, 0);
 322
 323                 if (!quiet)
 324                 {
 325                         printf(_("%s: vacuuming database \"%s\"\n"), progname, dbname);
 326                         fflush(stdout);
 327                 }
 328
 329                 vacuum_one_database(dbname, full, verbose, and_analyze, analyze_only,
 330                                                  freeze, NULL, host, port, username, prompt_password,
 331                                                         progname, echo);
 332         }
 333
 334         PQclear(result);
 335 }
 336
 337
 338 static void
 339 help(const char *progname)
 340 {
 341         printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
 342         printf(_("Usage:\n"));
 343         printf(_("  %s [OPTION]... [DBNAME]\n"), progname);
 344         printf(_("\nOptions:\n"));
 345         printf(_("  -a, --all                       vacuum all databases\n"));
 346         printf(_("  -d, --dbname=DBNAME             database to vacuum\n"));
 347         printf(_("  -e, --echo                      show the commands being sent to the server\n"));
 348         printf(_("  -f, --full                      do full vacuuming\n"));
 349         printf(_("  -F, --freeze                    freeze row transaction information\n"));
 350         printf(_("  -q, --quiet                     don't write any messages\n"));
 351         printf(_("  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table only\n"));
 352         printf(_("  -v, --verbose                   write a lot of output\n"));
 353         printf(_("  -V, --version                   output version information, then exit\n"));
 354         printf(_("  -z, --analyze                   update optimizer statistics\n"));
 355         printf(_("  -Z, --analyze-only              only update optimizer statistics\n"));
 356         printf(_("  -?, --help                      show this help, then exit\n"));
 357         printf(_("\nConnection options:\n"));
 358         printf(_("  -h, --host=HOSTNAME       database server host or socket directory\n"));
 359         printf(_("  -p, --port=PORT           database server port\n"));
 360         printf(_("  -U, --username=USERNAME   user name to connect as\n"));
 361         printf(_("  -w, --no-password         never prompt for password\n"));
 362         printf(_("  -W, --password            force password prompt\n"));
 363         printf(_("  --maintenance-db=DBNAME   alternate maintenance database\n"));
 364         printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
 365         printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
 366 }