upgrade 8.3.7 to 8.3.10 combined with ownership change

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

Hi

Further to David Jantzen's post of 3/16/2010.

We have decided to try to upgrade the database software from our old custom=
8.3.7 instance to the std upstream 8.3.10 version.
We have no hash indexes on interval types; we therefore anticipate that the=
migration will be a shutdown of the old instance, and restart on the std p=
gdb version, using the normal /etc/init.d/postgresql script.

The database is about 2.5Tb in size, backup takes a long time, we want to t=
ry to preserve the standby integrity.
We are currently running a warm standby, which updates using the following =
string in recovery.conf:

restore_command =3D 'pg_standby -c -d -s 10 -t /tmp/pgsql.t=
rigger.5432 /caesius/archive %f %p %r 2>>standby.log'

-------------
PROBLEM:

Testing this now on a smaller database but with the same software setup, we=
experience a problem on the standby:

- The pg_standby procedure doesn't always shutdown cleanly on the =
standby

o Checking active processes on the standby server following the issue of =
"pg_ctl stop", the pg_standby process is still running

o If we kill the pg_standby process, it switches timeline on startup

--------------

I am sure there must be a stable activity sequence to stopping/starting the=
primary/standby instances for software maintenance.
We would like to avoid having to rebuild the standby from a backup if possi=
ble.
Hence this posting.

--------------

A few more details about the sequence of actions I am trying to follow:

- Install postgres via yum repo (using rpm 8.3-7 http://yum.pgsqlr=
pms.org/reporpms/repoview/pgdg-centos.html)

o On primary

o On standby

- Setup /etc/sysconfig/pgsql/postgresql customization file

o Point at data directory

- Under old user and software:

o Switch WAL on primary, wait for WAL to recover on the standby

o Shutdown standby (pg_ctl stop -m immediate)

o Shutdown primary (pg_ctl stop -m immediate)

- Change ownership of database files to postgres

o On primary

o On standby

- Using root "service postgresql start"

o On primary

o On standby

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

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

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

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hi<o:p></o:p>

<o:p> </o:p>

Further to David Jantzen’s post of 3/16/2010.<o:=
p></o:p>

<o:p> </o:p>

We have decided to try to upgrade the database softwar=
e from
our old custom 8.3.7 instance to the std upstream 8.3.10 version.<o:p></o:p=
>

We have no hash indexes on interval types; we therefor=
e
anticipate that the migration will be a shutdown of the old instance, and
restart on the std pgdb version, using the normal /etc/init.d/postgresql
script.<o:p></o:p>

<o:p> </o:p>

The database is about 2.5Tb in size, backup takes a lo=
ng
time, we want to try to preserve the standby integrity.<o:p></o:p>

We are currently running a warm standby, which updates=
using
the following string in recovery.conf:<o:p></o:p>

<o:p> </o:p>

 =
 restore_command
=3D 'pg_standby -c -d -s 10 -t /tmp/pgsql.trigger.5432 /caesius/archive %f =
%p %r
2>>standby.log'<o:p></o:p>

<o:p> </o:p>

-------------<o:p></o:p>

PROBLEM:<o:p></o:p>

<o:p> </o:p>

Testing this now on a smaller database but with the sa=
me
software setup, we experience a problem on the standby:<o:p></o:p>

<![if !supportLists]>-&nb=
sp;
<![endif]>The pg_standby procedure doesn’t always shutd=
own cleanly
on the standby<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>Checking active processes on the standby ser=
ver
following the issue of “pg_ctl stop”, the pg_standby process is
still running<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>If we kill the pg_standby process, it switch=
es
timeline on startup<o:p></o:p>

<o:p> </o:p>

--------------<o:p></o:p>

<o:p> </o:p>

I am sure there must be a stable activity sequence to =
stopping/starting
the primary/standby instances for software maintenance.<o:p></o:p>

We would like to avoid having to rebuild the standby f=
rom a
backup if possible.<o:p></o:p>

Hence this posting.<o:p></o:p>

<o:p> </o:p>

--------------<o:p></o:p>

<o:p> </o:p>

A few more details about the sequence of actions I am =
trying
to follow: <o:p></o:p>

<o:p> </o:p>

<![if !supportLists]>-&nb=
sp;
<![endif]>Install postgres via yum repo (using rpm 8.3-7 <a
href=3D"http://yum.pgsqlrpms.org/reporpms/repoview/pgdg-cent os.html">http:/=
/yum.pgsqlrpms.org/reporpms/repoview/pgdg-centos.html</a>)<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>On primary<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>On standby<o:p></o:p>

<![if !supportLists]>-&nb=
sp;
<![endif]>Setup /etc/sysconfig/pgsql/postgresql customization
file<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>Point at data directory<o:p></o:p>

<![if !supportLists]>-&nb=
sp;
<![endif]>Under old user and software:<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>Switch WAL on primary, wait for WAL to recov=
er
on the standby<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>Shutdown standby (pg_ctl stop –m immed=
iate)<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>Shutdown primary (pg_ctl stop –m immed=
iate)<o:p></o:p>

<![if !supportLists]>-&nb=
sp;
<![endif]>Change ownership of database files to postgres<o:p>=
</o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>On primary<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>On standby<o:p></o:p>

<![if !supportLists]>-&nb=
sp;
<![endif]>Using root “service postgresql start”<o=
:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>On primary<o:p></o:p>

<![if !supportLists]>o&nb=
sp;
<![endif]>On standby<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

</div>

</body>

</html>

--_000_FD020D3E50E7FA479567872E5F5F31E304594D4CA7ex01corpql2 co_--