Re: Urgent :: Postgresql streaming replication issue - sync mode

On Wed, 2019-10-02 at 23:58 +0530, Shital A wrote:
> We are seeing a strange issue with postgresql streaming application
> in sync mode.
>
> We are using postgresql 9.6. Old version because of some specific
> requirements. We have setup cluster with master-standby using
> pacemaker.
>
> When we kill master using killall -9 postgres. The failed primary has
> few records extra than standby node. We have done setup with
> synchronous_commit = remote_apply and have set
> synchronous_standby_names=server_name.
>
> As the failed primary is having more data, How is it possible that
> primary is committing transaction before they were applied on standby
> with synchronous_commit=remote_apply?
>
>
> Please share if you have any thoughts. Are we missing any config ?

This is to be expected.

The transaction will be committed on the primary, then on the standby,
and COMMIT will only return once the standby reports success.

But the transacaction still has to be committed on the primary first.

If the standby sis promoted while COMMIT is waiting for the standby,
you can end up with the transaction committed on the primary,
but not yet committed on the standby.

You should use "pg_rewind" on the failed primary if you want to use
it as new standby for the promoted server.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com