reindex 中文man页面

NAME

REINDEX - 重建索引

SYNOPSIS

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

DESCRIPTION 描述

REINDEX 基于存储在表上的数据重建索引， 替换旧的索引拷贝。使用 REINDEX 有两个主要原因：

*

 索引崩溃，并且不再包含有效的数据。尽管理论上这是不可能发生的， 但实际上索引会因为软件毛病或者硬件问题而崩溃。REINDEX 提供了一个恢复方法。

*

 要处理的索引包含大量无用的索引页未被回收。在某些情况下， 这个问题会发生在 PostgreSQL 里面的 B-树索引上。REINDEX  提供了一个缩小索引空间消耗的方法，它采用的方法是写一个不带无用索引页的新版本的索引。 参阅Section 21.2 ``Routine Indexing'' 获取更多信息。

PARAMETERS 参数

DATABASE

 恢复一个声明了的数据库的所有系统索引。 不包含用户表上的索引。同样，除非在独立运行模式下，也会忽略在共享系统表上的索引（见下文）。

TABLE

 重新建立声明的表的所有索引。如果表有个从属的"TOAST"表，那么这个表也会重新索引。

INDEX

 重新建立声明了的索引。

name

 要重建的所声明的数据库/表/索引的名称。 表和索引名可以有模式修饰。

FORCE

 这是一个废弃的选项，如果声明，会被忽略。

NOTES 注意

 如果你怀疑一个用户表上的索引崩溃了，你可以简单地重建该索引， 或者该表上地所有索引，使用 REINDEX INDEX 或者 REINDEX TABLE。 另外一个对付用户表索引崩溃的方法时删除然后重建它。如果你还要在表上进行一些维护动作， 可能这么做更好一些。REINDEX 在表上请求排他锁，而 CREATE INDEX 只是锁住写动作， 而不会锁住读。

 如果你从一个崩溃的系统表索引上恢复，事情会更棘手一些。 这种情况下，系统必须不能使用任何有疑问的索引。 （实际上，在这种情况下，你可能发现服务器进程在启动之后马上就崩溃了， 因为依赖于崩溃了的索引。）要想安全恢复，服务器必须带着 -P 选项启动， 它禁止服务器在查找系统表的时候使用索引。

 这么做个一个办法事停止 postmaster 然后带着 -P 命令行选项启动一个独立的 PostgreSQL 服务器。 然后，根据你希望恢复的程度，可以发出 REINDEX DATABASE，REINDEX TABLE，或者 REINDEX INDEX。 如果还有怀疑，使用 REINDEX DATABASE 选择重新构造数据库中全部的系统索引。 然后退出独立服务器会话并且重启普通的服务器。参阅 postgres(1) 手册页获取有关如何与独立服务器交互的信息。

 另外，一个普通的会话可以在其命令行选项里带着 -P 启动。 这么做的方法因不同的客户端而异，但是在所有基于 libpq 的客户端上， 我们都可以通过在启动客户端之前设置 PGOPTIONS 环境变量为 -P 来实现。 请注意尽管这个方法并不要求锁住其它客户端，但是禁止其它客户端连接受损的数据库， 直到完成修补应该事一个明智的选择。

 如果怀疑任何共享的系统表的索引损坏（(pg_database， pg_group，或者 pg_shadow）， 那么必须用独立服务器的方式来修复它。REINDEX 不能在多用户环境下处理共享系统表。

 除了共享系统表之外的所有索引，REINDEX 是抗崩溃并且是事务安全的。 REINDEX 对于共享的索引而言不是抗崩溃的，这就是为什么不允许在正常操作中这么使用的原因。 如果在重新对一个共享表进行索引的时候发生了崩溃，那么在纠正问题之前，就不可能重新启动普通的服务器。 （一个建立了一部分的共享索引的典型症状是"index is not a btree/索引不是 btree 索引"错误。）

 在 PostgreSQL 7.4 之前，REINDEX TABLE 并不自动处理 TOAST 表，因此这些表必须用独立的命令进行处理。这么做仍然可以，但是已经多余了。  

EXAMPLES 例子

 重建表 mytable 上的索引：

REINDEX TABLE my_table;

 重建单个索引：

REINDEX INDEX my_index;

 重建一个数据库上的所有系统索引，不管他们是否有效：

$ export PGOPTIONS="-P"
$ psql broken_db
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

COMPATIBILITY 兼容性

 在SQL 标准里没有 REINDEX。  

#p#

NAME

REINDEX - rebuild indexes

SYNOPSIS

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

DESCRIPTION

REINDEX rebuilds an index based on the data stored in the table, replacing the old copy of the index. There are two main reasons to use REINDEX:

*

An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes may become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

*

The index in question contains a lot of dead index pages that are not being reclaimed. This can occur with B-tree indexes in PostgreSQL under certain access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See the section called ``Routine Indexing'' in the documentation for more information.

PARAMETERS

DATABASE

Recreate all system indexes of a specified database. Indexes on user tables are not processed. Also, indexes on shared system catalogs are skipped except in stand-alone mode (see below).

TABLE

Recreate all indexes of a specified table. If the table has a secondary ``TOAST'' table, that is reindexed as well.

INDEX

Recreate a specified index.

name

The name of the specific database, table, or index to be reindexed. Table and index names may be schema-qualified.

FORCE

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 -P 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 -P 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 postgres(1) reference page for more information about how to interact with the stand-alone server interface.

Alternatively, a regular server session can be started with -P 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 will not be possible to restart the regular server until the problem is rectified. (The typical symptom of a partially rebuilt shared index is ``index is not a btree'' errors.)

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

Recreate the indexes on the table my_table:

REINDEX TABLE my_table;

Rebuild a single index:

REINDEX INDEX my_index;

Rebuild all system indexes in a particular database, without trusting them to be valid already:

$ export PGOPTIONS="-P"
$ psql broken_db
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

COMPATIBILITY

There is no REINDEX command in the SQL standard.