pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
9+ messages / 5 participants
[nested] [flat]
* postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-05-31 23:00 Mohan NBSPS <mohan.nbs.ont@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Mohan NBSPS @ 2024-05-31 23:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello Community,
Although the WAL retaining settings are default (max size 1GB),
`wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
I noticed a few WAL files in secondary nodes (streaming replication)
`pg_xlog`
folder.
What could be the reason why postgresql did not delete these ?
Thank you
Regards
Mohan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-01 03:58 Muhammad Imtiaz <imtiaz.m@bitnine.net>
parent: Mohan NBSPS <mohan.nbs.ont@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Muhammad Imtiaz @ 2024-06-01 03:58 UTC (permalink / raw)
To: mohan.nbs.ont@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
Hi ,
Ensure the following:
• Replication is functioning correctly between the primary and secondary
servers, with no lag.
• The archive_command parameter is properly configured in the database
settings.
Regards,
Muhammad Imtiaz
On Sat, 1 Jun 2024, 04:01 Mohan NBSPS, <mohan.nbs.ont@gmail.com> wrote:
> Hello Community,
>
> Although the WAL retaining settings are default (max size 1GB),
> `wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
> I noticed a few WAL files in secondary nodes (streaming replication)
> `pg_xlog`
> folder.
>
> What could be the reason why postgresql did not delete these ?
>
> Thank you
> Regards
> Mohan
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-02 11:47 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: Mohan NBSPS <mohan.nbs.ont@gmail.com>
1 sibling, 2 replies; 9+ messages in thread
From: Kashif Zeeshan @ 2024-06-02 11:47 UTC (permalink / raw)
To: mohan.nbs.ont@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
Hi Mohsn
There can be multiple reasons for that.
1. If the archiving process on the standby is not working correctly, old
WAL files will not be removed.
2. Ensure that the configuration settings for WAL management are correctly
set on both primary and standby
3. If checkpoints are not happening frequently enough, WAL files will
accumulate.
To diagnose the issue
1. Ensure that the replication between the primary and the standby is
working correctly.
On Primary Server
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;
On Standby Server
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
2. Check if the archiving process on the standby is working properly. Look
for any errors or warnings in the PostgreSQL logs related to WAL archiving.
3. Ensure that the standby server has the correct restore command
configured to fetch the archived WAL files.
Regards
Kashif Zeeshan
Bitnine Global
On Sat, Jun 1, 2024 at 4:01 AM Mohan NBSPS <mohan.nbs.ont@gmail.com> wrote:
> Hello Community,
>
> Although the WAL retaining settings are default (max size 1GB),
> `wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
> I noticed a few WAL files in secondary nodes (streaming replication)
> `pg_xlog`
> folder.
>
> What could be the reason why postgresql did not delete these ?
>
> Thank you
> Regards
> Mohan
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-04 01:33 vignesh kumar <vigneshkumar.venugopal@outlook.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: vignesh kumar @ 2024-06-04 01:33 UTC (permalink / raw)
To: Kashif Zeeshan <kashi.zeeshan@gmail.com>; mohan.nbs.ont@gmail.com <mohan.nbs.ont@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
You can use slot based replication, so that postgres knows which WAL FILE to retain and delete. however, if you're not concerned about storage, but then the replica go for WAL storage or WAL hub kind of solutions..
Get Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Kashif Zeeshan <kashi.zeeshan@gmail.com>
Sent: Sunday, June 2, 2024 5:17:57 PM
To: mohan.nbs.ont@gmail.com <mohan.nbs.ont@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
Hi Mohsn
There can be multiple reasons for that.
1. If the archiving process on the standby is not working correctly, old WAL files will not be removed.
2. Ensure that the configuration settings for WAL management are correctly set on both primary and standby
3. If checkpoints are not happening frequently enough, WAL files will accumulate.
To diagnose the issue
1. Ensure that the replication between the primary and the standby is working correctly.
On Primary Server
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;
On Standby Server
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
2. Check if the archiving process on the standby is working properly. Look for any errors or warnings in the PostgreSQL logs related to WAL archiving.
3. Ensure that the standby server has the correct restore command configured to fetch the archived WAL files.
Regards
Kashif Zeeshan
Bitnine Global
On Sat, Jun 1, 2024 at 4:01 AM Mohan NBSPS <mohan.nbs.ont@gmail.com<mailto:mohan.nbs.ont@gmail.com>> wrote:
Hello Community,
Although the WAL retaining settings are default (max size 1GB),
`wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
I noticed a few WAL files in secondary nodes (streaming replication) `pg_xlog`
folder.
What could be the reason why postgresql did not delete these ?
Thank you
Regards
Mohan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-05 00:08 Mohan NBSPS <mohan.nbs.ont@gmail.com>
parent: Muhammad Imtiaz <imtiaz.m@bitnine.net>
0 siblings, 0 replies; 9+ messages in thread
From: Mohan NBSPS @ 2024-06-05 00:08 UTC (permalink / raw)
To: Muhammad Imtiaz <imtiaz.m@bitnine.net>; +Cc: pgsql-admin@lists.postgresql.org
thank you.
the replication seems to work.
sorry, my question was incomplete.
I see few very old (over 2 years) WAL files remain in the secondary
(primary is clean with only
new WAL files).
On Fri, May 31, 2024 at 11:58 PM Muhammad Imtiaz <imtiaz.m@bitnine.net>
wrote:
> Hi ,
>
> Ensure the following:
>
> • Replication is functioning correctly between the primary and secondary
> servers, with no lag.
>
> • The archive_command parameter is properly configured in the database
> settings.
>
> Regards,
>
> Muhammad Imtiaz
>
> On Sat, 1 Jun 2024, 04:01 Mohan NBSPS, <mohan.nbs.ont@gmail.com> wrote:
>
>> Hello Community,
>>
>> Although the WAL retaining settings are default (max size 1GB),
>> `wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
>> I noticed a few WAL files in secondary nodes (streaming replication)
>> `pg_xlog`
>> folder.
>>
>> What could be the reason why postgresql did not delete these ?
>>
>> Thank you
>> Regards
>> Mohan
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-05 00:08 Mohan NBSPS <mohan.nbs.ont@gmail.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Mohan NBSPS @ 2024-06-05 00:08 UTC (permalink / raw)
To: Kashif Zeeshan <kashi.zeeshan@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
thank you.
the replication seems to work.
sorry, my question was incomplete.
I see few very old (over 2 years) WAL files remain in the secondary
(primary is clean with only
new WAL files).
On Sun, Jun 2, 2024 at 7:48 AM Kashif Zeeshan <kashi.zeeshan@gmail.com>
wrote:
> Hi Mohsn
>
> There can be multiple reasons for that.
>
> 1. If the archiving process on the standby is not working correctly, old
> WAL files will not be removed.
> 2. Ensure that the configuration settings for WAL management are correctly
> set on both primary and standby
> 3. If checkpoints are not happening frequently enough, WAL files will
> accumulate.
>
> To diagnose the issue
>
> 1. Ensure that the replication between the primary and the standby is
> working correctly.
>
> On Primary Server
> SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
> FROM pg_stat_replication;
>
> On Standby Server
> SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
>
>
> 2. Check if the archiving process on the standby is working properly. Look
> for any errors or warnings in the PostgreSQL logs related to WAL archiving.
> 3. Ensure that the standby server has the correct restore command
> configured to fetch the archived WAL files.
>
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
>
>
> On Sat, Jun 1, 2024 at 4:01 AM Mohan NBSPS <mohan.nbs.ont@gmail.com>
> wrote:
>
>> Hello Community,
>>
>> Although the WAL retaining settings are default (max size 1GB),
>> `wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
>> I noticed a few WAL files in secondary nodes (streaming replication)
>> `pg_xlog`
>> folder.
>>
>> What could be the reason why postgresql did not delete these ?
>>
>> Thank you
>> Regards
>> Mohan
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-05 00:09 Mohan NBSPS <mohan.nbs.ont@gmail.com>
parent: vignesh kumar <vigneshkumar.venugopal@outlook.com>
0 siblings, 1 reply; 9+ messages in thread
From: Mohan NBSPS @ 2024-06-05 00:09 UTC (permalink / raw)
To: vignesh kumar <vigneshkumar.venugopal@outlook.com>; +Cc: Kashif Zeeshan <kashi.zeeshan@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
thank you,
we are not using slots due to storage related issues that could occur if
the network is broken.
sorry, my question was incomplete.
I see few very old (over 2 years) WAL files remain in the secondary
(primary is clean with only
new WAL files).
On Mon, Jun 3, 2024 at 9:33 PM vignesh kumar <
vigneshkumar.venugopal@outlook.com> wrote:
> You can use slot based replication, so that postgres knows which WAL FILE
> to retain and delete. however, if you're not concerned about storage, but
> then the replica go for WAL storage or WAL hub kind of solutions..
>
> Get Outlook for Android <https://aka.ms/AAb9ysg;
> ------------------------------
> *From:* Kashif Zeeshan <kashi.zeeshan@gmail.com>
> *Sent:* Sunday, June 2, 2024 5:17:57 PM
> *To:* mohan.nbs.ont@gmail.com <mohan.nbs.ont@gmail.com>
> *Cc:* pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> *Subject:* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
>
> Hi Mohsn
>
> There can be multiple reasons for that.
>
> 1. If the archiving process on the standby is not working correctly, old
> WAL files will not be removed.
> 2. Ensure that the configuration settings for WAL management are correctly
> set on both primary and standby
> 3. If checkpoints are not happening frequently enough, WAL files will
> accumulate.
>
> To diagnose the issue
>
> 1. Ensure that the replication between the primary and the standby is
> working correctly.
>
> On Primary Server
> SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
> FROM pg_stat_replication;
>
> On Standby Server
> SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
>
>
> 2. Check if the archiving process on the standby is working properly. Look
> for any errors or warnings in the PostgreSQL logs related to WAL archiving.
> 3. Ensure that the standby server has the correct restore command
> configured to fetch the archived WAL files.
>
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
>
>
> On Sat, Jun 1, 2024 at 4:01 AM Mohan NBSPS <mohan.nbs.ont@gmail.com>
> wrote:
>
> Hello Community,
>
> Although the WAL retaining settings are default (max size 1GB),
> `wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
> I noticed a few WAL files in secondary nodes (streaming replication)
> `pg_xlog`
> folder.
>
> What could be the reason why postgresql did not delete these ?
>
> Thank you
> Regards
> Mohan
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-05 03:36 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: Mohan NBSPS <mohan.nbs.ont@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kashif Zeeshan @ 2024-06-05 03:36 UTC (permalink / raw)
To: mohan.nbs.ont@gmail.com; +Cc: vignesh kumar <vigneshkumar.venugopal@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi
On Wed, Jun 5, 2024 at 5:10 AM Mohan NBSPS <mohan.nbs.ont@gmail.com> wrote:
> thank you,
> we are not using slots due to storage related issues that could occur if
> the network is broken.
>
Slots are very useful and it will ensure that the Standby will not lag
behind the master and master will retain the WAL Files if standy did not
apply them yet, the storage is consideration but only incase of network
failure but this wont be a very big consideration.
Regards
Kashif Zeeshan
Bitnine Global
>
> sorry, my question was incomplete.
> I see few very old (over 2 years) WAL files remain in the secondary
> (primary is clean with only
> new WAL files).
>
> On Mon, Jun 3, 2024 at 9:33 PM vignesh kumar <
> vigneshkumar.venugopal@outlook.com> wrote:
>
>> You can use slot based replication, so that postgres knows which WAL FILE
>> to retain and delete. however, if you're not concerned about storage, but
>> then the replica go for WAL storage or WAL hub kind of solutions..
>>
>> Get Outlook for Android <https://aka.ms/AAb9ysg;
>> ------------------------------
>> *From:* Kashif Zeeshan <kashi.zeeshan@gmail.com>
>> *Sent:* Sunday, June 2, 2024 5:17:57 PM
>> *To:* mohan.nbs.ont@gmail.com <mohan.nbs.ont@gmail.com>
>> *Cc:* pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
>> *Subject:* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
>>
>> Hi Mohsn
>>
>> There can be multiple reasons for that.
>>
>> 1. If the archiving process on the standby is not working correctly, old
>> WAL files will not be removed.
>> 2. Ensure that the configuration settings for WAL management are
>> correctly set on both primary and standby
>> 3. If checkpoints are not happening frequently enough, WAL files will
>> accumulate.
>>
>> To diagnose the issue
>>
>> 1. Ensure that the replication between the primary and the standby is
>> working correctly.
>>
>> On Primary Server
>> SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
>> FROM pg_stat_replication;
>>
>> On Standby Server
>> SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
>>
>>
>> 2. Check if the archiving process on the standby is working properly.
>> Look for any errors or warnings in the PostgreSQL logs related to WAL
>> archiving.
>> 3. Ensure that the standby server has the correct restore command
>> configured to fetch the archived WAL files.
>>
>>
>> Regards
>> Kashif Zeeshan
>> Bitnine Global
>>
>>
>>
>> On Sat, Jun 1, 2024 at 4:01 AM Mohan NBSPS <mohan.nbs.ont@gmail.com>
>> wrote:
>>
>> Hello Community,
>>
>> Although the WAL retaining settings are default (max size 1GB),
>> `wal_keep_segments` is 25, checkpoint target is 2.5 minutes(default).
>> I noticed a few WAL files in secondary nodes (streaming replication)
>> `pg_xlog`
>> folder.
>>
>> What could be the reason why postgresql did not delete these ?
>>
>> Thank you
>> Regards
>> Mohan
>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgrsql 9.5: Old WAL files remain in secondary `pg_xlog`
@ 2024-06-05 03:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2024-06-05 03:42 UTC (permalink / raw)
To: mohan.nbs.ont@gmail.com; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Jun 4, 2024, at 9:36 PM, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:
>
> we are not using slots due to storage related issues that could occur if the network is broken
If you can ever upgrade to a current version, then there's a parameter that can be set to put a limit on retained WAL, so that in case of network failure, it sacrifices the ability to bring the replica back online quickly in favor of not bringing the primary down with disk full condition.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-06-05 03:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-31 23:00 postgrsql 9.5: Old WAL files remain in secondary `pg_xlog` Mohan NBSPS <mohan.nbs.ont@gmail.com>
2024-06-01 03:58 ` Muhammad Imtiaz <imtiaz.m@bitnine.net>
2024-06-05 00:08 ` Mohan NBSPS <mohan.nbs.ont@gmail.com>
2024-06-02 11:47 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-06-04 01:33 ` vignesh kumar <vigneshkumar.venugopal@outlook.com>
2024-06-05 00:09 ` Mohan NBSPS <mohan.nbs.ont@gmail.com>
2024-06-05 03:36 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-06-05 03:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-06-05 00:08 ` Mohan NBSPS <mohan.nbs.ont@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox