Thread: Invalid data read from synchronously replicated hot standby

On 04/21/2016 01:05 AM, martin.kamp.jensen@schneider-electric.com wrote:
> Hi,
>
> We are getting invalid data when reading from a synchronously replicated
> hot standby node in a 2-node setup. To better understand the situation,
> we have created a document that provides an overview. We are hoping that
> someone might be able to confirm whether or not the setup makes sense,
> i.e., whether we are using PostgreSQL correctly and experiencing a bug,
> or if we are using PostgreSQL incorrectly.
>
> Link to document that contains a step-by-step description of the
> situation:
> https://docs.google.com/document/d/1MuX8rq1gKw_WZ-HVflqxFslvXNTRGKa77A4NHto4ue0/edit?usp=sharing
>
>
> If the setup is sane (and expected to work), we will work on setting up
> a minimal reproduce that avoids our complete system. We are thinking
> that a scripted Ansible/Vagrant setup makes sense.

Questions:

What is wal_level set to?

Why on Node A do you have in recovery.conf?:
primary_conninfo = 'host=<Node A IP address>'

What exactly are you trying to do?

Looks to me you are trying to have multi-master, is that the case?

>
> Best regards,
> Martin


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, May 11, 2016 at 5:44 AM,
<martin.kamp.jensen@schneider-electric.com> wrote:

>> We are getting invalid data when reading from a synchronously
>> replicated hot standby node in a 2-node setup. To better understand
>> the situation, we have created a document that provides an overview.
>> We are hoping that someone might be able to confirm whether or not
>> the setup makes sense, i.e., whether we are using PostgreSQL
>> correctly and experiencing a bug, or if we are using PostgreSQL
>> incorrectly.
>>
>> Link to document that contains a step-by-step description of the
>> situation:
>> https://docs.google.com/document/d/1MuX8rq1gKw_WZ-HVflqxFslvXNTRGKa77A4NHto4ue0/edit?usp=sharing

Please include such information in your post or as an attachment.
Who knows whether that link will still be usable and unchanged 20
years from now?

>> If the setup is sane (and expected to work),

I didn't see anywhere that you correctly handled WAL in setting up
your standby.  I am not surprised by there being corruption,
including duplicate keys in a unique index.  You might try -x or -X
when you run pg_basebackup, or use archiving.  Whatever you do, do
NOT delete the backup_label file!

> In the mean time, we are preparing a new platform on 9.5.2 where
> I have not been able to reproduce the issue (however, we have
> introduced a lot of changes besides upgrading PostgreSQL).

We would need a lot more detail to be able to even guess at whether
you have actually solved the flaws in your process or have just
been lucky so far.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company