1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
16 HREF="sql-commands.html"><LINK
19 HREF="sql-prepare.html"><LINK
22 HREF="sql-reset.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="sql-prepare.html"
60 HREF="sql-prepare.html"
102 >REINDEX -- rebuild indexes</DIV
107 CLASS="REFSYNOPSISDIV"
115 >REINDEX { DATABASE | TABLE | INDEX } <VAR
131 > rebuilds an index based on the data
132 stored in the table, replacing the old copy of the index. There are
133 two main reasons to use <TT
143 > An index has become corrupted, and no longer contains valid
144 data. Although in theory this should never happen, in
145 practice indexes may become corrupted due to software bugs or
146 hardware failures. <TT
155 > The index in question contains a lot of dead index pages that
156 are not being reclaimed. This can occur with B-tree indexes in
160 > under certain access
164 > provides a way to reduce
165 the space consumption of the index by writing a new version of
166 the index without the dead pages. See <A
167 HREF="routine-reindex.html"
169 > for more information.
195 > Recreate all system indexes of a specified database. Indexes on
196 user tables are not processed. Also, indexes on shared system
197 catalogs are skipped except in stand-alone mode (see below).
207 > Recreate all indexes of a specified table. If the table has a
211 > table, that is reindexed as well.
221 > Recreate a specified index.
231 > The name of the specific database, table, or index to be
232 reindexed. Table and index names may be schema-qualified.
242 > This is an obsolete option; it is ignored if specified.
256 > If you suspect corruption of an index on a user table, you can
257 simply rebuild that index, or all indexes on the table, using
265 >. Another approach to dealing with a corrupted
266 user-table index is just to drop and recreate it. This may in fact
267 be preferable if you would like to maintain some semblance of
268 normal operation on the table meanwhile. <TT
272 acquires exclusive lock on the table, while <TT
276 > only locks out writes not reads of the table.
279 > Things are more difficult if you need to recover from corruption of
280 an index on a system table. In this case it's important for the
281 system to not have used any of the suspect indexes itself.
282 (Indeed, in this sort of scenario you may find that server
283 processes are crashing immediately at start-up, due to reliance on
284 the corrupted indexes.) To recover safely, the server must be started
288 > option, which prevents it from using
289 indexes for system catalog lookups.
292 > One way to do this is to shut down the postmaster and start a stand-alone
300 > option included on its command line.
303 >REINDEX DATABASE</TT
312 issued, depending on how much you want to reconstruct. If in
315 >REINDEX DATABASE</TT
317 reconstruction of all system indexes in the database. Then quit
318 the standalone server session and restart the regular server.
320 HREF="app-postgres.html"
325 > reference page for more
326 information about how to interact with the stand-alone server
330 > Alternatively, a regular server session can be started with
334 > included in its command line options.
335 The method for doing this varies across clients, but in all
339 >-based clients, it is possible to set
343 > environment variable to <TT
347 before starting the client. Note that while this method does not
348 require locking out other clients, it may still be wise to prevent
349 other users from connecting to the damaged database until repairs
353 > If corruption is suspected in the indexes of any of the shared
365 >), then a standalone server
366 must be used to repair it. <TT
370 shared catalogs in multiuser mode.
373 > For all indexes except the shared system catalogs, <TT
377 is crash-safe and transaction-safe. <TT
381 crash-safe for shared indexes, which is why this case is disallowed
382 during normal operation. If a failure occurs while reindexing one
383 of these catalogs in standalone mode, it will not be possible to
384 restart the regular server until the problem is rectified. (The
385 typical symptom of a partially rebuilt shared index is <SPAN
399 > did not automatically process TOAST tables, and so those had
400 to be reindexed by separate commands. This is still possible, but
412 > Recreate the indexes on the table <TT
418 CLASS="PROGRAMLISTING"
419 >REINDEX TABLE my_table;</PRE
423 > Rebuild a single index:
426 CLASS="PROGRAMLISTING"
427 >REINDEX INDEX my_index;</PRE
431 > Rebuild all system indexes in a particular database, without trusting them
435 CLASS="PROGRAMLISTING"
438 >export PGOPTIONS="-P"</KBD
445 broken_db=> REINDEX DATABASE broken_db;
446 broken_db=> \q</PRE
461 > command in the SQL standard.
469 SUMMARY="Footer navigation table"
480 HREF="sql-prepare.html"
498 HREF="sql-reset.html"
514 HREF="sql-commands.html"