From: Nestor A. Diaz <nestor@tiendalinux.com>
To: pgsql-admin@postgresql.org
Subject: Help with PITR in PostgreSQL 8.4
Date: Mon, 15 Jul 2013 17:06:20 -0500
Message-ID: <51E4725C.3050609@tiendalinux.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: nestor@tiendalinux.com
Subject: Re: Help with PITR in PostgreSQL 8.4
In-Reply-To: <51E4725C.3050609@tiendalinux.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