11<!--
2- $Header: /cvsroot/pgsql/doc/src/sgml/ref/reindex.sgml,v 1.4 2001/09/03 12:57:50 petere Exp $
2+ $Header: /cvsroot/pgsql/doc/src/sgml/ref/reindex.sgml,v 1.5 2001/11/20 02:45:00 tgl Exp $
33Postgres documentation
44-->
55
@@ -15,7 +15,7 @@ Postgres documentation
1515 REINDEX
1616 </refname>
1717 <refpurpose>
18- recover a corruptedsystem index
18+ rebuild corruptedindexes
1919 </refpurpose>
2020 </refnamediv>
2121 <refsynopsisdiv>
@@ -49,6 +49,7 @@ REINDEX { TABLE | DATABASE | INDEX } <replaceable class="PARAMETER">name</replac
4949 <listitem>
5050 <para>
5151Recreate all system indexes of a specified database.
52+ (User-table indexes are not included.)
5253 </para>
5354 </listitem>
5455 </varlistentry>
@@ -72,8 +73,10 @@ REINDEX { TABLE | DATABASE | INDEX } <replaceable class="PARAMETER">name</replac
7273 <term>FORCE</term>
7374 <listitem>
7475 <para>
75- Recreate indexes forcedly. Without this keyword REINDEX does
76- nothing unless target indexes are invalidated.
76+ Force rebuild of system indexes. Without this keyword
77+ <command>REINDEX</> skips system indexes that are not marked invalid.
78+ FORCE is irrelevant for <command>REINDEX INDEX</>, or when reindexing
79+ user indexes.
7780 </para>
7881 </listitem>
7982 </varlistentry>
@@ -114,11 +117,86 @@ REINDEX
114117 Description
115118 </title>
116119 <para>
117- <command>REINDEX</command> is used to recover corrupted system indexes.
118- In order to run REINDEX command, postmaster must be shut down and
119- stand-alone Postgres should be started instead with options -O and
120- -P (an option to ignore system indexes). Note that we couldn't rely
121- on system indexes for the recovery of system indexes.
120+ <command>REINDEX</command> is used to rebuild corrupted indexes.
121+ Although in theory this should never be necessary, in practice
122+ indexes may become corrupted due to software bugs or hardware
123+ failures. <command>REINDEX</command> provides a recovery method.
124+ </para>
125+
126+ <para>
127+ If you suspect corruption of an index on a user table, you can
128+ simply rebuild that index, or all indexes on the table, using
129+ <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>.
130+ </para>
131+
132+ <note>
133+ <para>
134+ Another approach to dealing with a corrupted user-table index is
135+ just to drop and recreate it. This may in fact be preferable if
136+ you would like to maintain some semblance of normal operation on
137+ the table meanwhile. <command>REINDEX</> acquires exclusive lock
138+ on the table, while <command>CREATE INDEX</> only locks out writes
139+ not reads of the table.
140+ </para>
141+ </note>
142+
143+ <para>
144+ Things are more difficult if you need to recover from corruption of an
145+ index on a system table. In this case it's important for the backend
146+ doing the recovery to not have used any of the suspect indexes itself.
147+ (Indeed, in this sort of scenario you may find that backends are
148+ crashing immediately at startup, due to reliance on the corrupted
149+ indexes.) To recover safely, the postmaster must be shut down and a
150+ stand-alone Postgres backend must be started instead, giving it
151+ the command-line options -O and -P (these options allow system table
152+ modifications and prevent use of system indexes, respectively). Then
153+ issue <command>REINDEX INDEX</>, <command>REINDEX TABLE</>, or
154+ <command>REINDEX DATABASE</> depending on how much you want to reconstruct.
155+ If in doubt, use <command>REINDEX DATABASE FORCE</> to force reconstruction
156+ of all system indexes in the database. Then quit the standalone backend
157+ and restart the postmaster.
158+ </para>
159+
160+ <para>
161+ Since this is likely the only situation when most people will ever use
162+ a standalone backend, some usage notes might be in order:
163+
164+ <itemizedlist>
165+ <listitem>
166+ <para>
167+ Start the backend with a command like
168+ <screen>
169+ <userinput>postgres -D $PGDATA -O -P my_database</userinput>
170+ </screen>
171+ Provide the correct path to the database area with <option>-D</>, or
172+ make sure that the environment variable <envar>PGDATA</> is set.
173+ Also specify the name of the particular database you want to work in.
174+ </para>
175+ </listitem>
176+
177+ <listitem>
178+ <para>
179+ You can issue any SQL command, not only <command>REINDEX</>.
180+ </para>
181+ </listitem>
182+
183+ <listitem>
184+ <para>
185+ Be aware that the standalone backend treats newline as the command
186+ entry terminator, not semicolon; you can't continue commands across
187+ lines, as you can in <application>psql</>.
188+ Also, you won't have any of the conveniences of readline processing
189+ (no command history, for example).
190+ </para>
191+ </listitem>
192+
193+ <listitem>
194+ <para>
195+ To quit the backend, type EOF (control-D, usually).
196+ </para>
197+ </listitem>
198+
199+ </itemizedlist>
122200 </para>
123201 </refsect1>
124202
@@ -127,19 +205,26 @@ REINDEX
127205 Usage
128206 </title>
129207 <para>
130- Recreate the table <literal>mytable</literal>:
208+ Recreate theindexes on the table <literal>mytable</literal>:
131209
132210 <programlisting>
133211 REINDEX TABLE mytable;
134212 </programlisting>
135213 </para>
136214
137215 <para>
138- Some more examples:
216+ Rebuild a single index:
217+
218+ <programlisting>
219+ REINDEX INDEX my_index;
220+ </programlisting>
221+ </para>
222+
223+ <para>
224+ Rebuild all system indexes (this will only work in a standalone backend):
139225
140226 <programlisting>
141- REINDEX DATABASE my_database FORCE;
142- REINDEX INDEX my_index;
227+ REINDEX DATABASE my_database FORCE;
143228 </programlisting>
144229 </para>
145230 </refsect1>