Thread: Another WAL question

Another WAL question

From
"Nick Fankhauser"
Date:
Robert F. mentioned that there is a config parameter controlling the number
of WAL files in postgresql.conf.

I just looked at the Doc on this & I found WAL_FILES, which appears to
specify the number of files to create in advance, but it is no clear to me
that this limits the total number of WAL files created. Can this number be
exceeded as needed by the postmaster? If so, is there another parameter that
I'm missing to set a limit?

Are old WAL files deleted or re-used automatically when all of their
contents have been flushed to the DB, or should we be watching these &
either archiving or deleting old ones?

TIA,

-Nick

---------------------------------------------------------------------
Nick Fankhauser

Business:
nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop  - Court records at your fingertips - http://www.doxpop.com/

Personal:
nick@fankhausers.com   http://www.fankhausers.com


Re: Another WAL question

From
Bruce Momjian
Date:
In 7.1.3 and 7.2, there are only 2-3 WAL files kept because there is no
need to keep them after a checkpoint.  Is there any need to have these
WAL config paramaters anymore?

We currently have in postgresql.conf:

#wal_buffers = 8            # min 4
#wal_files = 0 # range 0-64
#wal_sync_method = fsync   # fsync or fdatasync or open_sync or
open_datasync
# Note: default wal_sync_method varies across platforms
#wal_debug = 0             # range 0-16
#commit_delay = 0          # range 0-100000
#commit_siblings = 5       # range 1-1000
#checkpoint_segments = 3   # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300  # in seconds, range 30-3600


> Robert F. mentioned that there is a config parameter controlling the number
> of WAL files in postgresql.conf.
>
> I just looked at the Doc on this & I found WAL_FILES, which appears to
> specify the number of files to create in advance, but it is no clear to me
> that this limits the total number of WAL files created. Can this number be
> exceeded as needed by the postmaster? If so, is there another parameter that
> I'm missing to set a limit?
>
> Are old WAL files deleted or re-used automatically when all of their
> contents have been flushed to the DB, or should we be watching these &
> either archiving or deleting old ones?
>
> TIA,
>
> -Nick
>
> ---------------------------------------------------------------------
> Nick Fankhauser
>
> Business:
> nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
> doxpop  - Court records at your fingertips - http://www.doxpop.com/
>
> Personal:
> nick@fankhausers.com   http://www.fankhausers.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Another WAL question

From
"Nick Fankhauser"
Date:
> In 7.1.3 and 7.2, there are only 2-3 WAL files kept because there is no
> need to keep them after a checkpoint.

This answers my original question- it sounds like after 3, they get recycled
or deleted, so if I reserve 48Mb (3*16) for these, I should be OK. ...Right?


> Is there any need to have these
> WAL config paramaters anymore?

I'd say all of the parameters you list below still make sense, as they all
control *how* the 2-3 WAL files are used. WAL_FILES might as well be 0-3
instead of 0-64, but I can still see an advantage to creating these in
advance to allocate the space.


> We currently have in postgresql.conf:
>
> #wal_buffers = 8            # min 4
> #wal_files = 0 # range 0-64
> #wal_sync_method = fsync   # fsync or fdatasync or open_sync or
> open_datasync
> # Note: default wal_sync_method varies across platforms
> #wal_debug = 0             # range 0-16
> #commit_delay = 0          # range 0-100000
> #commit_siblings = 5       # range 1-1000
> #checkpoint_segments = 3   # in logfile segments (16MB each), min 1
> #checkpoint_timeout = 300  # in seconds, range 30-3600


Thanks for the good answers on this!

-Nick


Re: Another WAL question

From
Bruce Momjian
Date:
> > In 7.1.3 and 7.2, there are only 2-3 WAL files kept because
> > there is no need to keep them after a checkpoint. Is there
> > any need to have these WAL config paramaters anymore?
>
> I missed what's you propose to remove.

I am not proposing to remove anything.  I just want to make sure they
are all still valid now that we recycle the WAL segments.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Another WAL question (sort of)

From
"Chad R. Larson"
Date:
Two sort of WAL related questions....

1)  Is point-in-time recovery using a pg_dumpall and the associated WAL
files still planned for 7.2?

2)  When is 7.2 planned to be released?
         -crl
--
Chad R. Larson (CRL22)    chad@eldocomp.com
   Eldorado Computing, Inc.   602-604-3100
      5353 North 16th Street, Suite 400
        Phoenix, Arizona  85016-3228


Re: Another WAL question (sort of)

From
Bruce Momjian
Date:
> Two sort of WAL related questions....
>
> 1)  Is point-in-time recovery using a pg_dumpall and the associated WAL
> files still planned for 7.2?

No, sorry, hopefully 7.3.

>
> 2)  When is 7.2 planned to be released?
>          -crl

Nov/Dec?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Another WAL question

From
"Mikheev, Vadim"
Date:
> In 7.1.3 and 7.2, there are only 2-3 WAL files kept because
> there is no need to keep them after a checkpoint. Is there
> any need to have these WAL config paramaters anymore?

I missed what's you propose to remove.

Vadim

Re: Another WAL question

From
"Mikheev, Vadim"
Date:
> > > In 7.1.3 and 7.2, there are only 2-3 WAL files kept because
> > > there is no need to keep them after a checkpoint. Is there
> > > any need to have these WAL config paramaters anymore?
> >
> > I missed what's you propose to remove.
>
> I am not proposing to remove anything.  I just want to make sure
> they are all still valid now that we recycle the WAL segments.

I think they are valid.

Vadim