Re: Inconsistent DB data in Streaming Replication

On 04/08/2013 12:34 PM, Samrat Revagade wrote:
>
> Hello,
>
> We have been trying to figure out possible solutions to the following
> problem in streaming replication Consider following scenario:
>
> If master receives commit command, it writes and flushes commit WAL
> records to the disk, It also writes and flushes data page related to
> this transaction.
>
No data page flushing takes place. All data page writing is delayed to
bgWriter and/or checkpoints.
>
> The master then sends WAL records to standby up to the commit WAL
> record. But before sending these records if failover happens then,
> old master is ahead of standby which is now the new master in terms
> of DB data leading to inconsistent data .
>
The master sends WAL records to standby continuously, not "upon commit
wal record".

In case of syncrep the master just waits for confirmation from standby
before returning to client on commit.
>
> One solution to avoid this situation is have the master send WAL
> records to standby and wait for ACK from standby committing WAL files
> to disk and only after that commit data page related to this
> transaction on master.
>
Not just commit, you must stop any *writing* of the wal records
effectively killing any parallelism.
>
> The main drawback would be increased wait time for the client due to
> extra round trip to standby before master sends ACK to client. Are
> there any other issues with this approach?
>
Min issue is that it will make *all* backends dependant on each sync
commit, essentially serialising all backends commits, with the
serialisation *including* the latency of roundtrip to client.

With current sync streaming the other backends can continue to write wal,
with proposed approach you can not write any records after the one
waiting an ACK from standby.
>
>
> Thank you,
>
> Samrat
>