Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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:
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:
select pg_start_backup('Full Backup - Master');
tar -cvzf /var/lib/pgsql/data/potgresMASTER.tar /var/lib/pgsql/data/
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

Grant Instruments (Cambridge) Ltd

Company registered in England, registration number 658133

Registered office address:
29 Station Road,

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

In response to


pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group