Обсуждение: 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
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$