Hi
PITR (Point in Time Recovery) seams like an excellent feature, but I have trouble getting the hang of it.
My environment:
PostgreSQL 8.0.3 on windows 2000.
My procedure:
- Set up archiving with the archive_command. The files are stored on a remote computer in a folder called wal_archive. Looks fine.
- Set up a scheduled task running a script every half hour. The script copies the last modified file in the pg_xlog dir (I call this file current_wal) to the same remote computer, but in a different folder. The script is described later.
- SELECT pg_start_backup('label')
- Copy the data directory to the remote computer. I remove the contents (in the copy) of the pg_xlog directory and archive_status directory. The pg_start_backup function produces a backup history file indicating where in the current WAL segment the backup took place.
Question 1: What do I need to save in my wal_archive. The backup file is automatically placed there. Do I need the WAL segment in which the backup file is based upon? It is not automatically copied to the wal_archive. In that case; what if the backup file is called .....39.00198218.backup and I have no ....39 WAL segment? Do I use the last modified segment, althoug it has a different file name, for example ....3F? Do I rename it???
The DOC tells me that the WAL segments numerically less than the current WAL segment are no longer needed. My base backup gave me a backup history file numbered 39 although all the WAL segments er numerically greater than 39!
Question 2: Should I delete the other WAL segments in the pg_xlog directory, although they are numerically greater?
So, after setting up the backup I now test the recovery process.
I think I'm well off when I have my backup history file, the archived WAL segments, the current_wal_segment on the data directory.
- I replace the data directory, fill the pg_xlog directory with the backup history file and the archived WAL segments. But what about the current_wal_segment produced by the scheduled task? It doesn't seem to fit in. The archived WAL segments are numbered in sequence based upon the backup history file. So the numbers do not match the numbers in the pg_xlog directory. The result of the copy is a current_wal_segment with a number not following the last archived wal.
The result is that I am able to backup to the last archived WAL segment, but I am not anywhere close to recover to the last half hour. Has anyone mastered this?
Per
The script:
@echo off
@setlocal
set PG_WAL_AREA="D:\PostgreSQL\8.0\data\pg_xlog"
set CUR_WAL_AREA="<path to current_wal>"
SET lastfil=
FOR /F %%f in ('DIR %PG_WAL_AREA% /AA /OD /B') do set lastfil=%%f
ECHO. last file in directory is %lastfil%
COPY /Y %PG_WAL_AREA%\%lastfil% %CUR_WAL_AREA%
@endlocal
