X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B449811F6928 for ; Thu, 13 Apr 2006 08:56:52 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.192 X-Spam-Level: X-Spam-Status: No, score=0.192 required=5 tests=[AWL=0.191, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id TTLewgjDpS4R for ; Thu, 13 Apr 2006 08:56:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id 9C85211F6779 for ; Thu, 13 Apr 2006 08:56:45 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id p48so1136152nfa for ; Thu, 13 Apr 2006 04:56:45 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=nudzqxdOJBwQ7HVu7PEXn/ZdTT40XVb1IviTZRb4PTLRn8vUZbhSMYLMvx1bvHWRMFjF80Z1yRpmQF5eNLiKWtQr10HqCaiNr1QvSvGpUbKqzGGHwLBa/m7/0KXKldDLtaf13kw9vzhCj1XwT0hNSYrQVrbC0JWmmncdK3l8Y5M= Received: by 10.48.217.1 with SMTP id p1mr137047nfg; Thu, 13 Apr 2006 04:56:44 -0700 (PDT) Received: by 10.49.27.16 with HTTP; Thu, 13 Apr 2006 04:56:44 -0700 (PDT) Message-ID: Date: Thu, 13 Apr 2006 17:26:44 +0530 From: "Rajesh Kumar Mallah" To: "Grega Bremec" Subject: Re: FW: Setting up of PITR system. Cc: pgsql-admin@postgresql.org In-Reply-To: <443E053D.4080506@p0f.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11333_20456230.1144929404856" References: <442E1945.4030006@p0f.net> <442FDC7F.9010001@p0f.net> <4430BC59.7040006@p0f.net> <4437AF15.3020707@p0f.net> <443E053D.4080506@p0f.net> X-Archive-Number: 200604/120 X-Sequence-Number: 21289 ------=_Part_11333_20456230.1144929404856 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 4/13/06, Grega Bremec 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=3D"`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=3D > =3Dolf3 > -----END PGP SIGNATURE----- > ------=_Part_11333_20456230.1144929404856 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 4/13/06, Grega Bremec <greg= ab@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* conce= rn ?
>
> Well, yes, to be honest. But it may in consequence ca= use problems of
> another kind, which I don't feel qualified to reass= ure you on: I am not
> positive about what happens if you try to replay an old WAL on a c= urrent
> database backup.
>
> If nothing else, it is goi= ng to make the person restoring the backup
> rather unnerved about su= ccess 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 informin= g 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 on= ly if something goes wrong
after pg_start_backup() . In such case the ba= ckup admin
can get notified in advance by using the MAILTO env variable= =20
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 real= iable way of getting the
name of .backup file from the contents in backup_label. consider the method you gave.

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

firstly i feel there should be a + after [0-9A-F] , when i run the comm= and 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 (/)/, "&qu= ot;, $6); sub(/[0-9A-F]+\//, "", $4); printf("%s.%08s.backup\n&qu= ot;, $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 i= n my other
> > server has got installed and i am adapting the scri= pt for doing
> > remote backup ( which is a more common senerio).
>
> Very nice! How is it going? And how are you copying the W= ALs? scp? rsync?

I finished it long back but i was waiting for comme= nts from the list
regarding the concern to which you replied. I think w= e discuss the=20
new version once this issue is resolved.

Regds
Rajesh Kumar Mallah.

>
> Kind regards,
> - --
>= ;     Grega Bremec
>     greg= ab at p0f dot net
> -----BEGIN PGP SIGNATURE-----
> Version: Gn= uPG v1.4.0 (GNU/Linux)
>
> iD8DBQFEPgU9fu4IwuB3+XoRA+MeAJ0dbbfcgBqP9SCYq0VICN8xrtGN0wCffE6i
&g= t; kq1LlDwlJwmfrOtwRBwGqFg=3D
> =3Dolf3
> -----END PGP SIGNATUR= E-----
>
------=_Part_11333_20456230.1144929404856--