2

I've setup a master / slave Postgresql (13.4) group, 1 master and 1 slave, using WAL warm standby. (refer: https://postgresql.org/docs/current/warm-standby.html#STANDBY-SERVER-SETUP)

The slave gone out of sync, and can't recover due to WAL not exists anymore on master, refer another question for details: Postgresql - recover slave after out of sync with master using WAL

Now, I'am re-doing the slave. There are 3.7T data on master, and it's increasing pretty quickly, roughly 30Gb per day ?

To avoid slave go out of sync again, I'm trying to increase the WAL retention.

Here is what I plan to do:

  • max_wal_size = 20Gb
  • min_wal_size = 10GB

Questions:

  • A-1. Are the above param values proper?
  • A-2. Does increasing these param has a bad side effect ?
  • A-3. I only need to apply these 2 params to master, right?
  • B. Is there more parameter that I should config?
  • C. Any better way to avoid slave out of sync ?

Thank you.

1 Answer 1

4

No, that won't help at all.

You have three alternative options:

  • increase wal_keep_size to a big enough value

    That is a simple, but coarse solution, in that it makes the primary always retain a certain amount of WAL, regardless if it is too much or not enough (because the downtime is longer than expected).

  • set a restore_command on the, so that it can use the WAL archive to catch up

    That may be the best option if you have a WAL archive.

  • use a physical replication slot

    Such an object marks a certain position in the WAL stream and prevents the primary from discarding WAL that is still needed by the standby. While being an elegant solution, this bears the risk of breaking the primary if the standby stays down too long. Consequently, it is a good idea to monitor replication in this case and/or to set max_slot_wal_keep_size to put a cap on the amount of WAL retained on the primary.

9
  • Thanks, can you give a bit more details about the last 2 options, and can be 3 options be used together or just choose one?
    – Eric
    Dec 13, 2021 at 9:28
  • 1
    Ok, let me try ` -c wal_keep_size=31000` first. 30Gb should be large enough.
    – Eric
    Dec 13, 2021 at 13:33
  • 1
    I've changed the wal_keep_size on master, then create the slave again, now it's working again, thanks, I would check the other 2 options later.
    – Eric
    Dec 13, 2021 at 18:12
  • 2
    Instead of using wal_keep_size=30GB, I suggest you to create a replication slot and set max_slot_wal_keep_size to 40GB. With this, The Primary's WAL will keep disc size till 40GB and only if it's necessary. You can also use slot without max_slot_wal_keep_size and monitor WAL folder size to prevent any "storage full" issue
    – Sahap Asci
    Dec 13, 2021 at 22:35
  • 1
    @user218867 Laurenz already did.
    – Sahap Asci
    Dec 14, 2021 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.