On 02/27/2017 11:14 PM, Sasa Vilic wrote:
On 2017-02-28 06:14, Adrian Klaver wrote:
On 02/27/2017 05:52 PM, Sasa Vilic wrote:
Because standby is running in syncronous replication, whereby wal
archiver is asynchronous. Therefore there is a small window where slave
has received the data but master has not pushed it yet to wal archive.
Exactly. The standby already has the latest information, it would gain
nothing from fetching it from the archive and anything it wrote to the
archive would only have the information it got from the master at the
point of failure. That is before you promoted it, after it would be on
its own path independent of the master.
Hi Adrian,
I am afraid that you don't understand me.
Standby is not fetching WAL from archive, it fetches it directly from
master and is done synchronously, which means that master will only
confirm transaction to client when WAL is streamed and applied at
standby. On the other hand, master does not have to wait for WAL
archiver. If master crashes before WAL archiver is able to send WAL, we
would still have it on standby.
Let us for the sake of demonstration consider that we have same very low
busy but very critical system:
1. Your client connects to primary server (master) and performs changes
on data
2. It just happen that this is a moment where PostgreSQL opens new WAL
segment. It writes few kilobytes in this new WAL segment but it has
almost 16MB to write before segment is complete. So the wal archiver has
to wait before it can push wal segment in wal archive
3. Secondary server (standby) is doing same, it is writing changes to
newly created WAL segment
4. Your client issues COMMIT
- primary waits until changes are applied at secondary
- primary flushes changes to WAL
- secondary confirms transaction to primary
- primary confirms transaction to client
- WAL is still not processed by wal archiver because it is only i.e.
1 MB big and we are still left 15MB to go
5. Primary server crashes, i.e. due to catastrophic disk failure
- everything stops and can't be recovered
- wal archiver is dead, but even if it were alive it wouldn't send
WAL to archive anyway because 16MB of wal segment was not filled up
6. We promote our secondary server to master
- In secondary server's WAL we already got changes from primary
- Secondary continues appending new changes to wal segment
7. Eventually WAL segment on secondary fills up and then pushes it to
wal archive.
8. Although primary is dead, we didn't loose anything because lost WAL
data was pushed by secondary.
I understand the above, what I did not understand, from your original post:
"My problem is that sometimes WAL uploaded from master and from slave
are not 100% identical. In most cases they are but occasionally they are
not. I have written small script that ensures that upload is free of
race condition and I log md5 sum of each WAL."
To me that reads as you sending WALs to the archive from both the master
and the standby in parallel, instead of sequentially as you imply in the
outline above. It would seem to be confirmed by the setting of
archive_mode = always:
https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING
https://www.postgresql.org/docs/9.6/static/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY
Seems to mean the simpler thing to do would be to set standby to
archive_mode = on, in which case the standby would not contribute WAL's
until it was promoted which would seem to be what you want.
Regards,
Sasa
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
