Skip to content

Commit fb72628

Browse files
tglsfdctglsfdc
committed
Expand description of how to use REINDEX.
1 parent 933761e commit fb72628

File tree

1 file changed

+98
-13
lines changed

1 file changed

+98
-13
lines changed

‎doc/src/sgml/ref/reindex.sgml

Lines changed: 98 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
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 $
33
Postgres documentation
44
-->
55

@@ -15,7 +15,7 @@ Postgres documentation
1515
REINDEX
1616
</refname>
1717
<refpurpose>
18-
recover a corrupted system index
18+
rebuild corrupted indexes
1919
</refpurpose>
2020
</refnamediv>
2121
<refsynopsisdiv>
@@ -49,6 +49,7 @@ REINDEX { TABLE | DATABASE | INDEX } <replaceable class="PARAMETER">name</replac
4949
<listitem>
5050
<para>
5151
Recreate 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 the indexes 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>

0 commit comments

Comments
 (0)