PITR online backups Setup

From: Renato Oliveira <renato(dot)oliveira(at)grant(dot)co(dot)uk>
To: Postgres-Admin <pgsql-admin(at)postgresql(dot)org>
Subject: PITR online backups Setup
Date: 2010-01-14 14:33:52
Message-ID: 7965A9DCF12CC14984420BCC37B1608F2584EA3881@Elzar.grant.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Dear all,

I am trying to setup PITR for online backup and also to create a more robust setup, in case our primary Postgres dies.

I am testing this setup on Centos 5.4, Postgres version: 8.1.18 (not sure why Centos has this old version by default).

I have enabled PITR, with the following commands:
/var/lib/pgsql/data/postgresql.conf
archive_command = on
archive_command = 'cp -i %p /data/pgsql/archives/%f </dev/null'

I have created the folder: /data/pgsql/archives and changed ownership to postgres:postgres

I can see the archiving seems to be working:
Ls -ls /data/pgsql/archives
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 000000010000000000000000
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 000000010000000000000001
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 000000010000000000000002
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 000000010000000000000003
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 000000010000000000000004
4 -rw------- 1 postgres postgres 247 Jan 13 12:41 000000010000000000000004.00C18E68.backup
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 000000010000000000000005
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 000000010000000000000006
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 000000010000000000000007
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 000000010000000000000008

I have done the base backup:
Psql
select pg_start_backup('Full Backup - Master');
tar -cvzf /var/lib/pgsql/data/potgresMASTER.tar /var/lib/pgsql/data/
psql
select pg_stop_backup();

Now I am trying to setup the secondary server, this is where I am stuck.

1 - I tried to setup rsync to ship these logs across to the remote server, but I can't get postgres to work with authorized_keys
How you guys are doing this? NFS will not be an option.

WARM server setup
On the Standby Server
I have restored the base backup
Tar -zxvf potgresMASTER.tar under /var/lib/pgsql/data

I have heard of pg_standby but apparently I have to compile it against postgres/source, not sure how this would help me, if anyone could help me explaining it to me, it would be really helpful.

Then I need to create a /var/lib/pgsql/data/recovery.conf and add similar lines
restore_command = 'cp /data/pgsql/archives/%f %p'

Do I need to turn this "ON" somewhere, because there seems to be an inconsistency on information around:
Some people says I have to turn archive_mode = on, some says I have to use archive_command = on

Do I need to do similar for recovery.conf?

Once I have rsync shipping logs to the remote server, recovery.conf configured, is that all I need to do to consider it complete and working?
Are there any other aspect that I need to consider?

I would be very thankful too all of you for any helps.

Thank you very much for all your repplies

Best regards

Renato Oliveira

e-mail: renato(dot)oliveira(at)grant(dot)co(dot)uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://www.grant.co.uk/

Grant Instruments (Cambridge) Ltd

Company registered in England, registration number 658133

Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK

P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our http://www.grant.co.uk/Support/openxml.html

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Joshua D. Drake 2010-01-14 16:09:11 Re: PITR online backups Setup
Previous Message Dimitri Fontaine 2010-01-14 08:57:00 Re: Question on moving data to new partitions