Re: PITR online backups Setup

Julio,

Thank you for your reply, I was wondering if anyone has encountered problems setting up PITR with 8.2.4?
Unfortunately our live system runs 8.2.4 and it is quite tricky to upgrade it right now.

By the way where can I find a how to use pitr-tools?

Thank you very much

Best regards

Renato

Renato Oliveira

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

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk<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

From: Julio Leyva [mailto:jcleyva(at)hotmail(dot)com]
Sent: 14 January 2010 18:47
To: Renato Oliveira
Subject: RE: [ADMIN] PITR online backups Setup

You better update to postgresql 8.3
PITR works very well with this version

I remember trying to setup it with 8.1 and It did not work.

> From: renato(dot)oliveira(at)grant(dot)co(dot)uk
> To: pgsql-admin(at)postgresql(dot)org
> Date: Thu, 14 Jan 2010 14:33:52 +0000
> Subject: [ADMIN] PITR online backups Setup
>
> 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
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

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 website<http://www.grant.co.uk/Support/openxml.html>