Query on WAL Optimization and Streaming Replication

On Thu, 2022-03-17 at 12:36 +0000, Shukla, Pranjal wrote:

uring upgrades of our application, we generally shutdown all Secondary servers
which are getting stream replicated from Primary Servers. This is to maintain
a copy of database on other servers should
we wish to revert (of course we take DB Backups too before starting the activity).
After the application upgrade is done, when we start the secondary, often the
replication is broken, and we need to
again setup using pg_basebackup. How do we ensure that secondary is able to
resume the replication without the need of base back up again?

There are three ways:

1. have a WAL archive and configure "restore_command" on the standby

2. set "wal_keep_size" on the primary high enough

3. use a replication slot

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

Hi Laurenze,
From the configuration we have, does it mean that the primary will retain 32 WAL's of 1 GB each and then start evicting the first WAL as soon as the last one gets filled? In layman's term, 32GB is huge amount of data and I don't think that much changes during upgrades. In fact the total size of our database is 56 GB. Is my understanding correct?

shared_buffers = 48GB
wal_level = replica
max_prepared_transactions = 200
max_wal_senders = 5
wal_keep_segments = 32
hot_standby = ON
effective_cache_size = 144GB
work_mem = 1GB
maintenance_work_mem = 2GB
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 2GB

Thanks & Regards
Pranjal Shukla

On 3/17/22, 6:50 PM, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:

On Thu, 2022-03-17 at 12:36 +0000, Shukla, Pranjal wrote:

uring upgrades of our application, we generally shutdown all Secondary servers
which are getting stream replicated from Primary Servers. This is to maintain
a copy of database on other servers should
we wish to revert (of course we take DB Backups too before starting the activity).
After the application upgrade is done, when we start the secondary, often the
replication is broken, and we need to
again setup using pg_basebackup. How do we ensure that secondary is able to
resume the replication without the need of base back up again?

There are three ways:

1. have a WAL archive and configure "restore_command" on the standby

2. set "wal_keep_size" on the primary high enough

3. use a replication slot

Yours,
Laurenz Albe
--
Cybertec | https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!GjvTz_vk!DWWCoWlC7gBG3UPcdGdgbBT_1hKnCxfiO7qpf7QV1Q-bOqCJ1JkNSBYlD2yvLg$

On Thu, 2022-03-17 at 14:05 +0000, Shukla, Pranjal wrote:

From the configuration we have, does it mean that the primary will retain 32 WAL's
of 1 GB each and then start evicting the first WAL as soon as the last one gets filled?
In layman's term, 32GB is huge amount of data and I don't think that much changes
during upgrades. In fact the total size of our database is 56 GB. Is my understanding
correct?

wal_keep_segments = 32

No, it means that the last 32 WAL segments of size 16MB are retained,
that is 0.5 GB.

"max_wal_size" is not the size of a WAL segment.

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

Ok,
Then what will increase the size of the WAL?

Sent from my iPhone