Re: Setting up of PITR system.

On 4/3/06, Brendan Duddridge <brendan(at)clickspace(dot)com> wrote:
> Whenever you get a finalized script including all the enhancements
> you've been talking about,
> I would really love to have a copy as I'm sure lots of people here
> would. I think this is
> something that should be included in the standard distribution.
>
> Can I make a couple of suggestions?
>
> 1. Include a mail option to send the admin an email when the backup
> succeeds or fails.

Considering the fact that the script is hugely unix oriented currently,
such a facility is anyway avialble in crontab using MAILTO variable.

> 2. Call pg_stop_backup() if the script fails for whatever reason. You
> can't run the script
> twice unless you stop the backup. I ran the script a few times to
> see if I can get a
> PITR backup system going and due to directory permissions and
> such and during basic
> testing, I had to manually call pg_stop_backup() after the script
> exits abnormally.

yes this is a problem , i do not know if calling pg_stop_backup() is the
end to the damage control steps, but i shall surely add it for the time
being.

>
> Thanks very much for building this script and sharing it with us.
> Many of us don't have the skills to
> write such a script. I'm a Java programmer, but I have little bash
> scripting skills, so this
> is very much appreciated.

Thanks to prying eyes of the bash gurus who are supervising it :)
i only have the machines and situation to run it and seek
suggestions.

Regds
Rajesh Kumar Mallah.

>
> ____________________________________________________________________
> Brendan Duddridge | CTO | 403-277-5591 x24 | brendan(at)clickspace(dot)com
>
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB T2G 0V9
>
> http://www.clickspace.com
>
> On Apr 2, 2006, at 10:14 AM, Rajesh Kumar Mallah wrote:
>
> > On 4/2/06, Grega Bremec <gregab(at)p0f(dot)net> 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=$DATADIR/backup_label
> >> | # get the like containing line START WAL LOCATION
> >> |
> >> | START_LINE=`grep -i "START WAL LOCATION" $BACKUP_LABEL`
> >> | # strip something like 'START WAL LOCATION: E/A9145E4 (file '
> >> from begin.
> >> | START_LINE=${START_LINE/#START*file /}
> >> | # strip ')' from end.
> >> | START_LINE=${START_LINE/%)/}
> >> | # REF_FILE_NUM is something like 000000010000000A00000068
> >> | REF_FILE_NUM=$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=
> >> =7MXQ
> >> -----END PGP SIGNATURE-----
> >>
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
>
>