turn pitr 'on' on PostgreSQL 8.2

--_000_7965A9DCF12CC14984420BCC37B1608F25ABF310ABElzargrantc ou_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Dear all,

I have been playing with PITR on version 8.3 so far, but I need to get it w=
orking on version 8.2.

What is the difference between:

archive_command =3D on (is this command for 8.2?)
archive_mode =3D on (This one is for 8.3!)

I need to enable 'archive_mode =3D on', restart postgres, before I can do t=
he base backup, is that correct?

In order to bring the standby online I need a trigger file, something like:
can I call the trigger file whatever I want? for example; '/var/log/postgre=
s/postgresql.8.2.trigger'

Postgres 8.2 config

Master:
Postgresql.conf
archive_command =3D on
archive_command =3D 'cp -i %p /mnt/pitr/%f </dev/null'
archive_timeout =3D 60

# mkdir /mnt/pitr
# chown postgres: /mnt/pitr (this will be an NFS mount point)
# mount -t nfs fnsserver01:/pitr /mnt/pitr
# service postgresql restart
# su - postgres
# psql -U postgres
SELECT pg_start_backup('pitr_backup');
\q
# Tar -cvf pg_data.tar /usr/loca/pgsql/data
# Psql -U postgres
SELECT pg_stop_backup();
# cp pg_data.tar /mnt/pitr

Postgres 8.2 config

StandBy:
# Compile pg_standy from the contrib/ folder
# cd /usr/local/pgsql
# rm -rf data/
# tar -xvf /mnt/pitr/pg_data.tar
# chown -R postgres: data
# cd data/
# cd pg_xlog/ - delete the content
# cd ../data
# touch recovery.conf
restore_command =3D 'pg_standby -l -d -s 2 -t /var/log/postg=
res/ postgresql.8.2.trigger /mnt/pitr/ %f %p %r 2>>pitr_standby.log'

Postgresql.conf
#archive_command =3D off - disabled and commented out
#archive_command =3D '' - disabled and command removed
# archive_timeout =3D 60 - disabled

Is this correct, am I missing something or anything here isn't correct?

If anyone could give me some light with this it would be very much apprecia=
ted.

Thank you very much

Renato

Renato Oliveira
Systems Administrator
e-mail: renato.oliveira [at] grant.co.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

P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is conf=
idential. It is intended only for the named recipients(s). If you are not t=
he 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 w=
hich could damage your own computer system. Whilst Grant Instruments (Cambr=
idge) Ltd has taken every reasonable precaution to minimise this risk, we c=
annot accept liability for any damage which you sustain as a result of soft=
ware viruses. You should therefore carry out your own virus checks before o=
pening 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>

