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 CC37411F6B4D for ; Sat, 1 Apr 2006 00:23:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67214-04 for ; Sat, 1 Apr 2006 00:23:44 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id 5C51911F66DC for ; Sat, 1 Apr 2006 00:23:42 -0400 (AST) Received: by nproxy.gmail.com with SMTP id l24so656783nfc for ; Fri, 31 Mar 2006 20:23:41 -0800 (PST) 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=HRpueNcy1Vaawu4OxLg5P4b7yuMYrhuta3vJXujQonK8vhuQJl8Jkc4IFcpGT8vMPJiHghQg4pYiIsKl7rIgsf1ZIxi8wDbM3F5UxjK+HZJet4iDg2CpDmJnO+cCyIc1EfSwGnEhdpto62YYR3s90ruBoG5+MrhYEB1uXjRww28= Received: by 10.48.209.14 with SMTP id h14mr784423nfg; Fri, 31 Mar 2006 20:23:41 -0800 (PST) Received: by 10.49.26.20 with HTTP; Fri, 31 Mar 2006 20:23:41 -0800 (PST) Message-ID: Date: Sat, 1 Apr 2006 09:53:41 +0530 From: "Rajesh Kumar Mallah" To: "Grega Bremec" Subject: Re: FW: Setting up of PITR system. Cc: pgsql-admin@postgresql.org In-Reply-To: <442B93DD.4020305@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> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.663 required=5 tests=[AWL=0.663] X-Spam-Score: 0.663 X-Spam-Level: X-Archive-Number: 200604/1 X-Sequence-Number: 21170 On 3/30/06, Grega Bremec wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > Rajesh Kumar Mallah wrote: > | > | OK i am posting my full script [ its not heavy programming i guess :) ] > | shall be grateful if you/someone could review it . (its well > commented i think) > | script also carries sample data. > | > | it does following > | 1. takes base backup to a destined folder by rsync > | 2. waits for .backup file to arrive in archive folder > | after pg_stop_bacup() > | 3. searches and removes unwanted archived log files. > | > | I have run it many times in my server and it seems to > | be working fine. > > Hello, Rajesh. > > Just a couple of comments on the script itself, not what it actually > does - I never tried WAL archiving before, so I can't comment on that. I > inserted the comments at relevant points in the script. I'm sorting them > into three categories, one is just improvements in style, the other is > optimization and the third is correction of an error. Dear Grega , Thanks for the useful tips and error spotting, i am incorporating some of them and testing the script in my server . I have concerns regarding some of your optimisations that makes the script less generic , below find my comments. > > | ------------------------------------ BEGIN > | -------------------------------------------- > | #!/bin/bash > | > | # folder where base_backup is put > | BACKUPFOLDER=3D/mnt/disk3/base_backups > | today=3D`date +%d-%m-%Y-%H-%M-%S` > | PSQL=3D/opt/usr/local/pgsql/bin/psql > | RSYNC=3D"/usr/bin/rsync -a" > | PGDATADIR=3D/mnt/disk5/pgdatadir > | > | # two table spaces. > | > | TS1=3D/mnt/disk4/bigtables > | TS2=3D/mnt/disk3/indexspace > > (optimization) Since you're using bash, you can use arrays. This could > be better written as > > ~ TS[0]=3D/mnt/disk5/pgdatadir > ~ TS[1]=3D/mnt/disk4/bigtables > ~ TS[2]=3D/mnt/disk3/indexspace > > or even > > ~ TS=3D(/mnt/disk5/pgdatadir \ > ~ /mnt/disk4/bigtables \ > ~ /mnt/disk3/indexspace) agreed , already incorporated. > > That way, you can add tablespaces at will and just use a while loop to > back them up, which greatly simplifies adding new tablespaces or moving > the script somewhere else. See below for how to implement that. > > | # folder where *archived* logs are put. > | WAL_ARCHIVE=3D/mnt/wal_archive > | > | label=3Dbase_backup_${today} > | > | echo "Executing pg_start_backup with label $label in server ... " > | > | # get the checkpoint at which backup starts > | # the .backup files seems to be bearing this string in it. > | > | CP=3D`$PSQL -q -Upostgres -d template1 -c "SELECT > | pg_start_backup('$label');" -P tuples_only -P format=3Dunaligned` > | > | echo "Begin CheckPoint is $CP" # this contain string like A/681D1214 > | > | if [ $? -ne 0 ] > | then > | echo "PSQL pg_start_backup failed" > | exit 1; > | fi > | echo "pg_start_backup executed successfully" > > (style) If you want to capture any error messages pg_start_backup may > have caused and store them into ${CP}, you should add 2>&1 at the end of > the psql invocation, see below snippet. Incorporated it. > > (error) Checking for exit status of pg_start_backup using $? at this > point will never report an error, as you've used echo prior to checking > what pg_start_backup returned. You should either move the echo below the > if statement (by adding an "else" clause) or store the exit status of > pg_start_backup into RVAL like this: > > ~ CP=3D"`$PSQL ... 2>&1`" > ~ RVAL=3D$? > ~ echo "Begin CheckPoint says: ${CP}" > ~ if [ ${RVAL} -ne 0 ]; then > ~ ... > ~ fi > > | echo "RSYNC begins.." > | > | # rsync each of the folders to the backup folder. > | for i in $TS1 $TS2 $PGDATADIR ; > | do > | echo "Syncing $i .. " > | time $RSYNC $i $BACKUPFOLDER > | echo "Done" > | done > > (optimization) If you store locations into an array, you could rewrite > this as follows: > > ~ CTR=3D0 > ~ while [ -n "${TS[${CTR}]}" ]; do > ~ echo "Syncing ${TS[${CTR}]}..." > ~ time ${RSYNC} ${TS[${CTR}]} ${BACKUPFOLDER} > ~ RVAL=3D$? > ~ echo "Sync finished with exit status ${RVAL}" > ~ if [ ${RVAL} -ne 0 ]; then > ~ > ~ fi > ~ CTR=3D$((CTR + 1)) > ~ done > ~ unset CTR > > | # fortunately rsync does *not* seems to be exitting with non zero exit > code > | # for expected file disappearances and modifications. > | if [ $? -ne 0 ] > | then > | echo "RSYNC failed" > | exit 1; > | fi > > (error) Same error as above - what you're checking here is whether the > last command in the last for loop run was successful, and this is always > going to be true as echoing to stdout will never fail until stdout is > closed for some reason. Yes it was an error, i am doing repeat runs of the script to find the non zero exit codes which should be treated as normal in context of taking base backups. > > | echo "RSYNC Done successfully" > | > | echo "Executing pg_stop_backup in server ... " > | $PSQL -Upostgres template1 -c "SELECT pg_stop_backup();" > | if [ $? -ne 0 ] > | then > | echo "PSQL pg_stop_backup failed" > | exit 1; > | fi > | echo "pg_stop_backup done successfully I think i should improve the style here. > | TO_SEARCH=3D"00${CP:4}" # $TO_SEARCH contains 1D1214 > | > | # now remove the unneeded files. > | > | # strip off first 4 chars from CP and prefix 00 to the result. > | # search the file that has the Checkpoint in its filename. > | # it takes a while to come, so wait till it comes. > | > | while true; do > | REF_FILE=3D`ls -1 $WAL_ARCHIVE | grep $TO_SEARCH` > | if [ ! $REF_FILE ]; then > | echo "Waitng for file with $TO_SEARCH in $WAL_ARCHIVE > ... " > | else > | break > | fi > | sleep 1 > | done > > (optimization) You could simplify this significantly using the test built= in: > > ~ while [ ! -e ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2 ]; do > ~ echo "Waiting for ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2" > ~ sleep 1 > ~ done > ~ REF_FILE=3D"`echo ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2`" I would not like to incorporate this becuase this code assumes WAL archives are being externally compressed to .bz2. Can you suggest an optimized but generic alternative ? > > | # REF_FILE is 000000010000000A00000068.001D1214.backup.bz2 > | > | # take only first 24 chars and store. > | REF_FILE_NUM=3D${REF_FILE:0:24} > | > | # REF_FILE_NUM is 000000010000000A00000068 > | > | echo "REF_FILE_NUM=3D$REF_FILE_NUM" > | > | # iterate list of files in the WAL_ARCHIVE folder > | for i in `ls -1 $WAL_ARCHIVE` ; > | do > | # $i is :000000010000000A0000005D.bz2 eg > | # get first 24 chars in filename > | FILE_NUM=3D${i:0:24} > | > | # compare if the number is less than the reference > | # here string comparison is being used. > | if [[ $FILE_NUM < $REF_FILE_NUM ]] > | then > | echo "$FILE_NUM [ $i ] removed" > | rm -f $WAL_ARCHIVE/$i > | else > | echo "$FILE_NUM [ $i ] not removed" > | fi > | done > > (optimization) Perhaps using find -newer/-anewer/-cnewer could be used > here to find files older than the reference file: > > ~ # "-not -newer" or "\! -newer" will also return REF_FILE > ~ # so you have to grep it out and use xargs; otherwise you > ~ # could also use the -delete action > ~ find ${WAL_ARCHIVE} \! -newer ${REF_FILE} -type f | \ > ~ grep -v "^${REF_FILE}$" | \ > ~ xargs rm -f Nopes , i have gone by the DOCS which tells to numerically compare the file= names i do not want to assume more recently created files are numerically more th= at later as i have not seen anything like that in the docs. I am concerned if the comparison below used in the script > | if [[ $FILE_NUM < $REF_FILE_NUM ]] is correct , as it compares strings not numbers , i am assuming that the results will be same as numerical comparison, as all the filenames are padded with '0' form the left. i thank you once again for your comments and shall post the improved versio= n once my observations are complete. Regds Rajesh Kumar Mallah.