Обсуждение: [BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

Поиск
Список
Период
Сортировка

[BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

От
Cocco Gianfranco
Дата:

Hi,

We have a big problem with managing the number of WAL products, during database activity, so I would try to write to you.

With pg_rman utility, we can’t save thousands of WAL files every few hours, to be used for a possible recovery.

We are using a production environment 9.2 PostgreSQL server which processes a database of about 260 GB

Is there a way to fix “wal_segsize” to about 1 Gb in 9.2. version, and “rebuild” postgreSQL server?

The goal is to drastically reduce the number of WALs.

 

Upgrading to 9.5, is the only way to fix this issue?

 

 

Thank you.

 

 

Descrizione: cid:image001.jpg@01CE4109.E9653C00

_______________________________

 

Gianfranco Cocco

Gruppo DBA Torino

Managed Operations - Data Center Factory

gianfranco.cocco@eng.it

Engineering.MO S.p.A

Corso Mortara, 22 - 10149 Torino

Tel. 011 19449548 (SHORT CODE: 676548)

www.eng.it

 

___________________________

 

Re: [BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

От
Michael Paquier
Дата:
On Fri, Jun 9, 2017 at 10:55 PM, Cocco Gianfranco
<Gianfranco.Cocco@eng.it> wrote:
> Is there a way to fix “wal_segsize” to about 1 Gb in 9.2. version, and “rebuild” postgreSQL server?

As long as you are able to compile your own version of Postgres and
your distribution does not allow that, there is nothing preventing you
to do so.

> The goal is to drastically reduce the number of WALs.
> Upgrading to 9.5, is the only way to fix this issue?

Note that a server initialized with a segment size of X won't work
with a binary compiled with a size of Y. But you can always take a
logical dump of the server before the upgrade, and reload it in the
version of the server with a larger segment size. The cost here is
more downtime.
--
Michael

Re: [BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

От
Jeff Janes
Дата:
On Fri, Jun 9, 2017 at 3:43 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Fri, Jun 9, 2017 at 10:55 PM, Cocco Gianfranco
<Gianfranco.Cocco@eng.it> wrote:
> Is there a way to fix “wal_segsize” to about 1 Gb in 9.2. version, and “rebuild” postgreSQL server?

As long as you are able to compile your own version of Postgres and
your distribution does not allow that, there is nothing preventing you
to do so.

But there is something preventing it.  wal_segsize cannot exceed 64MB in 9.2. v10 will be the first version which will allow sizes above 64MB.

Cheers,

Jeff

Re: [BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

От
Michael Paquier
Дата:
On Tue, Jun 13, 2017 at 2:27 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> But there is something preventing it.  wal_segsize cannot exceed 64MB in
> 9.2. v10 will be the first version which will allow sizes above 64MB.

Yes, indeed. I have misread --with-segsize and --with-wal-segsize in
the docs. Sorry for the confusion.
--
Michael

R: [BUGS] Invalid WAL segment size. Allowed values are1,2,4,8,16,32,64

От
Cocco Gianfranco
Дата:

Thank you.

If I understand it well, we can build a new postgreSQL server, by setting this value into "configure" file?

 

--with-wal-segsize=SEGSIZE   becomes  --with-wal-segsize=1024  ??

 

 

_______________________________

 

Gianfranco Cocco

Gruppo DBA Torino

Managed Operations - Data Center Factory

gianfranco.cocco@eng.it

Engineering.MO S.p.A

Corso Mortara, 22 - 10149 Torino

Tel. 011 19449548 (SHORT CODE: 676548)

www.eng.it

 

___________________________

 

-----Messaggio originale-----
Da: Michael Paquier [mailto:michael.paquier@gmail.com]
Inviato: lunedì 12 giugno 2017 23:26
A: Jeff Janes <jeff.janes@gmail.com>
Cc: Cocco Gianfranco <Gianfranco.Cocco@eng.it>; pgsql-performance@postgresql.org; pgsql-bugs@postgresql.org; DBA <DBA@italiaonline.it>
Oggetto: Re: [BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

 

On Tue, Jun 13, 2017 at 2:27 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

> But there is something preventing it.  wal_segsize cannot exceed 64MB

> in 9.2. v10 will be the first version which will allow sizes above 64MB.

 

Yes, indeed. I have misread --with-segsize and --with-wal-segsize in the docs. Sorry for the confusion.

--

Michael

Re: [BUGS] Invalid WAL segment size. Allowed values are 1,2,4,8,16,32,64

От
Michael Paquier
Дата:
On Tue, Jun 13, 2017 at 6:10 PM, Cocco Gianfranco
<Gianfranco.Cocco@eng.it> wrote:
> If I understand it well, we can build a new postgreSQL server, by setting
> this value into "configure" file?
>
> --with-wal-segsize=SEGSIZE   becomes  --with-wal-segsize=1024  ??

Yes, but as Jeff has pointed out upthread, this value can just go up
to 64 when using 9.5.
--
Michael