--_000_7965A9DCF12CC14984420BCC37B1608F25ABF310ABElzargrantc ou_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:a=3D"urn:schemas-micr=
osoft-com:office:access" xmlns:b=3D"urn:schemas-microsoft-com:office:publis=
her" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xml=
ns:D=3D"DAV:" xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/di r=
ectory/" xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" xmlns:dsp=3D"http:=
//schemas.microsoft.com/sharepoint/dsp" xmlns:dssi=3D"http://schemas.micros=
oft.com/office/2006/digsig" xmlns:dsss=3D"http://schemas.microsoft.com/offi=
ce/2006/digsig-setup" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882=
" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" xmlns:ex12m=3D"http://sche=
mas.microsoft.com/exchange/services/2006/messages" xmlns:ex12t=3D"http://sc=
hemas.microsoft.com/exchange/services/2006/types" xmlns:html=3D"http://www.=
w3.org/TR/REC-html40" xmlns:m=3D"http://schemas.microsoft.com/office/2004/1=
2/omml" xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/200 6/digit=
al-signature" xmlns:mrels=3D"http://schemas.openxmlformats.org/package/200 6=
/relationships" xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/me=
etings/" xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compa tibili=
ty/2006" xmlns:o=3D"urn:schemas-microsoft-com:office:office" xmlns:oa=3D"ur=
n:schemas-microsoft-com:office:activation" xmlns:odc=3D"urn:schemas-microso=
ft-com:office:odc" xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soa=
p/ois/" xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" xmlns:ppda=
=3D"http://www.passport.com/NameSpace.xsd" xmlns:pptsl=3D"http://schemas.mi=
crosoft.com/sharepoint/soap/SlideLibrary/" xmlns:q=3D"http://schemas.xmlsoa=
p.org/soap/envelope/" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" xml=
ns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:rtc=3D"http://microsoft.co=
m/officenet/conferencing" xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14=
882" xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"htt=
p://schemas.microsoft.com/sharepoint/soap/" xmlns:spsl=3D"http://microsoft.=
com/webservices/SharePointPortalServer/PublishedLinksService " xmlns:spwp=3D=
"http://microsoft.com/sharepoint/webpartpages" xmlns:ss=3D"urn:schemas-micr=
osoft-com:office:spreadsheet" xmlns:st=3D"" xmlns:sub=3D"http://schemas=
..microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:udc=3D"http://schemas.=
microsoft.com/data/udc" xmlns:udcp2p=3D"http://schemas.microsoft.com/data/u=
dc/parttopart" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" xm=
lns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" xmlns:v=3D"urn:=
schemas-microsoft-com:vml" xmlns:w=3D"urn:schemas-microsoft-com:office:word=
" xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/wor kflow/" xmlns=
:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:x2=3D"http://schemas.mi=
crosoft.com/office/excel/2003/xml" xmlns:xsd=3D"http://www.w3.org/2001/XMLS=
chema" xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" xmlns:Z=3D"u=
rn:schemas-microsoft-com:"><head><META content=3D"text/html; charset=3Dus-a=
scii" http-equiv=3D"Content-Type">
<META content=3D"text/html; charset=3Dus-ascii" http-equiv=3D"Content-Type"=
>

<meta content=3D"text/html; charset=3Dus-ascii" http-equiv=3DContent-Type>
<meta content=3D"Microsoft Word 12 (filtered medium)" name=3DGenerator>
<style>

</style>

</head><BODY>
<DIV STYLE=3D"FONT-SIZE: 9pt; FONT-FAMILY: Courier New">
<DIV>
<DIV>

<div class=3DSection1>

Dear all,<o:p></o:p>

<o:p> </o:p>

I have been playing with PITR on version 8.3 so far, b=
ut I
need to get it working on version 8.2.<o:p></o:p>

<o:p> </o:p>

What is the difference between:<o:p></o:p>

<o:p> </o:p>

archive_command
=3D on (is this command for 8.2?)<o:p></o:p>

<code>archive_mode
=3D on (This one is for 8.3!)<o:p></o:p></co=
de>

<code><o:p> </o:p></code>

<code>I
need to enable ‘archive_mode =3D on’, restart postgres, before =
I can
do the base backup, is that correct?<o:p></o:p></code>

<code><o:p> </o:p></code>

<code>In
order to bring the standby online I need a trigger file, something like:<o:=
p></o:p></code>

<code>can
I call the trigger file whatever I want? for example; ‘/var/log/postg=
res/postgresql.8.2.trigger’<o:p></o:p></code>

<code><o:p> </o:p></code>

Postgres
8.2 config<o:p></o:p>

<o:p> </o:p>

Master:<o:p></o:p>

 Postgresql.conf<o:p></o:p><=
/span>

 &nbs=
p; archive_command
=3D on <o:p></o:p>

 &nbs=
p; archive_command
=3D 'cp -i %p /mnt/pitr/%f </dev/null' <o:p></o:p>

 &nbs=
p; archive_timeout
=3D 60<o:p></o:p>

 <o:p></o:p>

 #
mkdir /mnt/pitr<o:p></o:p>

 #
chown postgres: /mnt/pitr (this will be an NFS mount point)<o:p></o:p></spa=
n>

 #
mount –t nfs fnsserver01:/pitr /mnt/pitr<o:p></o:p>

 #
service postgresql restart<o:p></o:p>

 #
su – postgres<o:p></o:p>

 #
psql –U postgres <o:p></o:p>

 &nb=
sp; <code>SELECT pg_start_backup('pitr_backup');</code><o:p></o:p>

 &nb=
sp; <code>\q<o:p></o:p></code>

<code> #
Tar –cvf pg_data.tar /usr/loca/pgsql/data<o:p></o:p></code></p=
>

<code> #
Psql –U postgres<o:p></o:p></code>

