src/bin/scripts/reindexdb.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * reindexdb
   4  *
   5  * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
   6  *
   7  * $PostgreSQL$
   8  *
   9  *-------------------------------------------------------------------------
  10  */
  11
  12 #include "postgres_fe.h"
  13 #include "common.h"
  14 #include "dumputils.h"
  15
  16
  17 static void reindex_one_database(const char *name, const char *dbname,
  18                                          const char *type, const char *host,
  19                                          const char *port, const char *username,
  20                                          bool password, const char *progname,
  21                                          bool echo);
  22 static void reindex_all_databases(const char *host, const char *port,
  23                                           const char *username, bool password,
  24                                           const char *progname, bool echo,
  25                                           bool quiet);
  26 static void reindex_system_catalogs(const char *dbname,
  27                                                 const char *host, const char *port,
  28                                                 const char *username, bool password,
  29                                                 const char *progname, bool echo);
  30 static void help(const char *progname);
  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                 {"password", no_argument, NULL, 'W'},
  40                 {"echo", no_argument, NULL, 'e'},
  41                 {"quiet", no_argument, NULL, 'q'},
  42                 {"dbname", required_argument, NULL, 'd'},
  43                 {"all", no_argument, NULL, 'a'},
  44                 {"system", no_argument, NULL, 's'},
  45                 {"table", required_argument, NULL, 't'},
  46                 {"index", required_argument, NULL, 'i'},
  47                 {NULL, 0, NULL, 0}
  48         };
  49
  50         const char *progname;
  51         int                     optindex;
  52         int                     c;
  53
  54         const char *dbname = NULL;
  55         const char *host = NULL;
  56         const char *port = NULL;
  57         const char *username = NULL;
  58         bool            password = false;
  59         bool            syscatalog = false;
  60         bool            alldb = false;
  61         bool            echo = false;
  62         bool            quiet = false;
  63         const char *table = NULL;
  64         const char *index = NULL;
  65
  66         progname = get_progname(argv[0]);
  67         set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
  68
  69         handle_help_version_opts(argc, argv, "reindexdb", help);
  70
  71         /* process command-line options */
  72         while ((c = getopt_long(argc, argv, "h:p:U:Weqd:ast:i:", long_options, &optindex)) != -1)
  73         {
  74                 switch (c)
  75                 {
  76                         case 'h':
  77                                 host = optarg;
  78                                 break;
  79                         case 'p':
  80                                 port = optarg;
  81                                 break;
  82                         case 'U':
  83                                 username = optarg;
  84                                 break;
  85                         case 'W':
  86                                 password = true;
  87                                 break;
  88                         case 'e':
  89                                 echo = true;
  90                                 break;
  91                         case 'q':
  92                                 quiet = true;
  93                                 break;
  94                         case 'd':
  95                                 dbname = optarg;
  96                                 break;
  97                         case 'a':
  98                                 alldb = true;
  99                                 break;
 100                         case 's':
 101                                 syscatalog = true;
 102                                 break;
 103                         case 't':
 104                                 table = optarg;
 105                                 break;
 106                         case 'i':
 107                                 index = optarg;
 108                                 break;
 109                         default:
 110                                 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 111                                 exit(1);
 112                 }
 113         }
 114
 115         switch (argc - optind)
 116         {
 117                 case 0:
 118                         break;
 119                 case 1:
 120                         dbname = argv[optind];
 121                         break;
 122                 default:
 123                         fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind + 1]);
 124                         fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 125                         exit(1);
 126         }
 127
 128         setup_cancel_handler();
 129
 130         if (alldb)
 131         {
 132                 if (dbname)
 133                 {
 134                         fprintf(stderr, _("%s: cannot reindex all databases and a specific one at the same time\n"), progname);
 135                         exit(1);
 136                 }
 137                 if (syscatalog)
 138                 {
 139                         fprintf(stderr, _("%s: cannot reindex all databases and system catalogs at the same time\n"), progname);
 140                         exit(1);
 141                 }
 142                 if (table)
 143                 {
 144                         fprintf(stderr, _("%s: cannot reindex a specific table in all databases\n"), progname);
 145                         exit(1);
 146                 }
 147                 if (index)
 148                 {
 149                         fprintf(stderr, _("%s: cannot reindex a specific index in all databases\n"), progname);
 150                         exit(1);
 151                 }
 152
 153                 reindex_all_databases(host, port, username, password,
 154                                                           progname, echo, quiet);
 155         }
 156         else if (syscatalog)
 157         {
 158                 if (table)
 159                 {
 160                         fprintf(stderr, _("%s: cannot reindex a specific table and system catalogs at the same time\n"), progname);
 161                         exit(1);
 162                 }
 163                 if (index)
 164                 {
 165                         fprintf(stderr, _("%s: cannot reindex a specific index and system catalogs at the same time\n"), progname);
 166                         exit(1);
 167                 }
 168
 169                 if (dbname == NULL)
 170                 {
 171                         if (getenv("PGDATABASE"))
 172                                 dbname = getenv("PGDATABASE");
 173                         else if (getenv("PGUSER"))
 174                                 dbname = getenv("PGUSER");
 175                         else
 176                                 dbname = get_user_name(progname);
 177                 }
 178
 179                 reindex_system_catalogs(dbname, host, port, username, password,
 180                                                                 progname, echo);
 181         }
 182         else
 183         {
 184                 if (dbname == NULL)
 185                 {
 186                         if (getenv("PGDATABASE"))
 187                                 dbname = getenv("PGDATABASE");
 188                         else if (getenv("PGUSER"))
 189                                 dbname = getenv("PGUSER");
 190                         else
 191                                 dbname = get_user_name(progname);
 192                 }
 193
 194                 if (index)
 195                         reindex_one_database(index, dbname, "INDEX", host, port,
 196                                                                  username, password, progname, echo);
 197                 if (table)
 198                         reindex_one_database(table, dbname, "TABLE", host, port,
 199                                                                  username, password, progname, echo);
 200                 /* reindex database only if index or table is not specified */
 201                 if (index == NULL && table == NULL)
 202                         reindex_one_database(dbname, dbname, "DATABASE", host, port,
 203                                                                  username, password, progname, echo);
 204         }
 205
 206         exit(0);
 207 }
 208
 209 static void
 210 reindex_one_database(const char *name, const char *dbname, const char *type,
 211                                          const char *host, const char *port, const char *username,
 212                                          bool password, const char *progname, bool echo)
 213 {
 214         PQExpBufferData sql;
 215
 216         PGconn     *conn;
 217
 218         initPQExpBuffer(&sql);
 219
 220         appendPQExpBuffer(&sql, "REINDEX");
 221         if (strcmp(type, "TABLE") == 0)
 222                 appendPQExpBuffer(&sql, " TABLE %s", fmtId(name));
 223         else if (strcmp(type, "INDEX") == 0)
 224                 appendPQExpBuffer(&sql, " INDEX %s", fmtId(name));
 225         else if (strcmp(type, "DATABASE") == 0)
 226                 appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
 227         appendPQExpBuffer(&sql, ";\n");
 228
 229         conn = connectDatabase(dbname, host, port, username, password, progname);
 230
 231         if (!executeMaintenanceCommand(conn, sql.data, echo))
 232         {
 233                 if (strcmp(type, "TABLE") == 0)
 234                         fprintf(stderr, _("%s: reindexing of table \"%s\" in database \"%s\" failed: %s"),
 235                                         progname, name, dbname, PQerrorMessage(conn));
 236                 if (strcmp(type, "INDEX") == 0)
 237                         fprintf(stderr, _("%s: reindexing of index \"%s\" in database \"%s\" failed: %s"),
 238                                         progname, name, dbname, PQerrorMessage(conn));
 239                 else
 240                         fprintf(stderr, _("%s: reindexing of database \"%s\" failed: %s"),
 241                                         progname, dbname, PQerrorMessage(conn));
 242                 PQfinish(conn);
 243                 exit(1);
 244         }
 245
 246         PQfinish(conn);
 247         termPQExpBuffer(&sql);
 248 }
 249
 250 static void
 251 reindex_all_databases(const char *host, const char *port,
 252                                           const char *username, bool password,
 253                                           const char *progname, bool echo, bool quiet)
 254 {
 255         PGconn     *conn;
 256         PGresult   *result;
 257         int                     i;
 258
 259         conn = connectDatabase("postgres", host, port, username, password, progname);
 260         result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", progname, echo);
 261         PQfinish(conn);
 262
 263         for (i = 0; i < PQntuples(result); i++)
 264         {
 265                 char       *dbname = PQgetvalue(result, i, 0);
 266
 267                 if (!quiet)
 268                 {
 269                         printf(_("%s: reindexing database \"%s\"\n"), progname, dbname);
 270                         fflush(stdout);
 271                 }
 272
 273                 reindex_one_database(dbname, dbname, "DATABASE", host, port, username,
 274                                                          password, progname, echo);
 275         }
 276
 277         PQclear(result);
 278 }
 279
 280 static void
 281 reindex_system_catalogs(const char *dbname, const char *host, const char *port,
 282                                                 const char *username, bool password,
 283                                                 const char *progname, bool echo)
 284 {
 285         PQExpBufferData sql;
 286
 287         PGconn     *conn;
 288
 289         initPQExpBuffer(&sql);
 290
 291         appendPQExpBuffer(&sql, "REINDEX SYSTEM %s;\n", dbname);
 292
 293         conn = connectDatabase(dbname, host, port, username, password, progname);
 294         if (!executeMaintenanceCommand(conn, sql.data, echo))
 295         {
 296                 fprintf(stderr, _("%s: reindexing of system catalogs failed: %s"),
 297                                 progname, PQerrorMessage(conn));
 298                 PQfinish(conn);
 299                 exit(1);
 300         }
 301         PQfinish(conn);
 302         termPQExpBuffer(&sql);
 303 }
 304
 305 static void
 306 help(const char *progname)
 307 {
 308         printf(_("%s reindexes a PostgreSQL database.\n\n"), progname);
 309         printf(_("Usage:\n"));
 310         printf(_("  %s [OPTION]... [DBNAME]\n"), progname);
 311         printf(_("\nOptions:\n"));
 312         printf(_("  -a, --all                 reindex all databases\n"));
 313         printf(_("  -d, --dbname=DBNAME       database to reindex\n"));
 314         printf(_("  -e, --echo                show the commands being sent to the server\n"));
 315         printf(_("  -i, --index=INDEX         recreate specific index only\n"));
 316         printf(_("  -q, --quiet               don't write any messages\n"));
 317         printf(_("  -s, --system              reindex system catalogs\n"));
 318         printf(_("  -t, --table=TABLE         reindex specific table only\n"));
 319         printf(_("  --help                    show this help, then exit\n"));
 320         printf(_("  --version                 output version information, then exit\n"));
 321         printf(_("\nConnection options:\n"));
 322         printf(_("  -h, --host=HOSTNAME       database server host or socket directory\n"));
 323         printf(_("  -p, --port=PORT           database server port\n"));
 324         printf(_("  -U, --username=USERNAME   user name to connect as\n"));
 325         printf(_("  -W, --password            force password prompt\n"));
 326         printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
 327         printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
 328 }