Thread: Streaming Replication and Hash Indices

Streaming Replication and Hash Indices

From
Seb Mel
Date:
Hi list,

today i got bitten by the issue that streaming replication does not replicate "hash" indices as those changes are not
writtento WAL. That fact is documented here: 
http://www.postgresql.org/docs/current/static/indexes-types.html

But i could not find that information anywhere else like:
https://wiki.postgresql.org/wiki/Streaming_Replication#Restrictions
or
http://www.postgresql.org/docs/current/static/warm-standby.html

I suppose it would be very helpful to add it there, as the usual policy for "corrupt slave data" is to recreate the
slavefrom a base_backup which would make you into thinking that the issue was fixed.  
That is wrong though, as it will reappear after some time when the data in the table and the index diverge :)!

Regards,
Seb

Re: Streaming Replication and Hash Indices

From
Bruce Momjian
Date:
On Mon, Jan 18, 2016 at 01:15:28PM +0100, Seb Mel wrote:
> Hi list,
>
> today i got bitten by the issue that streaming replication does not replicate "hash" indices as those changes are not
writtento WAL. That fact is documented here: 
> http://www.postgresql.org/docs/current/static/indexes-types.html
>
> But i could not find that information anywhere else like:
> https://wiki.postgresql.org/wiki/Streaming_Replication#Restrictions
> or
> http://www.postgresql.org/docs/current/static/warm-standby.html
>
> I suppose it would be very helpful to add it there, as the usual policy for "corrupt slave data" is to recreate the
slavefrom a base_backup which would make you into thinking that the issue was fixed.  
> That is wrong though, as it will reappear after some time when the data in the table and the index diverge :)!

We issue a warning on hash index creation in 9.5:

        Issue a warning during the creation of <link
        linkend="indexes-types">hash</> indexes because they are not
        crash-safe (Bruce Momjian)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +