FW: Setting up of PITR system.

we want to remove archived WAL log files which are no longer needed
so that the size of wal_archive folder is under control.

The DOCs say that filenames numerically less than the WAL record that
pg_stop_backup()
suggests can be removed. Will an alphabetical sorting be different from
numerically sorted
filename ? Sorting numerically is not easy as the filenames look like HEX
numbers
ut they are are so huge that they cant be stored in normal integers
(32bits).

The WAL files are all numerical - however the backup file has the checkpoint
appended to it - eg. 00009012514000916.A0AC91.backup.

You need to examine the contents of this file to find the earliest file
needed (it's usually the one just before it - i.e. 00009012514000916 in
this case) and any numerically later than the .backup file.

Also you wouldn't particular need any heavy programming - I'm sure a simple
shell script could be written in bash to pick out the correct files.

Q2. We are attempting to automate the process of taking base backup and
removal
of the unneeded WAL files. Is there any reliable way of knowing the WAL
file X from
inside the shell script , such that files prior to X can be removed ?

from a shell script we issue pg_stop_backup() by psql -c , it returns
something
which does not looks like a WAL file.

Pg_stop_backup() returns the checkpoint record - something like 9/A0AC91 -
this is purely a guess, but you might be able to find which backup file
contains this checkpoint by taking the digits after the forward-slash in the
checkpoint (i.e. A0AC91 in this case), then finding the filename that
contains this - in my example it's 00009012514000916.A0AC91.backup, and grep
the file for the number after the text "Start WAL Location: " in this file -
then remove anything numerically less.

It'd be a very "bitty" process, but I'm certain it could be done - it would
need heavy testing over a period of backups though to ensure the wrong files
are not being deleted.

Q3. tar exits with non zero status for the same reasons as mentioned in docs
is there any better archiving tool for this purpose ? can we use cp -a
?

The WAL archive command can be set to use either cp or mv - then why not
have a regular cronjob that runs a shell script to add any new files to your
tar archive every time a new file is detected in your WAL-archive directory?

Regds
Rajesh Kumar Mallah.

!DSPAM:14,44293ed135042000516834!

Regards

Andy Shellam