Index for Section 5

Alphabetical listing for R

Bottom of page

REINDEX(5)
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 be reindexed.

  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.

  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 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 startup, 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 readline processing (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.

Index for Section 5

Alphabetical listing for R

Top of page