FW: Setting up of PITR system.

["Andy Shellam" <andy.shellam@xxxxxxxxxxxxxxxxx>]

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