Thread: Another WAL question
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
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
> 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
> > 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
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
> 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
> 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
> > > 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