pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brendan Duddridge <brendan@clickspace.com>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: Grega Bremec <gregab@p0f.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Setting up of PITR system.
Date: Sun, 2 Apr 2006 22:15:44 -0600
Message-ID: <747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com> (raw)
In-Reply-To: <a97c77030604020914n38b3e3bco2138b9673f25b176@mail.gmail.com>
References: <!&!AAAAAAAAAAAuAAAAAAAAALfqleqaijxJlxu+E5RYF+YBAJaQ0jfg6zBFp7poaER6UCkAAAGy3PcAABAAAAB0heSWPqq+QLrrk5Lik5sLAQAAAAA=@mailnetwork.co.uk>
	<a97c77030603290445o29f96099xd3cc5f15cd4fb364@mail.gmail.com>
	<442B93DD.4020305@p0f.net>
	<a97c77030603312023k7bdc705eif22af429dc3c943d@mail.gmail.com>
	<442E1945.4030006@p0f.net>
	<a97c77030604010258j657622fcj6a42a10c8c38f344@mail.gmail.com>
	<442FDC7F.9010001@p0f.net>
	<a97c77030604020914n38b3e3bco2138b9673f25b176@mail.gmail.com>

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.
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.

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.

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.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@p0f.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
>

Attachments:

  smime.p7s (application/pkcs7-signature, 2.3K) download

view thread (36+ messages)  latest in thread

Message-ID: <747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com>
Permalink:  ../747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com/
Also on:    postgresql.org/message-id/747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: brendan@clickspace.com, mallah.rajesh@gmail.com, gregab@p0f.net
  Subject: Re: Setting up of PITR system.
  In-Reply-To: <747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com>
  References: <!&!AAAAAAAAAAAuAAAAAAAAALfqleqaijxJlxu+E5RYF+YBAJaQ0jfg6zBFp7poaER6UCkAAAGy3PcAABAAAAB0heSWPqq+QLrrk5Lik5sLAQAAAAA=@mailnetwork.co.uk> <a97c77030603290445o29f96099xd3cc5f15cd4fb364@mail.gmail.com> <442B93DD.4020305@p0f.net> <a97c77030603312023k7bdc705eif22af429dc3c943d@mail.gmail.com> <442E1945.4030006@p0f.net> <a97c77030604010258j657622fcj6a42a10c8c38f344@mail.gmail.com> <442FDC7F.9010001@p0f.net> <a97c77030604020914n38b3e3bco2138b9673f25b176@mail.gmail.com> <747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox