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 40A6A11F6646 for ; Wed, 29 Mar 2006 08:46:02 -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 19949-03 for ; Wed, 29 Mar 2006 08:46:01 -0400 (AST) 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 6200911F6641 for ; Wed, 29 Mar 2006 08:45:59 -0400 (AST) Received: by nproxy.gmail.com with SMTP id g2so124413nfe for ; Wed, 29 Mar 2006 04:45:59 -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=PSxPQgD1gL8M/eDtxXWoWuJwUj3fsF1sFvWEq01tvFESBnUJOQuWHPSp50cebPanrSGw5G7r68wEwdtTMLzZKf252p9qtxvs3mV69VVToFbH5BrpYSNGqPW/U9fYS84ImAJgFOenoB+PZLf7tc+ciDUhQUeQhZz1Jdwxfea+T+w= Received: by 10.48.237.10 with SMTP id k10mr339443nfh; Wed, 29 Mar 2006 04:45:59 -0800 (PST) Received: by 10.49.26.20 with HTTP; Wed, 29 Mar 2006 04:45:59 -0800 (PST) Message-ID: Date: Wed, 29 Mar 2006 18:15:59 +0530 From: "Rajesh Kumar Mallah" To: andy.shellam@mailnetwork.co.uk Subject: Re: FW: Setting up of PITR system. Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.348 required=5 tests=[AWL=0.016, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 1.348 X-Spam-Level: * X-Archive-Number: 200603/338 X-Sequence-Number: 21095 On 3/28/06, Andy Shellam wrote: > > > > 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 =96 however the backup file has the check= point > appended to it =96 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 =96 i.e. 00009012514000916 i= n > this case) and any numerically later than the .backup file. > > > > Also you wouldn't particular need any heavy programming =96 I'm sure a si= mple > shell script could be written in bash to pick out the correct files. 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 t= hink) 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. ------------------------------------ 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 # 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" 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 # 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 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" 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 # 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 ------------------------------------ END ----------------------------------------------- ---------------------------------- REAL OUTPUT OF A RUN--------------------= --- Executing pg_start_backup with label base_backup_29-03-2006-17-29-01 in server ... Begin CheckPoint is A/681D1214 pg_start_backup executed successfully RSYNC begins.. Syncing /mnt/disk4/bigtables .. real 6m24.338s user 1m12.831s sys 0m55.295s Done Syncing /mnt/disk3/indexspace .. real 5m45.245s user 0m32.520s sys 0m26.567s Done Syncing /mnt/disk5/pgdatadir .. readlink pgdatadir/global/pgstat.tmp: No such file or directory rsync error: some files could not be transferred (code 23) at main.c(620) real 12m1.844s user 1m43.698s sys 1m24.486s Done RSYNC Done successfully Executing pg_stop_backup in server ... pg_stop_backup ---------------- A/6FA82D74 (1 row) pg_stop_backup done successfully Waitng for file with 001D1214 in /mnt/wal_archive ... REF_FILE_NUM=3D000000010000000A00000068 000000010000000A0000005D [ 000000010000000A0000005D.003C0B54.backup.bz2 ] removed 000000010000000A0000005D [ 000000010000000A0000005D.bz2 ] removed 000000010000000A0000005E [ 000000010000000A0000005E.bz2 ] removed 000000010000000A0000005F [ 000000010000000A0000005F.bz2 ] removed 000000010000000A00000060 [ 000000010000000A00000060.bz2 ] removed 000000010000000A00000061 [ 000000010000000A00000061.bz2 ] removed 000000010000000A00000062 [ 000000010000000A00000062.bz2 ] removed 000000010000000A00000063 [ 000000010000000A00000063.bz2 ] removed 000000010000000A00000064 [ 000000010000000A00000064.bz2 ] removed 000000010000000A00000065 [ 000000010000000A00000065.bz2 ] removed 000000010000000A00000066 [ 000000010000000A00000066.bz2 ] removed 000000010000000A00000067 [ 000000010000000A00000067.bz2 ] removed 000000010000000A00000068 [ 000000010000000A00000068.001D1214.backup.bz2 ] not removed 000000010000000A00000068 [ 000000010000000A00000068.bz2 ] not removed 000000010000000A00000069 [ 000000010000000A00000069.bz2 ] not removed 000000010000000A0000006A [ 000000010000000A0000006A.bz2 ] not removed 000000010000000A0000006B [ 000000010000000A0000006B.bz2 ] not removed 000000010000000A0000006C [ 000000010000000A0000006C.bz2 ] not removed 000000010000000A0000006D [ 000000010000000A0000006D.bz2 ] not removed 000000010000000A0000006E [ 000000010000000A0000006E.bz2 ] not removed ------------------------------- END----------------------------------------------------- > > > 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 retu= rns > something > which does not looks like a WAL file. > > > > Pg_stop_backup() returns the checkpoint record =96 something like 9/A0AC9= 1 =96 > 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 =96 in my example it's 00009012514000916.A0AC91.backup, I think its not correct , pg_start_backup() returns the checkpoint record which becomes part of the backup filename. and grep > the file for the number after the text "Start WAL Location: " in this fil= e =96 > then remove anything numerically less. > > > > It'd be a very "bitty" process, but I'm certain it could be done =96 it w= ould > need heavy testing over a period of backups though to ensure the wrong fi= les > 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 c= p > -a ? > > > > The WAL archive command can be set to use either cp or mv =96 then why no= t > have a regular cronjob that runs a shell script to add any new files to y= our > tar archive every time a new file is detected in your WAL-archive directo= ry? Hmmm you got me wrong, am talking about taking the base_backup not archival of WAL segments > > > > Regds > Rajesh Kumar Mallah. > > !DSPAM:14,44293ed135042000516834! > > > Regards > > > > Andy Shellam