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

I read this
https://www.2ndquadrant.com/en/blog/evolution-fault-tolerance-postgresql-synchronous-commit/

But don't see why your primary would have more records than the standby?

If killall was issued before commit returned, that means the transaction
wasn't completed so yes you would lose records after last commit but that's
expected; if commit was returned both primary and standby should have the
transaction.

Are you sure in your case you end up with primary and standby with
different records from a single transaction?

On Thu, 3 Oct 2019, 9:41 pm Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> 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
>
>
>
>