information about how to interact with the single-user server
interface.
</para>
<para>
Alternatively, a regular server session can be started with
<option>-P</option> included in its command line options.
The method for doing this varies across clients, but in all
<application>libpq</application>-based clients, it is possible to set
the <envar>PGOPTIONS</envar> environment variable to <literal>-P</literal>
before starting the client. Note that while this method does not
require locking out other clients, it might still be wise to prevent
other users from connecting to the damaged database until repairs
have been completed.
</para>
<para>
<command>REINDEX</command> is similar to a drop and recreate of the index
in that the index contents are rebuilt from scratch. However, the locking
considerations are rather different. <command>REINDEX</command> locks out writes
but not reads of the index's parent table. It also takes an
<literal>ACCESS EXCLUSIVE</literal> lock on the specific index being processed,
which will block reads that attempt to use that index. In particular,
the query planner tries to take an <literal>ACCESS SHARE</literal>
lock on every index of the table, regardless of the query, and so
<command>REINDEX</command> blocks virtually any queries except for some
prepared queries whose plan has been cached and which don't use this very
index. In contrast,
<command>DROP INDEX</command> momentarily takes an
<literal>ACCESS EXCLUSIVE</literal> lock on the parent table, blocking both
writes and reads. The subsequent <command>CREATE INDEX</command> locks out
writes but not reads; since the index is not there, no read will attempt to
use it, meaning that there will be no blocking but reads might be forced
into expensive sequential scans.
</para>
<para>
While <command>REINDEX</command> is running, the <xref
linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
pg_temp</literal>.
</para>
<para>
Reindexing a single index or table requires
having the <literal>MAINTAIN</literal> privilege on the
table. Note that while <command>REINDEX</command> on a partitioned index or
table requires having the <literal>MAINTAIN</literal> privilege on the
partitioned table, such commands skip the privilege checks when processing
the individual partitions. Reindexing a schema or database requires being the
owner of that schema or database or having privileges of the
<xref linkend="predefined-role-pg-maintain"/>
role. Note specifically that it's thus
possible for non-superusers to rebuild indexes of tables owned by
other users. However, as a special exception,
<command>REINDEX DATABASE</command>, <command>REINDEX SCHEMA</command>,
and <command>REINDEX SYSTEM</command> will skip indexes on shared catalogs
unless the user has the <literal>MAINTAIN</literal> privilege on the
catalog.
</para>
<para>
Reindexing partitioned indexes or partitioned tables is supported
with <command>REINDEX INDEX</command> or <command>REINDEX TABLE</command>,
respectively. Each partition of the specified partitioned relation is
reindexed in a separate transaction. Those commands cannot be used inside
a transaction block when working on a partitioned table or index.
</para>
<para>
When using the <literal>TABLESPACE</literal> clause with
<command>REINDEX</command> on a partitioned index or table, only the
tablespace references of the leaf partitions are updated. As partitioned
indexes are not updated, it is recommended to separately use
<command>ALTER TABLE ONLY</command> on them so as any new partitions
attached inherit the new tablespace. On failure, it may not have moved
all the indexes to the new tablespace. Re-running the command will rebuild
all the leaf partitions and move previously-unprocessed indexes to the new
tablespace.
</para>