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 3D0B711F6F4C for ; Sun, 2 Apr 2006 13:15:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94973-06 for ; Sun, 2 Apr 2006 13:15:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id 4C07F11F6E80 for ; Sun, 2 Apr 2006 13:14:59 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id g2so998256nfe for ; Sun, 02 Apr 2006 09:14:59 -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:content-transfer-encoding:content-disposition:references; b=WRMFpjxomXc4JSVqnfAV93d6NdCG62XPCnNS/bmtjYCHTZ2m6LvbAisWKamDAr4a7LPGuiyRfo35qK/+g90EBeG+aI1IpOrYEPa2cbtxbqZPgwdXdnQr5iUr952RvVb3VkIlJKkJFcp/rNW/Wugcg1W34Tf+dTMXiLJZJbQjgyo= Received: by 10.49.3.13 with SMTP id f13mr668587nfi; Sun, 02 Apr 2006 09:14:58 -0700 (PDT) Received: by 10.49.26.20 with HTTP; Sun, 2 Apr 2006 09:14:58 -0700 (PDT) Message-ID: Date: Sun, 2 Apr 2006 21:44:58 +0530 From: "Rajesh Kumar Mallah" To: "Grega Bremec" Subject: Re: FW: Setting up of PITR system. Cc: pgsql-admin@postgresql.org In-Reply-To: <442FDC7F.9010001@p0f.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <442B93DD.4020305@p0f.net> <442E1945.4030006@p0f.net> <442FDC7F.9010001@p0f.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.506 required=5 tests=[AWL=0.506] X-Spam-Score: 0.506 X-Spam-Level: X-Archive-Number: 200604/17 X-Sequence-Number: 21186 On 4/2/06, Grega Bremec wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > Rajesh Kumar Mallah wrote: > | > | Instead of taking a round about method i am using the content of the > | file, (This was also suggested by Andy at somepoint) > > After reading the docs again, that's what I would do as well, indeed. :) > > | lately i feel that we should not be discussing the topic over here > | becoz it has less to do with postgresql and more of bash. > > I've been considering that seriously in the very first post I wrote, but > since there seems to be a lot of people interested in a working, > flexible WAL archiving script, I decided to keep it on the list. It is > an administration issue, afterall. I will stand corrected if someone > feels we're clogging their mailboxes. > > | ############################################################## > | #START WAL LOCATION: E/A9145E4 (file 000000010000000E0000000A) > | #CHECKPOINT LOCATION: E/A92939C > | #START TIME: 2006-04-01 14:36:48 IST > | #LABEL: base_backup_01-04-2006-14-36-45 > | ############################################################### > | > | BACKUP_LABEL=3D$DATADIR/backup_label > | # get the like containing line START WAL LOCATION > | > | START_LINE=3D`grep -i "START WAL LOCATION" $BACKUP_LABEL` > | # strip something like 'START WAL LOCATION: E/A9145E4 (file ' from begi= n. > | START_LINE=3D${START_LINE/#START*file /} > | # strip ')' from end. > | START_LINE=3D${START_LINE/%)/} > | # REF_FILE_NUM is something like 000000010000000A00000068 > | REF_FILE_NUM=3D$START_LINE > > Why not go for the entire filename? it takes a while (i dont know how much) for the .backup file to get archived and appear in the wal archive area. thats why i prefer to use the wal log filename (000000010000000A00000068) instead of something like 000000010000000A00000068.0A348A45.backup. Do you see any problem in the current approach ? i have seen it working fine till now. Another area i was thinking to improve this script was to make it dig out all the tablespace folders to be archived by looking into PGDATADIR/pg_tblspc . This shall make the script more generic. pg_tblspc as contents like below: $ ls -l total 0 lrwxrwxrwx 1 postgres postgres 19 Mar 27 21:45 16391 -> /mnt/indexspace_new lrwxrwxrwx 1 postgres postgres 18 Mar 27 21:45 16392 -> /mnt/bigtables_new can you suggest the sane/recommended way to get the destination folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf "%??????= ?" ) or do i parse output of ls ! if above is done i see the script INPUT/OUTPUT'ACTIVITY as below INPUTS: PGDATADIR , WAL_ARCHIVE_DIR , LOCAL DUMP DIRECTORY OUTPUT: n/a ACTIVITY: 1. it shall take a base backup of PGDATADIR (minus pg_xlog) and all tablespaces into *LOCAL* DUMP Directory 2. Frees disk space by removing unwanted LOG files in WAL_ARCHIVE_DIR i am waiting for my another machine to get fitted with 2 more drives so that i can test/develop scripts for the restoration part and modify the script for remote base backups and archiving. that shall happen in a week or so. thanks for your support till now. Regds Rajesh Kumar Mallah. > > Kind regards, > - -- > ~ Grega Bremec > ~ gregab at p0f dot net > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.0 (GNU/Linux) > > iD8DBQFEL9x/fu4IwuB3+XoRA3IgAJ9Qn7dYsNhv3e9f+P64mJoiz+s77gCeLELY > 4xAxFb3Ncd8RHWkBbgyag7U=3D > =3D7MXQ > -----END PGP SIGNATURE----- >