Re: PITR problems - Mailing list pgsql-general

Hi,

I can tell you how I do this on Linux:

1. Set up the archiving command in postgresql.conf. Archiving of complete WAL files (that is, of WALs whose 16M space
iscompletely filled) starts automatically after restarting the engine. No renaming, no nothing else with complete WALs.
Nodeleting also. You__ll need all the WALs since the time of the full backup for recovery!
 
2. Copy the database with the pg_start_backup feature. Clean up pg_xlog in that copy.
3. Run a cronjob that finds the latest incomplete WAL and copy it also into the archive (but into a subdirectory, not
toconfuse with the complete WALs).
 

I copy the full backup to another machine once I do the backup, usually once at night. Also I copy the WAL archive
includingthe incomplete WAL to that other machine once per minute while taking the incomplete WAL with rsync. Also no
renaming.

To start the backup database on that other machine I have written a script that basically does the following:

0. Shutdown an eventually running postgresql engine. Shutdown the interface connecting to the "master" to prevent
furthercopying of WALs.
 
1. Wipe the postgresql data directory and copy the full backup to that directory, not including any WALs that might be
withinthe full backup. pg_xlog must be empty except an empty directory "archive_status".
 
2. Create a file "recovery.conf" in the data directory containing the single line 'cp /archivedirectory/%f %p'.
/archivedirectorycontains all the archived WALs including the incomplete last one. The oldest one must be at least as
oldas the start time of the full backup. I usually keep some even older ones, so I have at least one complete WAL, that
isolder than the full backup.
 
3. Start postgresql. It will recover with all the WALs from the archive directory, starting with the one the full
backuprequires and using all the WALs piece by piece up to and including the last incomplete one. Incomplete does NOT
mean,that this WAL is shorter than 16M, but the 16M space is not completely filled.
 

If everything goes well, you have a running consistent database. You may now start the interface to the "master"
again.
    
Recovery might take some minutes due to data masses of WALs. For example, I have about 160M of WALs per hour.

Regards, Frank.



On Tue, 5 Jul 2005 15:24:35 +0200 Per Lauvås <per.lauvaas@mintra.no> thought long, then sat down and wrote:

> 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_statusdirectory. The pg_start_backup function produces a backup history file indicating where in the current
WALsegment 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
WALsegment in which the backup file is based upon? It is not automatically copied to the wal_archive. In that case;
whatif 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
backupgave 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
datadirectory.
 
> - 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
WALsegments are numbered in sequence based upon the backup history file. So the numbers do not match the numbers in the
pg_xlogdirectory. 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
thelast 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
> 
> 
> 
> 
> 


-- 
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606    Mail: frank.finner@invenius.de
Telefax: 0271 231 8608    Web:  http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF  6E6A A74E 67E4 E788 2651