Re: FW: Setting up of PITR system. - Mailing list pgsql-admin

On 4/13/06, Grega Bremec <gregab@p0f.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
> Rajesh Kumar Mallah wrote:
> > Is cluttering of the wal archive area in cases where that backup
> > had to be re-started for whatever reasons is the *only* concern ?
>
> Well, yes, to be honest. But it may in consequence cause problems of
> another kind, which I don't feel qualified to reassure you on: I am not
> positive about what happens if you try to replay an old WAL on a current
> database backup.
>
> If nothing else, it is going to make the person restoring the backup
> rather unnerved about success of the operation they are currently
> performing, which is not a good thing, IMHO. They are restoring a backup
> afterall, which means they'd already undergone a fair amount of stress
> as it is. :)
>
> End all, it is your choice to decide which is more trouble and which is
> worth more: fixing the script to produce clean backups or informing your
> backup operators about the extra care they need to take when restoring
> backups.

Dear Grega,

gald to see the clarification.

The concern you have is valid only if something goes wrong
after pg_start_backup() . In such case the backup admin
can get notified in advance by using the MAILTO env variable
in crontab. so its not that we are stressing him in already stressed
situation.  

the reason of my aversion in incorporating you suggestion is however
different. There does not seem to be any realiable way of getting the
name of .backup file from the contents in backup_label. consider the
method you gave.

REF_FILE="`grep 'START WAL LOCATION' ${BACKUP_LABEL} | \
   awk '{
            sub(/)/, "", $6);
            sub(/[0-9A-F]\//, "", $4);
            printf("%s.%08s.backup", $6, $4);
   }'`"

firstly i feel there should be a + after [0-9A-F] , when i run the command on
my data following is the outcome.

-bash-2.05b$ cat  /mnt/disk2/base_backups/pgdatadir/backup_label
START WAL LOCATION: 1C/4C7E5E90 (file 000000010000001C0000004C)
CHECKPOINT LOCATION: 1C/4C824A44
START TIME: 2006-04-12 21:32:43 IST
LABEL: base_backup_12-04-2006-21-32-52

-bash-2.05b$ grep 'START WAL'  /mnt/disk2/base_backups/pgdatadir/backup_label | awk '{ sub (/)/, "", $6); sub(/[0-9A-F]+\//, "", $4); printf("%s.%08s.backup\n", $6, $4 );  }'
000000010000001C0000004C.4C7E5E90.backup

-bash-2.05b$ ls -l /mnt/disk2/wal_archive/*.backup
-rw-------    1 postgres postgres      270 Apr 12 21:53 /mnt/disk2/wal_archive/000000010000001C0000004C.007E5E90.backup


was you can see the predicted name
000000010000001C0000004C.4C7E5E90.backup is not same as
000000010000001C0000004C.007E5E90.backup

one may argue that replacing first 2 charachers of 4C7E5E90 with
'0's may yeild the correct result, but it is not so , i have observed
a case which runs against this hypothesis.

therefore unless someone tells the correct method of derieving
the filename , the current approach is the best that can be done.
I really hope some guru to throw some light here. i tried digging
xlog.c and xlogutils.c without any success.


>
> > Please do not put too much effort, as i the drives in my other
> > server has got installed and i am adapting the script for doing
> > remote backup ( which is a more common senerio).
>
> Very nice! How is it going? And how are you copying the WALs? scp? rsync?

I finished it long back but i was waiting for comments from the list
regarding the concern to which you replied. I think we discuss the
new version once this issue is resolved.

Regds
Rajesh Kumar Mallah.

>
> Kind regards,
> - --
>     Grega Bremec
>     gregab at p0f dot net
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.0 (GNU/Linux)
>
> iD8DBQFEPgU9fu4IwuB3+XoRA+MeAJ0dbbfcgBqP9SCYq0VICN8xrtGN0wCffE6i
> kq1LlDwlJwmfrOtwRBwGqFg=
> =olf3
> -----END PGP SIGNATURE-----
>