REINDEX(7)							   SQL Commands 							REINDEX(7)

NAME

       REINDEX - rebuild corrupted indexes

SYNOPSIS

       REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

   INPUTS
       TABLE  Recreate all indexes of a specified table.

       DATABASE
	      Recreate all system indexes of a specified database.  (User-table indexes are not included.)

       INDEX  Recreate a specified index.

       name   The name of the specific table/database/index to be reindexed.  Table and index names may be schema-qualified.

       FORCE  Force rebuild of system indexes. Without this keyword REINDEX skips system indexes that are not marked invalid.  FORCE is irrelevant
	      for REINDEX INDEX, or when reindexing user indexes.

   OUTPUTS
       REINDEX
	      Message returned if the table is successfully reindexed.

DESCRIPTION

       REINDEX is used to rebuild corrupted indexes.  Although in theory this should never be necessary, in practice indexes may become  corrupted
       due to software bugs or hardware failures. REINDEX provides a recovery method.

       REINDEX also removes certain dead index pages that can't be reclaimed any other way. See the "Routine Reindexing" section in the manual for
       more information.

       If you suspect corruption of an index on a user table, you can simply rebuild that index, or all indexes on the table, using REINDEX  INDEX
       or REINDEX TABLE.

	      Note:  Another approach to dealing with a corrupted user-table index is just to drop and recreate it. This may in fact be preferable
	      if you would like to maintain some semblance of normal operation on the table meanwhile. REINDEX acquires exclusive lock on the  ta-
	      ble, while CREATE INDEX only locks out writes not reads of the table.

       Things are more difficult if you need to recover from corruption of an index on a system table. In this case it's important for the backend
       doing the recovery to not have used any of the suspect indexes itself.  (Indeed, in this sort of scenario you may find  that  backends  are
       crashing  immediately  at  start-up,  due  to reliance on the corrupted indexes.) To recover safely, the postmaster must be shut down and a
       stand-alone PostgreSQL backend must be started instead, giving it the command-line options -O and -P (these options allow system table mod-
       ifications  and prevent use of system indexes, respectively). Then issue REINDEX INDEX, REINDEX TABLE, or REINDEX DATABASE depending on how
       much you want to reconstruct.  If in doubt, use REINDEX DATABASE FORCE to force reconstruction of all system indexes in the database.  Then
       quit the standalone backend and restart the postmaster.

       Since this is likely the only situation when most people will ever use a standalone backend, some usage notes might be in order:

       o Start the backend with a command like

	 postgres -D $PGDATA -O -P my_database

	 Provide  the  correct path to the database area with -D, or make sure that the environment variable PGDATA is set.  Also specify the name
	 of the particular database you want to work in.

       o You can issue any SQL command, not only REINDEX.

       o Be aware that the standalone backend treats newline as the command entry terminator; there is no intelligence about semicolons, as  there
	 is in psql. To continue a command across multiple lines, you must type backslash just before each newline except the last one.  Also, you
	 won't have any of the conveniences of command-line editing (no command history, for example).

       o To quit the backend, type EOF (Control+D, usually).

       See the postgres(1) reference page for more information.

USAGE

       Recreate the indexes on the table mytable:

	    REINDEX TABLE mytable;

       Rebuild a single index:

	   REINDEX INDEX my_index;

       Rebuild all system indexes (this will only work in a standalone backend):

	   REINDEX DATABASE my_database FORCE;

COMPATIBILITY

   SQL92
       There is no REINDEX in SQL92.

SQL - Language Statements					    2002-11-22								REINDEX(7)