Re: WAL log switch: 8.1.4 and current log

[Andy Shellam <andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk>]

Hi Robin,

No there is no command to switch the current WAL file.  There are a 
number of things you could do.
You could rsync the current WAL file (i.e. the last modified file in the 
pg_xlog directory) every 5 minutes or so.

However, depending on server usage, it isn't going to be long after a 
backup that the WAL file is archived.  For example on my own server, I 
get roughly 50 - 60 WAL files each day, so after a full backup, I have 
at most half an hour to wait until the current file is archived.  And I 
wouldn't say that my server is heavily used.

Really as long as your archive command is set up correctly, it's very 
rare that you'd suffer because the current WAL file hasn't been 
archived.  At worst what you can do is, until that first WAL log is 
archived AFTER your backup, keep around all the WAL logs since your 
PREVIOUS full backup - then in the event, you can recover that backup 
and roll forward every log since then.

The best thing would be to test it - on your system (while transactions 
are taking place) take a full backup, examine the *.backup file produced 
in your xlog archive for the "ending WAL location" filename, and time 
how long it takes until that file is archived.  Do it a couple of times 
at different times of the day and you'll see roughly how much data 
you'll lose on the off-chance the server fails immediately after a full 
backup.

Hope this helps some!

Andy.

robin(dot)c(dot)smith(at)bt(dot)com wrote:
> I've looked through the archive on the mailing list and various 
> discussions there seem to indicate that with postgreSQL as of 8.1.4 
> there is no command to switch the current WAL file to archive it after 
> a backup has completed. Is this true?
>
> Is there any catalog view that shows you the current WAL file? - I 
> couldn't see this mentioned when I looked through the documentation.
>
> Regards
>
> Robin Smith
> BT XJE4JA
> Tel. 01992 897013
> robin(dot)c(dot)smith(at)bt(dot)com
>
> British Telecommunications plc     Registered office: 81 Newgate 
> Street London EC1A 7AJ
>
> Registered in England no. 1800000
>
> This electronic message contains information from British 
> Telecommunications plc which may be privileged and confidential. The 
> information is intended to be for the use of the individual(s) or 
> entity named above. If you are not the intended recipient, be aware 
> that any disclosure, copying, distribution or use of the contents of 
> this information is prohibited. If you have received this electronic 
> message in error, please notify us by telephone or e-mail (to the 
> number or address above) immediately.
>
>
> !DSPAM:37,44e1dce6143291969613451!