Thread: WAL log archive frequency
Hi, Further to my question on PITR, I have now implemented it :-) However, I was wondering about the frequency of archiving the WAL. Does postgresql wait until the current WAL file has reached 16MB before calling the archive_command? The reason why I ask is that last night, after doing the base backup, I noticed that a WAL file had been written to my backup server. This morning, I added a primary key to a table but noticed that nothing was written to the backup server. Should it have been? Is there any method of forcing a WAL segment to be archived at a given frequency irrespective of whether or not it is a full 16MB? Our database changes so infrequently at the moment that I'm worried that these small changes may not be backed up regularly by the PITR system. Thanks, Chris -- Chris Jewell, BSc(Hons), BVSc, MRCVS Dept of Maths and Statistics Fylde College Lancaster University Lancaster Lancs LA1 4YF
Chris Jewell <c.jewell@lancaster.ac.uk> writes:
> Further to my question on PITR, I have now implemented it :-) However,
> I was wondering about the frequency of archiving the WAL. Does
> postgresql wait until the current WAL file has reached 16MB before
> calling the archive_command?
Yes. The assumption is the archive_command may be too stupid to deal
with archiving the same file more than once, and/or might have
performance issues with doing that, eg, if it's writing to tape or some
kind of write-once media.
You can do something like saving the newest-by-timestamp file in the
xlog directory every minute or whatever via a cron job. This ought to
be better integrated though ...
regards, tom lane
Hello, I have done a silent installation, the installation worked eventually, and I can use pgAdmin interface to connect to it. However I have problem using psql.exe to connect to the database. I need to create a database called delphie from command line. It should be a part of the installation I'm working on. I used the follwing commands psql.exe -U root but it keeps giving me: psql: FATAL: password authentication failed for user "root" root is the supper user and the password is also root, but not only the program does not prompt for password but also there is no way that I can supply a password through the command line. The ultimate goal is I need to create a database automatically without user interacation. I have a sql script that I need to run to create the tables. I have used Oracle without problems before but not sure how to approach with Postgres. Any help/tip is greatly appreciaged. Sean
Tom Lane wrote: > Chris Jewell <c.jewell@lancaster.ac.uk> writes: > > Further to my question on PITR, I have now implemented it :-) However, > > I was wondering about the frequency of archiving the WAL. Does > > postgresql wait until the current WAL file has reached 16MB before > > calling the archive_command? > > Yes. The assumption is the archive_command may be too stupid to deal > with archiving the same file more than once, and/or might have > performance issues with doing that, eg, if it's writing to tape or some > kind of write-once media. > > You can do something like saving the newest-by-timestamp file in the > xlog directory every minute or whatever via a cron job. This ought to > be better integrated though ... FYI, this is documented. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073