X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DCA0A9FA3CD for ; Mon, 1 May 2006 21:48:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 05497-06 for ; Mon, 1 May 2006 21:48:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 5E34B9FA2BE for ; Mon, 1 May 2006 21:48:08 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id d4so2003063nfe for ; Mon, 01 May 2006 17:48:05 -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=DmQgNLq2wGrnvEPzinjWk2Ze3tppsahI9zmZek/Fc9sSUR41MELnWmpLEbFhAwaxWDLMAqrU1O9H/NhHfnI+NjRtX1yeqJdG8RCB9yOgA1iyrzPo8GGWzdNitmOfkv2lNH3xRPT6yvNTURyg+mSTrOiM0U0xeW7ZRGU+SWHUq44= Received: by 10.49.15.11 with SMTP id s11mr82908nfi; Mon, 01 May 2006 17:48:05 -0700 (PDT) Received: by 10.49.27.1 with HTTP; Mon, 1 May 2006 17:48:05 -0700 (PDT) Message-ID: Date: Tue, 2 May 2006 06:18:05 +0530 From: "Rajesh Kumar Mallah" To: "Grega Bremec" Subject: Re: FW: Setting up of PITR system. Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15066_18893404.1146530885213" References: <442FDC7F.9010001@p0f.net> <4430BC59.7040006@p0f.net> <4437AF15.3020707@p0f.net> <443E053D.4080506@p0f.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/9 X-Sequence-Number: 21541 ------=_Part_15066_18893404.1146530885213 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi List, please find below a 1. A script that takes remote base backups of a postgresql database. it assumes a password less communication between the backup server and the database server. After taking a base backup it removes that un neccesary WAL log files from the archive folder. The script has bee= n running in my server for past few weeks without any problems. It takes apprx 40 mins to backup around 40 GB data. sample execution on the backup server $ ./pg_rsync.sh 216.247.238.130 /mnt/disk2/base_backups /mnt/disk2/wal_archive or in crontab as 30 20 * * * /usr/bin/time /home/postgres/pg_rsync.sh 216.247.238.130/mnt/disk2/base_backups /mnt/disk2/wal_archive Note: script also assumes that you have setup remote wal archiving using a suitable archive_command in postgresql.conf , these scripts are *not* posted here. any suggestions welcome. ------------------------ script begins (pg_rsync.sh)------------------------------------------------ #!/bin/bash ################################################## # it does following # 1. checks existance and permission of imp folders. # 2. takes base backup to a destined folder by rsync # 3. removes unwanted archived log files. # Contributions: Grega Bremec (gregab@p0f.net) , # Alvaro Herrera (alvherre@commandprompt.com) # Preliminary Version: Mallah (mallah.rajesh@gmail.com) ################################################## if [ $# -ne 3 ] then echo "Usage: $0 " exit 1 fi HOSTNAME=3D$1 BACKUPFOLDER=3D$2 WAL_ARCHIVE=3D$3 echo "HOSTNAME=3D$HOSTNAME" echo "BACKUPFOLDER=3D$BACKUPFOLDER" echo "WAL_ARCHIVE=3D$WAL_ARCHIVE" if [ -z $BACKUPFOLDER ] || [ ! -d $BACKUPFOLDER ] || [ ! -w $BACKUPFOLDER ] then echo "Sorry base backup folder $BACKUPFOLDER does not exists or is not writable or is not specified!" exit 1 fi if [ -z $WAL_ARCHIVE ] || [ ! -d $WAL_ARCHIVE ] || [ ! -w $WAL_ARCHIVE ] then echo "Sorry WAL archive folder $WAL_ARCHIVE does not exists or is not writable or is not specified!" exit 1 fi PSQL_BIN=3D`which psql` || /usr/local/pgsql/bin/psql RSYNC_BIN=3D`which rsync` || /usr/bin/rsync SSH_BIN=3D`which ssh` || /usr/bin/ssh for PROG in $PSQL_BIN $RSYNC_BIN $SSH_BIN ; do if [ ! -f $PROG ] || [ ! -x $PROG ] then echo "Sorry $PROG does not exists or is not executable by you" echo "Please set env variable PATH to include psql and rsync" exit 1 else echo "Using $PROG" fi done RSYNC_OPTS=3D"--delete-after -a -e $SSH_BIN --exclude pg_xlog" RSYNC=3D"$RSYNC_BIN $RSYNC_OPTS" PSQL=3D$PSQL_BIN today=3D`date +%d-%m-%Y-%H-%M-%S` label=3Dbase_backup_${today} echo "Querying data_directory and tablespace folders from server $HOSTNAME" DATA_DIR=3D`$PSQL -q -Upostgres -d template1 -h $HOSTNAME -c "show data_directory;" -P tuples_only -P format=3Dunaligned 2>&1` RVAL=3D$? if [ $RVAL -ne 0 ] then echo "Some error in getting data_directory:$DATA_DIR" exit 1; fi echo "DATA_DIR:$DATA_DIR" TBL_SPCS=3D(`$PSQL -q -Upostgres -d template1 -h $HOSTNAME -c "SELECT spclocation from pg_catalog.pg_tablespace where length(spclocation)>0;" -P tuples_only -P format=3Dunaligned`) RVAL=3D$? if [ $RVAL -ne 0 ] then echo "There is some problem in getting table spaces" exit 1; fi DIRS=3D( "${TBL_SPCS[@]}" $DATA_DIR) echo "Folders for Backup:" CTR=3D0 while [ -n "${DIRS[${CTR}]}" ]; do echo -n "${DIRS[${CTR}]} --> " DIRS[${CTR}]=3D`ssh $HOSTNAME readlink -f ${DIRS[${CTR}]}` echo "${DIRS[${CTR}]} (after symlink resolution)" CTR=3D$((CTR + 1)) done unset CTR CP=3D`$PSQL -q -Upostgres -d template1 -h$HOSTNAME -c "SELECT pg_start_backup('$label');" -P tuples_only -P format=3Dunaligned 2>&1` RVAL=3D$? if [ $RVAL -ne 0 ] then echo "PSQL pg_start_backup failed:$CP" exit 1; fi echo "pg_start_backup executed successfully: $CP" echo "RSYNC begins.." # rsync each of the folders to the backup folder. CTR=3D0 while [ -n "${DIRS[${CTR}]}" ]; do echo "Syncing ${DIRS[${CTR}]}..." time ${RSYNC} $HOSTNAME:${DIRS[${CTR}]} ${BACKUPFOLDER} RVAL=3D$? echo "Sync finished with exit status ${RVAL}" if [[ ${RVAL} -eq 0 || ${RVAL} -eq 23 ]]; then echo "Rsync success" else echo "Rsync failed" $PSQL -Upostgres -h$HOSTNAME template1 -c "SELECT pg_stop_backup();= " exit 1 fi CTR=3D$((CTR + 1)) done unset CTR echo "Executing pg_stop_backup in server ... " $PSQL -Upostgres -h$HOSTNAME 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" # read the backup_label file in pgdatadir and get the name of start wal fil= e # below is example content. #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 DATA_DIR_NAME=3D`basename $DATA_DIR` BACKUP_LABEL=3D$BACKUPFOLDER/$DATA_DIR_NAME/backup_label echo "BACKUP_LABEL: $BACKUP_LABEL" START_LINE=3D`grep -i "START WAL LOCATION" $BACKUP_LABEL` # get the like containing START WAL LOCATION START_LINE=3D${START_LINE/#START*file /} # strip something like 'START WAL LOCATION: E/A9145E4 (file ' from begin. START_LINE=3D${START_LINE/%)/} # strip ')' from end. # REF_FILE_NUM is something like 000000010000000A00000068 REF_FILE_NUM=3D$START_LINE echo "Content of $BACKUP_LABEL" echo "------------- begin -----------" cat $BACKUP_LABEL echo "------------- end -----------" echo "Read Start Wal as : $REF_FILE_NUM" 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 #------------------------- script ends ----------------------------------------------------------------- ------=_Part_15066_18893404.1146530885213 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi List,

please find below a

1. A script that takes remote base backups of a postgresql database.
it assumes a password less communication between the backup
server and the database server. After taking a base backup it removes
that un neccesary WAL log files from the archive folder. The script has bee= n
running in my server for past few weeks without any problems. It takes
apprx 40 mins to backup around 40 GB data.

sample execution on the backup server
$ ./pg_rsync.sh 216.247.238.130 /mnt= /disk2/base_backups  /mnt/disk2/wal_archive
or in crontab as
30 20 * * * /usr/bin/time /home/postgres/pg_rsync.sh 216.247.238.130 /mnt/disk2/base_backups  /mnt/disk2/w= al_archive

Note: script also assumes that you have setup remote wal archiving using a = suitable
archive_command in postgresql.conf , these scripts are *not* posted here.

any suggestions welcome.


------------------------ script begins (pg_rsync.sh)-----------------------= -------------------------
#!/bin/bash

##################################################
# it does following
# 1. checks existance and permission of imp folders.
# 2. takes base backup to a destined folder by rsync
# 3. removes unwanted archived log files.
# Contributions: Grega Bremec (gregab@p0f= .net) ,
#            &n= bsp;           Alvaro Herrera (alvherre@comm= andprompt.com)
# Preliminary Version: Mallah (m= allah.rajesh@gmail.com)
##################################################

if [ $# -ne 3 ]
  then
        echo "Usage: $0 <HOSTNAME> <BACKUP DIRECTORY> <WAL ARCHIVE DIRECTORY>&quo= t;
        exit 1
fi
HOSTNAME=3D$1
BACKUPFOLDER=3D$2
WAL_ARCHIVE=3D$3



echo "HOSTNAME=3D$HOSTNAME"
echo "BACKUPFOLDER=3D$BACKUPFOLDER"
echo "WAL_ARCHIVE=3D$WAL_ARCHIVE"

if [ -z $BACKUPFOLDER ] || [ ! -d $BACKUPFOLDER ] || [ ! -w $BACKUPFOLDER ]=
   then
        echo "Sorry base backup folder $BACKUPFOLDER does not exists or is not writable or is not specified!"
        exit 1
fi
if [ -z $WAL_ARCHIVE ] || [ ! -d $WAL_ARCHIVE ] || [ ! -w $WAL_ARCHIVE ]    then
        echo "Sorry WAL archive folder $WAL_ARCHIVE does not exists or is not writable or is not specified!"
        exit 1
fi

PSQL_BIN=3D`which psql` || /usr/local/pgsql/bin/psql
RSYNC_BIN=3D`which rsync` || /usr/bin/rsync
SSH_BIN=3D`which ssh` || /usr/bin/ssh

for PROG in $PSQL_BIN $RSYNC_BIN $SSH_BIN ; do
if [ ! -f $PROG ] || [ ! -x $PROG ]
   then
        echo "Sorry $PROG does not = exists or is not executable by you"
        echo "Please set env variab= le PATH to include psql and rsync"
        exit 1
   else
        echo "Using $PROG"
fi
done

RSYNC_OPTS=3D"--delete-after -a -e $SSH_BIN --exclude pg_xlog" RSYNC=3D"$RSYNC_BIN $RSYNC_OPTS"
PSQL=3D$PSQL_BIN

today=3D`date +%d-%m-%Y-%H-%M-%S`
label=3Dbase_backup_${today}

echo "Querying data_directory and tablespace folders from server $HOST= NAME"
DATA_DIR=3D`$PSQL -q -Upostgres -d template1 -h $HOSTNAME -c "show data_directory;" -P tuples_only -P format=3Dunaligned 2>&1`
RVAL=3D$?
if [ $RVAL -ne 0 ]
        then
        echo "Some error in getting= data_directory:$DATA_DIR"
        exit 1;
fi
echo "DATA_DIR:$DATA_DIR"

TBL_SPCS=3D(`$PSQL -q -Upostgres -d template1 -h $HOSTNAME -c "SELECT  spclocation from pg_catalog.pg_tablespace where length(spclocation)>0;" -P tuples_only -P format=3Dunaligned`)
RVAL=3D$?
if [ $RVAL -ne 0 ]
        then
        echo "There is some problem= in getting table spaces"
        exit 1;
fi
DIRS=3D( "${TBL_SPCS[@]}" $DATA_DIR)
echo "Folders for Backup:"
CTR=3D0
while [ -n "${DIRS[${CTR}]}" ]; do
    echo -n "${DIRS[${CTR}]} --> "
    DIRS[${CTR}]=3D`ssh $HOSTNAME readlink -f ${DIRS[${CTR}]= }`
    echo "${DIRS[${CTR}]} (after symlink resolution)&qu= ot;
    CTR=3D$((CTR + 1))
done
unset CTR




CP=3D`$PSQL -q -Upostgres -d template1 -h$HOSTNAME -c "SELECT pg_start_backup('$label');" -P tuples_only -P format=3Dunaligned 2>&1`

RVAL=3D$?
if [ $RVAL -ne 0 ]
        then
        echo "PSQL pg_start_backup = failed:$CP"
        exit 1;
fi
echo "pg_start_backup executed successfully: $CP"



echo "RSYNC begins.."

# rsync each of the folders to the backup folder.
CTR=3D0
while [ -n "${DIRS[${CTR}]}" ]; do
    echo "Syncing ${DIRS[${CTR}]}..."
    time ${RSYNC} $HOSTNAME:${DIRS[${CTR}]} ${BACKUPFOLDER}<= br>     RVAL=3D$?
    echo "Sync finished with exit status ${RVAL}"<= br>     if [[ ${RVAL} -eq 0 || ${RVAL} -eq 23  ]]; then
        echo "Rsync success"     else
        echo "Rsync failed"         $PSQL -Upostgres -h$HOSTNAME tem= plate1 -c "SELECT pg_stop_backup();"
        exit 1
    fi
    CTR=3D$((CTR + 1))
done
unset CTR


echo "Executing pg_stop_backup in server ... "
$PSQL -Upostgres -h$HOSTNAME template1 -c "SELECT pg_stop_backup();&qu= ot;
if [ $? -ne 0 ]
        then
        echo "PSQL pg_stop_backup f= ailed"
        exit 1;
fi
echo "pg_stop_backup done successfully"

# read the backup_label file in pgdatadir and get the name of start wal fil= e
# below is example content.
#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

DATA_DIR_NAME=3D`basename $DATA_DIR`
BACKUP_LABEL=3D$BACKUPFOLDER/$DATA_DIR_NAME/backup_label
echo "BACKUP_LABEL: $BACKUP_LABEL"

START_LINE=3D`grep -i  "START WAL LOCATION"  $BACKUP_LA= BEL` # get the like containing START WAL LOCATION
START_LINE=3D${START_LINE/#START*file /} # strip something like 'START WAL = LOCATION: E/A9145E4 (file ' from begin.
START_LINE=3D${START_LINE/%)/} # strip ')' from end.

# REF_FILE_NUM is something like 000000010000000A00000068
REF_FILE_NUM=3D$START_LINE

echo "Content of $BACKUP_LABEL"
echo "------------- begin -----------"
cat $BACKUP_LABEL
echo "------------- end -----------"
echo "Read Start Wal as : $REF_FILE_NUM"

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 :000000010000000A0000005= D.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 bein= g used.
        if [[ $FILE_NUM  < $REF_= FILE_NUM ]]
        then
            &nb= sp;   echo "$FILE_NUM [ $i ] removed"
            &nb= sp;   rm -f $WAL_ARCHIVE/$i
        else
            &nb= sp;   echo "$FILE_NUM [ $i ] not removed"
        fi
done
#------------------------- script ends ------------------------------------= -----------------------------

 
------=_Part_15066_18893404.1146530885213--