Re: FW: Setting up of PITR system. - Mailing list pgsql-admin
From | Grega Bremec |
---|---|
Subject | Re: FW: Setting up of PITR system. |
Date | |
Msg-id | 442B93DD.4020305@p0f.net Whole thread Raw |
In response to | Re: FW: Setting up of PITR system. ("Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>) |
Responses |
Re: FW: Setting up of PITR system.
|
List | pgsql-admin |
-----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. | ------------------------------------ BEGIN | -------------------------------------------- | #!/bin/bash | | # folder where base_backup is put | BACKUPFOLDER=/mnt/disk3/base_backups | today=`date +%d-%m-%Y-%H-%M-%S` | PSQL=/opt/usr/local/pgsql/bin/psql | RSYNC="/usr/bin/rsync -a" | PGDATADIR=/mnt/disk5/pgdatadir | | # two table spaces. | | TS1=/mnt/disk4/bigtables | TS2=/mnt/disk3/indexspace (optimization) Since you're using bash, you can use arrays. This could be better written as ~ TS[0]=/mnt/disk5/pgdatadir ~ TS[1]=/mnt/disk4/bigtables ~ TS[2]=/mnt/disk3/indexspace or even ~ TS=(/mnt/disk5/pgdatadir \ ~ /mnt/disk4/bigtables \ ~ /mnt/disk3/indexspace) 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=/mnt/wal_archive | | label=base_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=`$PSQL -q -Upostgres -d template1 -c "SELECT | pg_start_backup('$label');" -P tuples_only -P format=unaligned` | | 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. (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="`$PSQL ... 2>&1`" ~ RVAL=$? ~ 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=0 ~ while [ -n "${TS[${CTR}]}" ]; do ~ echo "Syncing ${TS[${CTR}]}..." ~ time ${RSYNC} ${TS[${CTR}]} ${BACKUPFOLDER} ~ RVAL=$? ~ echo "Sync finished with exit status ${RVAL}" ~ if [ ${RVAL} -ne 0 ]; then ~ <handle errors> ~ fi ~ CTR=$((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. | 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="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=`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 builtin: ~ 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="`echo ${WAL_ARCHIVE}/*.00${TO_SEARCH}.backup.bz2`" | # REF_FILE is 000000010000000A00000068.001D1214.backup.bz2 | | # take only first 24 chars and store. | REF_FILE_NUM=${REF_FILE:0:24} | | # REF_FILE_NUM is 000000010000000A00000068 | | echo "REF_FILE_NUM=$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=${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 |>The WAL archive command can be set to use either cp or mv ? then why not |>have a regular cronjob that runs a shell script to add any new files to your |>tar archive every time a new file is detected in your WAL-archive directory? | | Hmmm you got me wrong, am talking about taking the base_backup not | archival of WAL segments Just a thought - it might be worth trying star (Schilly's tar); it won't bail out completely if a file changes during read, and I'm 100% sure that when it only changes in size, it is going to be stored nevertheless. It also has functionality for storing ACLs and xattrs in the tar header, works on numerous UNIX flavours and comes as a system package with most of the popular distributions. Hope this helped, - -- ~ Grega Bremec ~ gregab at p0f dot net -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFEK5Pdfu4IwuB3+XoRA7KVAJ9wbK6RDBnA4s0cjbdR3FE6+oUvGwCfWeAO SecCgFnus70LKNYCDNeV3Z0= =oUrb -----END PGP SIGNATURE-----
pgsql-admin by date: