Thread: Query on WAL Optimization and Streaming Replication
During 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?
Below are some of the WAL related settings:
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 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
thefirst 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
muchchanges 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 > On 17-Mar-2022, at 8:08 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > 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://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!GjvTz_vk!CfWoaVi0_A_FauqITD6xevYV9YuIJGyWWueduHyTOiD3CY65CJorQpYhD5zbUA$