public inbox for [email protected]
help / color / mirror / Atom feedHuge archive log generate in Postgresql-13
17+ messages / 6 participants
[nested] [flat]
* Huge archive log generate in Postgresql-13
@ 2022-04-18 11:34 Ram Pratap Maurya <[email protected]>
2022-04-18 11:43 ` Re: Huge archive log generate in Postgresql-13 Mladen Gogala <[email protected]>
2022-04-18 11:47 ` Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
0 siblings, 3 replies; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-18 11:34 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Manu Saxena <[email protected]>
Hi Support,
We have upgraded postgresql DB from version 11 to 13 . after upgrade to 13 huge archive log generate in system .
Before upgrade per day 120GB to 150 GB log generated but after upgrade per day approx. 250 to 300 GB log generated.
Can you please suggest why huge archive log generated after upgrade there any configure setting or this is Postgresql-13 behaviour.
Postgresql-13 Postgresql conf file attached for your references.
Regards,
Ram Pratap.
Attachments:
[application/octet-stream] postgresql.conf (27.6K, 3-postgresql.conf)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-18 11:43 ` Mladen Gogala <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: Mladen Gogala @ 2022-04-18 11:43 UTC (permalink / raw)
To: [email protected]
On 4/18/22 07:34, Ram Pratap Maurya wrote:
>
> Hi Support,
>
> We have upgraded postgresql DB from version 11 to 13 . after upgrade
> to 13 huge archive log generate in system .
>
> Before upgrade per day 120GB to 150 GB log generated but after
> upgrade per day approx. 250 to 300 GB log generated.
>
> Can you please suggest why huge archive log generated after upgrade
> there any configure setting or this is Postgresql-13 behaviour.
>
> Postgresql-13 Postgresql conf file attached for your references.
>
> Regards,
>
> Ram Pratap.
>
Have you checked what's in those WAL archives? You can do that with
pg_waldump. My guess would be that your vacuum is probably more active
than in the version 11. However, that's just a guess. You can also turn
on WAL compression. Be aware that compression will reduce disk
consumption at the expense of CPU consumption.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-18 11:47 ` Ram Pratap Maurya <[email protected]>
2022-04-18 12:10 ` Re: Huge archive log generate in Postgresql-13 Holger Jakobs <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-18 11:47 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Manu Saxena <[email protected]>
Hi Support,
We have upgraded postgresql DB from version 11 to 13 . after upgrade to 13 huge archive log generate in system .
Before upgrade per day 120GB to 150 GB log generated but after upgrade per day approx. 250 to 300 GB log generated.
Can you please suggest why huge archive log generated after upgrade there any configure setting or this is Postgresql-13 behaviour.
Postgresql-13 Postgresql conf file attached for your references.
Regards,
Ram Pratap.
Attachments:
[application/octet-stream] postgresql.conf (27.6K, 3-postgresql.conf)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 11:47 ` Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-18 12:10 ` Holger Jakobs <[email protected]>
2022-04-18 19:15 ` Re: Huge archive log generate in Postgresql-13 Clive Swan <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Holger Jakobs @ 2022-04-18 12:10 UTC (permalink / raw)
To: [email protected]
wal_level = hot_standby # minimal, replica, or logical
# (change requires restart)
Set wal_level to one of the allowed values. It's possible that your
value gets interpreted as logical, thus creating larger wal entries.
Am 18.04.22 um 13:47 schrieb Ram Pratap Maurya:
>
> Hi Support,
>
> We have upgraded postgresql DB from version 11 to 13 . after upgrade
> to 13 huge archive log generate in system .
>
> Before upgrade per day 120GB to 150 GB log generated but after
> upgrade per day approx. 250 to 300 GB log generated.
>
> Can you please suggest why huge archive log generated after upgrade
> there any configure setting or this is Postgresql-13 behaviour.
>
> Postgresql-13 Postgresql conf file attached for your references.
>
> Regards,
>
> Ram Pratap.
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (203B, 3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 11:47 ` Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 12:10 ` Re: Huge archive log generate in Postgresql-13 Holger Jakobs <[email protected]>
@ 2022-04-18 19:15 ` Clive Swan <[email protected]>
2022-04-21 05:54 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Clive Swan @ 2022-04-18 19:15 UTC (permalink / raw)
To: Holger Jakobs <[email protected]>; [email protected] <[email protected]>
Grenting,
Is your logging set to high verbosity??
Usually use high verbosity for debugging, then change to low verbosity.
Clive
________________________________
From: Holger Jakobs <[email protected]>
Sent: Monday, 18 April 2022, 13:10
To: [email protected] <[email protected]>
Subject: Re: Huge archive log generate in Postgresql-13
wal_level = hot_standby # minimal, replica, or logical
# (change requires restart)
Set wal_level to one of the allowed values. It's possible that your value gets interpreted as logical, thus creating larger wal entries.
Am 18.04.22 um 13:47 schrieb Ram Pratap Maurya:
Hi Support,
We have upgraded postgresql DB from version 11 to 13 . after upgrade to 13 huge archive log generate in system .
Before upgrade per day 120GB to 150 GB log generated but after upgrade per day approx. 250 to 300 GB log generated.
Can you please suggest why huge archive log generated after upgrade there any configure setting or this is Postgresql-13 behaviour.
Postgresql-13 Postgresql conf file attached for your references.
Regards,
Ram Pratap.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 11:47 ` Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 12:10 ` Re: Huge archive log generate in Postgresql-13 Holger Jakobs <[email protected]>
2022-04-18 19:15 ` Re: Huge archive log generate in Postgresql-13 Clive Swan <[email protected]>
@ 2022-04-21 05:54 ` Ram Pratap Maurya <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-21 05:54 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Ram Pratap Maurya <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>
Dear Holger,
we have one slave server and one DR server.
salve server working on streaming replication and DR server replicate with Archive log.
if we change "wal_level " parameter value to “replica” there any impact in existing server (Salve and DR) replication?
Please suggest .
Regards,
Ram Pratap.
From: Ram Pratap Maurya
Sent: 21 April 2022 09:49
To: Ram Pratap Maurya <[email protected]>
Subject: FW: Huge archive log generate in Postgresql-13
---------- Forwarded message ---------
From: Clive Swan <[email protected]<mailto:[email protected]>>
Date: Tue, Apr 19, 2022 at 12:45 AM
Subject: Re: Huge archive log generate in Postgresql-13
To: Holger Jakobs <[email protected]<mailto:[email protected]>>, [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>>
Grenting,
Is your logging set to high verbosity??
Usually use high verbosity for debugging, then change to low verbosity.
Clive
________________________________
From: Holger Jakobs <[email protected]<mailto:[email protected]>>
Sent: Monday, 18 April 2022, 13:10
To: [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>>
Subject: Re: Huge archive log generate in Postgresql-13
wal_level = hot_standby # minimal, replica, or logical
# (change requires restart)
Set wal_level to one of the allowed values. It's possible that your value gets interpreted as logical, thus creating larger wal entries.
Am 18.04.22 um 13:47 schrieb Ram Pratap Maurya:
Hi Support,
We have upgraded postgresql DB from version 11 to 13 . after upgrade to 13 huge archive log generate in system .
Before upgrade per day 120GB to 150 GB log generated but after upgrade per day approx. 250 to 300 GB log generated.
Can you please suggest why huge archive log generated after upgrade there any configure setting or this is Postgresql-13 behaviour.
Postgresql-13 Postgresql conf file attached for your references.
Regards,
Ram Pratap.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/octet-stream] postgresql.conf (27.6K, 3-postgresql.conf)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-18 16:00 ` Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2022-04-18 16:00 UTC (permalink / raw)
To: Ram Pratap Maurya <[email protected]>; [email protected] <[email protected]>; +Cc: Manu Saxena <[email protected]>
On 4/18/22 04:34, Ram Pratap Maurya wrote:
> Hi Support,
>
> We have upgraded postgresql DB from version 11 to 13 . after upgrade to
> 13 huge archive log generate in system .
>
> Before upgrade per day 120GB to 150 GB log generated but after upgrade
> per day approx. 250 to 300 GB log generated.
Where are you measuring this in the WAL directory or the archive directory?
Do you have replication set up from this server and if so what type?
FYI, the wal_level setting of hot_standby is deprecated and maps to
replica since version 9.6. At some point you might want to change to
match current documentation.
>
> Can you please suggest why huge archive log generated after upgrade
> there any configure setting or this is Postgresql-13 behaviour.
>
> Postgresql-13 Postgresql conf file attached for your references.
>
> Regards,
>
> Ram Pratap.
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
@ 2022-04-19 03:29 ` Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-19 03:29 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>; +Cc: Manu Saxena <[email protected]>
Dear Adrian,
We have two replica system one is Slave and other id DR server .
Salve server replicate from PG_WAL and DR system replicate from pg_log_archive.
Can you please suggest what changes need to required in PG13 conf file.
Regards,
Ram Pratap.
-----Original Message-----
From: Adrian Klaver [mailto:[email protected]]
Sent: 18 April 2022 21:30
To: Ram Pratap Maurya <[email protected]>; [email protected]
Cc: Manu Saxena <[email protected]>
Subject: Re: Huge archive log generate in Postgresql-13
On 4/18/22 04:34, Ram Pratap Maurya wrote:
> Hi Support,
>
> We have upgraded postgresql DB from version 11 to 13 . after upgrade
> to
> 13 huge archive log generate in system .
>
> Before upgrade per day 120GB to 150 GB log generated but after
> upgrade per day approx. 250 to 300 GB log generated.
Where are you measuring this in the WAL directory or the archive directory?
Do you have replication set up from this server and if so what type?
FYI, the wal_level setting of hot_standby is deprecated and maps to replica since version 9.6. At some point you might want to change to match current documentation.
>
> Can you please suggest why huge archive log generated after upgrade
> there any configure setting or this is Postgresql-13 behaviour.
>
> Postgresql-13 Postgresql conf file attached for your references.
>
> Regards,
>
> Ram Pratap.
>
--
Adrian Klaver
[email protected]
Attachments:
[application/octet-stream] postgresql.conf (27.6K, 2-postgresql.conf)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-19 04:41 ` Ram Pratap Maurya <[email protected]>
2022-04-19 17:20 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2023-07-21 04:06 ` Server unable to UP after restore Ram Pratap Maurya <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-19 04:41 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Dear Adrian,
If we set parameter "PG-WAL = replica" in PG13 conf file , there is any issue in replication through PG_WAL and pg_log_archive log.
https://www.postgresql.org/docs/13/runtime-config-wal.html
Regards,
Ram Pratap.
-----Original Message-----
From: Ram Pratap Maurya
Sent: 19 April 2022 09:00
To: Adrian Klaver <[email protected]>; [email protected]
Cc: Manu Saxena <[email protected]>
Subject: RE: Huge archive log generate in Postgresql-13
Dear Adrian,
We have two replica system one is Slave and other id DR server .
Salve server replicate from PG_WAL and DR system replicate from pg_log_archive.
Can you please suggest what changes need to required in PG13 conf file.
Regards,
Ram Pratap.
-----Original Message-----
From: Adrian Klaver [mailto:[email protected]]
Sent: 18 April 2022 21:30
To: Ram Pratap Maurya <[email protected]<mailto:[email protected]>>; [email protected]<mailto:[email protected]>
Cc: Manu Saxena <[email protected]<mailto:[email protected]>>
Subject: Re: Huge archive log generate in Postgresql-13
On 4/18/22 04:34, Ram Pratap Maurya wrote:
> Hi Support,
>
> We have upgraded postgresql DB from version 11 to 13 . after upgrade
> to
> 13 huge archive log generate in system .
>
> Before upgrade per day 120GB to 150 GB log generated but after
> upgrade per day approx. 250 to 300 GB log generated.
Where are you measuring this in the WAL directory or the archive directory?
Do you have replication set up from this server and if so what type?
FYI, the wal_level setting of hot_standby is deprecated and maps to replica since version 9.6. At some point you might want to change to match current documentation.
>
> Can you please suggest why huge archive log generated after upgrade
> there any configure setting or this is Postgresql-13 behaviour.
>
> Postgresql-13 Postgresql conf file attached for your references.
>
> Regards,
>
> Ram Pratap.
>
--
Adrian Klaver
[email protected]<mailto:[email protected]>
Attachments:
[application/octet-stream] postgresql.conf (27.6K, 3-postgresql.conf)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-19 17:20 ` Adrian Klaver <[email protected]>
2022-04-20 04:31 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2022-04-19 17:20 UTC (permalink / raw)
To: Ram Pratap Maurya <[email protected]>; [email protected] <[email protected]>
On 4/18/22 21:41, Ram Pratap Maurya wrote:
> Dear Adrian,
>
> If we set parameter *"PG-WAL = replica"* in PG13 conf file , there is
> any issue in replication through PG_WAL and pg_log_archive log.
>
In versions of Postgres from 9.6+ your setting of hot_standby is mapped
to the replica setting so it is really not a change. In other words the
hot_standby setting = replica setting as of 9.6. I made the suggestion
to change just so the conf setting matches the choices in current
documentation. Less confusion for someone looking through the
postgresql.conf file and then looking up the values in the documentation.
As to your replication is that binary replication or logical replication?
Also you have not said where you are measuring the size growth in WAL files?
>
> Regards,
>
> Ram Pratap.
>
> -----Original Message-----
> From: Ram Pratap Maurya
> Sent: 19 April 2022 09:00
> To: Adrian Klaver <[email protected]>; [email protected]
> Cc: Manu Saxena <[email protected]>
> Subject: RE: Huge archive log generate in Postgresql-13
>
> Dear Adrian,
>
> We have two replica system one is Slave and other id DR server .
>
> Salve server replicate from PG_WAL and DR system replicate from
> pg_log_archive.
>
> Can you please suggest what changes need to required in PG13 conf file.
>
> Regards,
>
> Ram Pratap.
>
> -----Original Message-----
>
> From: Adrian Klaver [mailto:[email protected]
> <mailto:[email protected]>]
>
> Sent: 18 April 2022 21:30
>
> To: Ram Pratap Maurya <[email protected]
> <mailto:[email protected]>>; [email protected]
> <mailto:[email protected]>
>
> Cc: Manu Saxena <[email protected]
> <mailto:[email protected]>>
>
> Subject: Re: Huge archive log generate in Postgresql-13
>
> On 4/18/22 04:34, Ram Pratap Maurya wrote:
>
> > Hi Support,
>
> >
>
> > We have upgraded postgresql DB from version 11 to 13 . after upgrade
>
> > to
>
> > 13 huge archive log generate in system .
>
> >
>
> > Before upgrade per day 120GB to 150 GB log generated but after
>
> > upgrade per day approx. 250 to 300 GB log generated.
>
> Where are you measuring this in the WAL directory or the archive directory?
>
> Do you have replication set up from this server and if so what type?
>
> FYI, the wal_level setting of hot_standby is deprecated and maps to
> replica since version 9.6. At some point you might want to change to
> match current documentation.
>
> >
>
> > Can you please suggest why huge archive log generated after upgrade
>
> > there any configure setting or this is Postgresql-13 behaviour.
>
> >
>
> > Postgresql-13 Postgresql conf file attached for your references.
>
> >
>
> > Regards,
>
> >
>
> > Ram Pratap.
>
> >
>
> --
>
> Adrian Klaver
>
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 17:20 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
@ 2022-04-20 04:31 ` Ram Pratap Maurya <[email protected]>
2022-04-21 16:04 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-20 04:31 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Dear Adrian,
We are using binary replication and par day pg_log_archive total size is increase after upgrade for PG11 to PG13.
Regards,
Ram Pratap.
-----Original Message-----
From: Adrian Klaver [mailto:[email protected]]
Sent: 19 April 2022 22:51
To: Ram Pratap Maurya <[email protected]>; [email protected]
Subject: Re: Huge archive log generate in Postgresql-13
On 4/18/22 21:41, Ram Pratap Maurya wrote:
> Dear Adrian,
>
> If we set parameter *"PG-WAL = replica"* in PG13 conf file , there
> is any issue in replication through PG_WAL and pg_log_archive log.
>
In versions of Postgres from 9.6+ your setting of hot_standby is mapped to the replica setting so it is really not a change. In other words the hot_standby setting = replica setting as of 9.6. I made the suggestion to change just so the conf setting matches the choices in current documentation. Less confusion for someone looking through the postgresql.conf file and then looking up the values in the documentation.
As to your replication is that binary replication or logical replication?
Also you have not said where you are measuring the size growth in WAL files?
>
> Regards,
>
> Ram Pratap.
>
> -----Original Message-----
> From: Ram Pratap Maurya
> Sent: 19 April 2022 09:00
> To: Adrian Klaver <[email protected]<mailto:[email protected]>>;
> [email protected]<mailto:[email protected]>
> Cc: Manu Saxena <[email protected]<mailto:[email protected]>>
> Subject: RE: Huge archive log generate in Postgresql-13
>
> Dear Adrian,
>
> We have two replica system one is Slave and other id DR server .
>
> Salve server replicate from PG_WAL and DR system replicate from
> pg_log_archive.
>
> Can you please suggest what changes need to required in PG13 conf file.
>
> Regards,
>
> Ram Pratap.
>
> -----Original Message-----
>
> From: Adrian Klaver [mailto:[email protected]
> <mailto:[email protected]>]
>
> Sent: 18 April 2022 21:30
>
> To: Ram Pratap Maurya <[email protected]
> <mailto:[email protected]>>;
> [email protected]<mailto:[email protected]> <mailto:[email protected]>
>
> Cc: Manu Saxena <[email protected]
> <mailto:[email protected]>>
>
> Subject: Re: Huge archive log generate in Postgresql-13
>
> On 4/18/22 04:34, Ram Pratap Maurya wrote:
>
> > Hi Support,
>
> >
>
> > We have upgraded postgresql DB from version 11 to 13 . after
> upgrade
>
> > to
>
> > 13 huge archive log generate in system .
>
> >
>
> > Before upgrade per day 120GB to 150 GB log generated but after
>
> > upgrade per day approx. 250 to 300 GB log generated.
>
> Where are you measuring this in the WAL directory or the archive directory?
>
> Do you have replication set up from this server and if so what type?
>
> FYI, the wal_level setting of hot_standby is deprecated and maps to
> replica since version 9.6. At some point you might want to change to
> match current documentation.
>
> >
>
> > Can you please suggest why huge archive log generated after upgrade
>
> > there any configure setting or this is Postgresql-13 behaviour.
>
> >
>
> > Postgresql-13 Postgresql conf file attached for your references.
>
> >
>
> > Regards,
>
> >
>
> > Ram Pratap.
>
> >
>
> --
>
> Adrian Klaver
>
> [email protected]<mailto:[email protected]> <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]<mailto:[email protected]>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 17:20 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-20 04:31 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2022-04-21 16:04 ` Adrian Klaver <[email protected]>
2022-04-22 03:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2022-04-21 16:04 UTC (permalink / raw)
To: Ram Pratap Maurya <[email protected]>; [email protected] <[email protected]>
On 4/19/22 21:31, Ram Pratap Maurya wrote:
> Dear Adrian,
>
> We are using binary replication and par day *pg_log_archive* total size
> is increase after upgrade for PG11 to PG13.
So what is pruning the WAL's in pg_log_archive?
Or to put it another way what is the purpose of pg_log_archive and how
is it managed?
>
> Regards,
>
> Ram Pratap.
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Huge archive log generate in Postgresql-13
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 17:20 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-20 04:31 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-21 16:04 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
@ 2022-04-22 03:41 ` Ram Pratap Maurya <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Ram Pratap Maurya @ 2022-04-22 03:41 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Dear Adrian,
We are using "pg_log_archive" for Point-in-Time Recovery and DR replication.
Regards,
Ram Pratap.
-----Original Message-----
From: Adrian Klaver [mailto:[email protected]]
Sent: 21 April 2022 21:35
To: Ram Pratap Maurya <[email protected]>; [email protected]
Subject: Re: Huge archive log generate in Postgresql-13
On 4/19/22 21:31, Ram Pratap Maurya wrote:
> Dear Adrian,
>
> We are using binary replication and par day *pg_log_archive* total
> size is increase after upgrade for PG11 to PG13.
So what is pruning the WAL's in pg_log_archive?
Or to put it another way what is the purpose of pg_log_archive and how is it managed?
>
> Regards,
>
> Ram Pratap.
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Server unable to UP after restore
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
@ 2023-07-21 04:06 ` Ram Pratap Maurya <[email protected]>
2024-08-27 08:50 ` After DB upgrade from PG13 to PG15 showing error Ram Pratap Maurya <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Ram Pratap Maurya @ 2023-07-21 04:06 UTC (permalink / raw)
To: '[email protected]' <[email protected]>
Hi Team,
I am facing error during restore of PG_BASEBACKUP , server is not going UP after restoring .
We follow below process .
1. First we take backup by command : pg_basebackup -h127.0.01 -U postgres -D /mnt/backup
2. Maintain restore_command = 'cp /var/lib/pgsql/13/data/pg_log_archive/%f %p' in postgresql.conf file .
3. Create file recovery.signal file in data directory.
But when we starting server showing error below error , can you please suggest .
[root@NHQ-MYLAVA-DB01 data]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2023-07-21 09:00:15 IST; 17s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 8407 ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
Process: 8401 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8407 (code=exited, status=1/FAILURE)
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: Starting PostgreSQL 13 database server...
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: postgresql-13.service: Main process exited, code=exited, status=1/FAILURE
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: postgresql-13.service: Failed with result 'exit-code'.
Jul 21 09:00:15 NHQ-MYLAVA-DB01 systemd[1]: Failed to start PostgreSQL 13 database server.
Regards,
Ram Pratap.
^ permalink raw reply [nested|flat] 17+ messages in thread
* After DB upgrade from PG13 to PG15 showing error
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2023-07-21 04:06 ` Server unable to UP after restore Ram Pratap Maurya <[email protected]>
@ 2024-08-27 08:50 ` Ram Pratap Maurya <[email protected]>
2024-08-27 08:52 ` Re: After DB upgrade from PG13 to PG15 showing error Kashif Zeeshan <[email protected]>
2024-08-27 16:13 ` Re: After DB upgrade from PG13 to PG15 showing error Adrian Klaver <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Ram Pratap Maurya @ 2024-08-27 08:50 UTC (permalink / raw)
To: '[email protected]' <[email protected]>
Dear Team,
We have upgraded PostgreSQL DB from version 13 to 15 version .
We are facing issue in PG15 we not enable any parameter related to AUTOVACUUM , but still running on PG15 data base.
Below process showing :
2313192 | 02:10:01.283176 | | myLava | active | autovacuum: VACUUM ANALYZE public.tstock_movement (to prevent wraparound)
And one more issue we are facing after upgrade lot of alert is coming in DB log file , please suggest this is bug in Postgresql-15 version.
2024-08-26 00:00:36.783 IST [702937] WARNING: oldest xmin is far in the past
2024-08-26 00:00:36.783 IST [702937] HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2024-08-26 00:00:36.784 IST [702937] WARNING: oldest xmin is far in the past
2024-08-26 00:00:36.784 IST [702937] HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
DB OS -RHEL8.
Regards,
Ram Pratap.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After DB upgrade from PG13 to PG15 showing error
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2023-07-21 04:06 ` Server unable to UP after restore Ram Pratap Maurya <[email protected]>
2024-08-27 08:50 ` After DB upgrade from PG13 to PG15 showing error Ram Pratap Maurya <[email protected]>
@ 2024-08-27 08:52 ` Kashif Zeeshan <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Kashif Zeeshan @ 2024-08-27 08:52 UTC (permalink / raw)
To: Ram Pratap Maurya <[email protected]>; +Cc: [email protected] <[email protected]>
Hi
On Tue, Aug 27, 2024 at 1:50 PM Ram Pratap Maurya <
[email protected]> wrote:
> Dear Team,
>
>
>
> We have upgraded PostgreSQL DB from version 13 to 15 version .
>
> We are facing issue in PG15 we not enable any parameter related to
> AUTOVACUUM , but still running on PG15 data base.
>
>
>
> Below process showing :
>
>
>
> *2313192 | 02:10:01.283176 | | myLava | active |
> autovacuum: VACUUM ANALYZE public.tstock_movement (to prevent wraparound)*
>
>
>
> And one more issue we are facing after upgrade lot of alert is coming in
> DB log file , please suggest this is bug in Postgresql-15 version.
>
>
>
> *2024-08-26 00:00:36.783 IST [702937] WARNING: oldest xmin is far in the
> past*
>
> *2024-08-26 00:00:36.783 IST [702937] HINT: Close open transactions soon
> to avoid wraparound problems.*
>
> * You might also need to commit or roll back old prepared
> transactions, or drop stale replication slots.*
>
> *2024-08-26 00:00:36.784 IST [702937] WARNING: oldest xmin is far in the
> past*
>
> *2024-08-26 00:00:36.784 IST [702937] HINT: Close open transactions soon
> to avoid wraparound problems.*
>
> * You might also need to commit or roll back old prepared
> transactions, or drop stale replication slots.*
>
These are warnings not error, can you please share the error you are
getting.
Thanks
Kashif Zeeshan
>
>
> *DB OS -RHEL8.*
>
>
>
>
>
> Regards,
>
> Ram Pratap.
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: After DB upgrade from PG13 to PG15 showing error
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Re: Huge archive log generate in Postgresql-13 Adrian Klaver <[email protected]>
2022-04-19 03:29 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` RE: Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2023-07-21 04:06 ` Server unable to UP after restore Ram Pratap Maurya <[email protected]>
2024-08-27 08:50 ` After DB upgrade from PG13 to PG15 showing error Ram Pratap Maurya <[email protected]>
@ 2024-08-27 16:13 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Adrian Klaver @ 2024-08-27 16:13 UTC (permalink / raw)
To: Ram Pratap Maurya <[email protected]>; '[email protected]' <[email protected]>
On 8/27/24 01:50, Ram Pratap Maurya wrote:
> Dear Team,
>
> We have upgraded PostgreSQL DB from version 13 to 15 version .
>
> We are facing issue in PG15 we not enable any parameter related to
> AUTOVACUUM , but still running on PG15 data base.
You need to provide more detail about the above.
What parameters are you trying to change?
Do you get an error?
Did you reload/restart the server after the changes?
What user did you do the changes as?
> *2024-08-26 00:00:36.783 IST [702937] WARNING: oldest xmin is far in
> the past*
>
> *2024-08-26 00:00:36.783 IST [702937] HINT: Close open transactions
> soon to avoid wraparound problems.*
>
> * You might also need to commit or roll back old prepared
> transactions, or drop stale replication slots.*
I would say the above is pointing you at possible solutions.
To understand why this is important:
https://www.postgresql.org/docs/16/routine-vacuuming.html
Read the following sections:
25.1.5. Preventing Transaction ID Wraparound Failures
25.1.6. The Autovacuum Daemon
>
> Regards,
>
> Ram Pratap.
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-08-27 16:13 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-18 11:34 Huge archive log generate in Postgresql-13 Ram Pratap Maurya <[email protected]>
2022-04-18 11:43 ` Mladen Gogala <[email protected]>
2022-04-18 11:47 ` Ram Pratap Maurya <[email protected]>
2022-04-18 12:10 ` Holger Jakobs <[email protected]>
2022-04-18 19:15 ` Clive Swan <[email protected]>
2022-04-21 05:54 ` Ram Pratap Maurya <[email protected]>
2022-04-18 16:00 ` Adrian Klaver <[email protected]>
2022-04-19 03:29 ` Ram Pratap Maurya <[email protected]>
2022-04-19 04:41 ` Ram Pratap Maurya <[email protected]>
2022-04-19 17:20 ` Adrian Klaver <[email protected]>
2022-04-20 04:31 ` Ram Pratap Maurya <[email protected]>
2022-04-21 16:04 ` Adrian Klaver <[email protected]>
2022-04-22 03:41 ` Ram Pratap Maurya <[email protected]>
2023-07-21 04:06 ` Server unable to UP after restore Ram Pratap Maurya <[email protected]>
2024-08-27 08:50 ` After DB upgrade from PG13 to PG15 showing error Ram Pratap Maurya <[email protected]>
2024-08-27 08:52 ` Re: After DB upgrade from PG13 to PG15 showing error Kashif Zeeshan <[email protected]>
2024-08-27 16:13 ` Re: After DB upgrade from PG13 to PG15 showing error Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox