Обсуждение: Huge archive log generate in Postgresql-13

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-language:EN-US;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri","sans-serif"; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri","sans-serif"; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}

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

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
adrian.klaver@aklaver.com

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:adrian.klaver@aklaver.com] 
Sent: 18 April 2022 21:30
To: Ram Pratap Maurya <ram.maurya@lavainternational.in>; pgsql-general@postgresql.org
Cc: Manu Saxena <manu.saxena@lavainternational.in>
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
wantto 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
adrian.klaver@aklaver.com

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 <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
> Cc: Manu Saxena <manu.saxena@lavainternational.in>
> 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:adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>]
> 
> Sent: 18 April 2022 21:30
> 
> To: Ram Pratap Maurya <ram.maurya@lavainternational.in 
> <mailto:ram.maurya@lavainternational.in>>; pgsql-general@postgresql.org 
> <mailto:pgsql-general@postgresql.org>
> 
> Cc: Manu Saxena <manu.saxena@lavainternational.in 
> <mailto:manu.saxena@lavainternational.in>>
> 
> 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
> 
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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
adrian.klaver@aklaver.com

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:adrian.klaver@aklaver.com] 
Sent: 21 April 2022 21:35
To: Ram Pratap Maurya <ram.maurya@lavainternational.in>; pgsql-general@postgresql.org
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
adrian.klaver@aklaver.com