Help with PITR in PostgreSQL 8.4 - Mailing list pgsql-admin

Hello People,

I need some clarification on Continuous Archiving and Point-In-Time
Recovery (PITR).

I have been able to replicate a postgresql 8.4 database from one machine
to another using rsync and wal files.

I do this as follows (Using debian gnu/linux squeeze):

In *master* i have the following options under postgresql.conf:

archive_mode = on               # allows archiving to be done
archive_command = 'test ! -f
/var/lib/postgresql/8.4/main/backup_in_progress || cp -i %p
/var/lib/postgresql/8.4/main/wal-archive/%f'   # command to use to
archive a logfile segment

I start the backup on *master*:

touch /var/lib/postgresql/8.4/main/backup_in_progress
DATE=$(date +%Y%m%d-%H%M%S)
psql  --cluster 8.4/main -c "SELECT pg_start_backup('backup_${DATE}');"

Then rsync on *standby*.

    rsync -avz --partial --progress --delete --rsh=ssh \
        --exclude 'lost+found' --exclude 'postmaster.pid' --exclude
'postmaster.opts' --exclude 'wal-archive' \
        postgres@$SRC_HOST:/var/lib/postgresql/8.4/main/
/var/lib/postgresql/8.4/main/

Plus all the tablespaces which are located on different directories and
rsynced with same options as above (except the exclude)

Then stop backup on *main*.

psql  --cluster 8.4/main -c "SELECT pg_stop_backup();"

And transfer all the WAL files from *master* to *standby*:

rsync -avz --partial --progress --rsh=ssh \
     --exclude 'lost+found' \
     postgres@$SRC_HOST:/var/lib/postgresql/8.4/main/wal-archive/
/var/lib/postgresql/8.4/main/wal-archive/

In *standby* i have in recovery.conf:

restore_command = '/usr/lib/postgresql/8.4/bin/pg_standby -l -d -s 2 -t
/var/run/postgresql/8.4-main.trigger
/var/lib/postgresql/8.4/main/wal-archive %f %p %r
2>>/var/log/postgresql/postgresql-8.4-main-standby.log'
recovery_end_command = 'rm -f /var/run/postgresql/8.4-main.trigger'

Then start the database on *standby* and everything worked fine, I
replicated the main database from one machine to another and later on
the *standby* machine I process the logical backups which off course
takes a lot of time.

But there is one big issue:

The database is about: 300 Gb of size, so rsyncing the data from one
machine to another takes more than 2 hours, I don't know if I can change
some rsync options in order to improve since this is too much time.

Another thing is to improve the script and not having to rsync the
database and just transferring the wal files and apply them every day,
this is what I am trying to do without luck.

So the question is: Can I have some continues wal archiving and then
transfer all WAL files to *standby* everyday, recover the database and
backup all data without having to rsync the data every time i make a
backup ?

For now i have to stick with 8.4 .

Thank you very much for your time and support.

Slds.

--
Typed on my key64.org keyboard

Nestor A Diaz