REINDEX(l) SQL - Language Statements (2002-11-22) REINDEX(l)
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
Page 1 (printed 3/24/03)
REINDEX(l) SQL - Language Statements (2002-11-22) REINDEX(l)
meanwhile. REINDEX acquires exclusive lock on the
table, 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 modifications 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:
Page 2 (printed 3/24/03)
REINDEX(l) SQL - Language Statements (2002-11-22) REINDEX(l)
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.
Page 3 (printed 3/24/03)
