Postgresql: find hashed indexes

Question

I am setting up WAL-archiving and streaming replication to a warm standby server with Postgresql 9.1.

The Postgresql documentation states:

Operations on hash indexes are not presently WAL-logged, so replay will not update these indexes. This will mean that any new inserts will be ignored by the index, updated rows will apparently disappear and deleted rows will still retain pointers. In other words, if you modify a table with a hash index on it then you will get incorrect query results on a standby server. When recovery completes it is recommended that you manually REINDEX each such index after completing a recovery operation.

How can I find out if a specific database contains hash indexes?

Frank Heikens · Accepted Answer · 2015-02-28 07:23:32Z

3

Check pg_class and pg_am to find indexes using the hash method:

SELECT  *
FROM    pg_class
    JOIN pg_am ON pg_am.oid = relam
WHERE   relkind = 'i'
AND     amname = 'hash';

answered Feb 28, 2015 at 7:23

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: find hashed indexes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related