Re: [ADMIN] Setting up of PITR system.

[Brendan Duddridge]

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?




is very much appreciated.

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  [EMAIL PROTECTED]

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:

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

On 4/2/06, Grega Bremec <[EMAIL PROTECTED]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Rajesh Kumar Mallah wrote:
|

| file, (This was also suggested by Andy at somepoint)



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


since there seems to be a lot of people interested in a working,

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`

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

| Instead of taking a round about method i am using the content of the After reading the docs again, that's what I would do as well, indeed. :) I've been considering that seriously in the very first post I wrote, but flexible WAL archiving script, I decided to keep it on the list. It is | # strip something like 'START WAL LOCATION: E/A9145E4 (file ' from begin.

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


can you suggest the sane/recommended way to get the destination

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.

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 folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf "%???????" )

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

TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------

smime.p7s
Description: S/MIME cryptographic signature