Re: BUG #5727: Indexes broken in streaming replication

On 26.10.2010 20:04, Jan Kantert wrote:
> we have set up streaming replication. It works fine in normal cases. We
> found out that one query did not work anymore on our slaves. We have
> verified that the slaves were up to date and contained all data.
>...
> master=# CREATE INDEX index_user_lower_login ON users USING hash
> (lower(login::text));

Hash indexes are not WAL-logged, and therefore are not replicated
either. For the same reason, they are not crash-safe, and won't work
after recovery from a continuous WAL archive. Don't use them.

There's a note in the docs about this:

> Note: Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For this reason, hash index use is presently discouraged.

though it doesn't explicitly mention replication. Perhaps it should be
updated to something like:

Note: Hash index operations are not presently WAL-logged, so hash
indexes might need to be rebuilt with REINDEX after a database crash.
They are also not replicated over streaming or file-based replication.
For this reason, hash index use is presently discouraged.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com