Re: WAL log switch: 8.1.4 and current log

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!