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

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