From a56a016ceb612cdee1ddc5990682f36d541e5b07 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 24 Sep 2003 18:54:02 +0000 Subject: Repair some REINDEX problems per recent discussions. The relcache is now able to cope with assigning new relfilenode values to nailed-in-cache indexes, so they can be reindexed using the fully crash-safe method. This leaves only shared system indexes as special cases. Remove the 'index deactivation' code, since it provides no useful protection in the shared- index case. Require reindexing of shared indexes to be done in standalone mode, but remove other restrictions on REINDEX. -P (IgnoreSystemIndexes) now prevents using indexes for lookups, but does not disable index updates. It is therefore safe to allow from PGOPTIONS. Upshot: reindexing system catalogs can be done without a standalone backend for all cases except shared catalogs. --- doc/src/sgml/ref/postgres-ref.sgml | 8 +-- doc/src/sgml/ref/reindex.sgml | 141 ++++++++++++++++++++++++------------- 2 files changed, 97 insertions(+), 52 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index b80c9caafac..279d8875e0e 100644 --- a/doc/src/sgml/ref/postgres-ref.sgml +++ b/doc/src/sgml/ref/postgres-ref.sgml @@ -1,5 +1,5 @@ @@ -177,9 +177,9 @@ PostgreSQL documentation - Ignore system indexes while scanning/updating system tables. The - REINDEX command for system tables/indexes - requires this option to be used. + Ignore system indexes when reading system tables (but still update + the indexes when modifying the tables). This is useful when + recovering from damaged system indexes. diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml index 29b96e462c3..d945112de79 100644 --- a/doc/src/sgml/ref/reindex.sgml +++ b/doc/src/sgml/ref/reindex.sgml @@ -1,5 +1,5 @@ @@ -56,43 +56,6 @@ REINDEX { DATABASE | TABLE | INDEX } name - - - 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. 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 - system to not have used any of the suspect indexes itself. - (Indeed, in this sort of scenario you may find that server - processes are crashing immediately at start-up, due to reliance on - the corrupted indexes.) To recover safely, the server must be shut - down and a stand-alone PostgreSQL server - must be started instead with the command-line options - and . (These options allow - system table modifications and prevent use of system indexes, - respectively.) Then, REINDEX DATABASE, - REINDEX TABLE, or REINDEX INDEX can be - issued, 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 server session and restart the real server. - - - - See the reference page for more - information about how to interact with the stand-alone server - interface. - @@ -104,8 +67,8 @@ REINDEX { DATABASE | TABLE | INDEX } name Recreate all system indexes of a specified database. Indexes on - user tables are not included. This form of REINDEX - can only be used in stand-alone mode (see above). + user tables are not processed. Also, indexes on shared system + catalogs are skipped except in stand-alone mode (see below). @@ -114,7 +77,8 @@ REINDEX { DATABASE | TABLE | INDEX } nameTABLE - Recreate all indexes of a specified table. + Recreate all indexes of a specified table. If the table has a + secondary TOAST table, that is reindexed as well. @@ -142,16 +106,93 @@ REINDEX { DATABASE | TABLE | INDEX } nameFORCE - Force rebuild of system indexes. Without this key word, - REINDEX skips system indexes that are not marked - invalid. FORCE is irrelevant for REINDEX - INDEX or when reindexing user indexes. + This is an obsolete option; it is ignored if specified. + + Notes + + + 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. 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 + system to not have used any of the suspect indexes itself. + (Indeed, in this sort of scenario you may find that server + processes are crashing immediately at start-up, due to reliance on + the corrupted indexes.) To recover safely, the server must be started + with the option, which prevents it from using + indexes for system catalog lookups. + + + + One way to do this is to shut down the postmaster and start a stand-alone + PostgreSQL server + with the option included on its command line. + Then, REINDEX DATABASE, + REINDEX TABLE, or REINDEX INDEX can be + issued, depending on how much you want to reconstruct. If in + doubt, use REINDEX DATABASE to select + reconstruction of all system indexes in the database. Then quit + the standalone server session and restart the regular server. + See the reference page for more + information about how to interact with the stand-alone server + interface. + + + + Alternatively, a regular server session can be started with + included in its command line options. + The method for doing this varies across clients, but in all + libpq-based clients, it is possible to set + the PGOPTIONS environment variable to -P + before starting the client. Note that while this method does not + require locking out other clients, it may still be wise to prevent + other users from connecting to the damaged database until repairs + have been completed. + + + + If corruption is suspected in the indexes of any of the shared + system catalogs (pg_database, + pg_group, or + pg_shadow), then a standalone server + must be used to repair it. REINDEX will not process + shared catalogs in multiuser mode. + + + + For all indexes except the shared system catalogs, REINDEX + is crash-safe and transaction-safe. REINDEX is not + crash-safe for shared indexes, which is why this case is disallowed + during normal operation. If a failure occurs while reindexing one + of these catalogs in standalone mode, it is important that the failure + be rectified and the REINDEX operation redone + before attempting to restart the regular server. + + + + Prior to PostgreSQL 7.4, REINDEX + TABLE did not automatically process TOAST tables, and so those had + to be reindexed by separate commands. This is still possible, but + redundant. + + + Examples @@ -172,11 +213,15 @@ REINDEX INDEX my_index; - Rebuild all system indexes (this will only work in a stand-alone - server session): + Rebuild all system indexes in a particular database, without trusting them + to be valid already: -REINDEX DATABASE my_database FORCE; +$ export PGOPTIONS="-P" +$ psql broken_db +... +broken_db=> REINDEX DATABASE broken_db; +broken_db=> \q -- cgit v1.2.3