<code> =
; SELECT
pg_stop_backup();<o:p></o:p></code>

<code> #
cp pg_data.tar /mnt/pitr</code><o:p></o:p>

 <o:p></o:p>

<o:p> </o:p>

Postgres
8.2 config<o:p></o:p>

<o:p> </o:p>

StandBy:<o:p></o:p>

 #
Compile pg_standy from the contrib/ folder<o:p></o:p>

 #
cd /usr/local/pgsql<o:p></o:p>

 #
rm –rf data/<o:p></o:p>

 #
tar –xvf /mnt/pitr/<code>pg_data.tar<o:p></o:p></code>

<code> #
chown –R postgres: data<o:p></o:p></code>

<code> #
cd data/<o:p></o:p></code>

<code> #
cd pg_xlog/ - delete the content<o:p></o:p></code>

<code> #
cd ../data<o:p></o:p></code>

<code> #
touch recovery.conf<o:p></o:p></code>

<code> =
 restore_command
=3D 'pg_standby -l -d -s 2 -t /var/log/postgres/ postgresql.8.2.trigger /mn=
t/pitr/
%f %p %r 2>>pitr_standby.log'</code><o:p></o:p>

<o:p> </o:p>

 Postgresql.conf<o:p></o:p><=
/span>

 &nbs=
p; #archive_command
=3D off - disabled and commented out<o:p></o:p>

 &nbs=
p; #archive_command
=3D '' – disabled and command removed<o:p></o:p>

 &nbs=
p; #
archive_timeout =3D 60 – disabled<o:p></o:p>

<o:p> </o:p>

Is
this correct, am I missing something or anything here isn’t correct?&=
nbsp; <o:p></o:p>

<o:p> </o:p>

If anyone could give me some light with this it would =
be
very much appreciated.<o:p></o:p>

<o:p> </o:p>

Thank you very much<o:p></o:p>

<o:p> </o:p>

Renato<o:p></o:p>

<o:p> </o:p>

</div>

</DIV>
<DIV> </DIV>
<DIV>Renato=
Oliveira Systems Administrator e-mail: renato.oliveira [at] grant.co.uk</=
FONT></DIV>
<DIV></FONT=
> </DIV>
<DIV>Tel: +=
44 (0)1763 260811 Fax: +44 (0)1763 262410 <A HREF=3D"http://www.grant=
..co.uk/">www.grant.co.uk</A></DIV>
<DIV></FONT=
> </DIV>
<DIV>Grant =
Instruments (Cambridge) Ltd Company registered in England, re=
gistration number 658133 Registered office address: 29 Stat=
ion Road, Shepreth, CAMBS SG8 6GB UK</DIV>
<DIV></FONT=
></F=
ONT> </DIV>
<DIV></FONT=
></DIV>
<DIV></FONT=
> </DIV>
<DIV></FONT=
></DIV>
<DIV> </DIV>
<DIV></FONT=
></DIV>
<DIV> </DIV> </DIV>
<DIV>
 
P Please consider the environ=
ment before printing this email</DIV>
<DIV>CONFIDENTIALITY: The =
information in this e-mail and any attachments is confidential. It is inten=
ded only for the named recipients(s). If you are not the named recipient pl=
ease notify the sender immediately and do not disclose the contents to anot=
her person or take copies. </DIV>
<DIV> </DIV>
<DIV></DIV>
<DIV>VIRUSES: The contents=
of this e-mail or attachment(s) may contain viruses which could damage you=
r own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken e=
very reasonable precaution to minimise this risk, we cannot accept liabilit=
y for any damage which you sustain as a result of software viruses. You sho=
uld therefore carry out your own virus checks before opening the attachment=
(s).</DIV>
<DIV> </DIV>
<DIV></DIV>
<DIV>OpenXML: For informat=
ion about the OpenXML file format in use within Grant Instruments please vi=
sit our <A HREF=3D"http://www.grant.co.uk/Support/openxml.html">website</A>=
</DIV></DIV></BODY></HTML>

--_000_7965A9DCF12CC14984420BCC37B1608F25ABF310ABElzargrantc ou_--

turn pitr 'on' on PostgreSQL 8.2 - pg_standby

Re: turn pitr 'on' on PostgreSQL 8.2 - pg_standby