Question on moving data to new partitions

This is a multi-part message in MIME format.

------_=_NextPart_001_01CA94B3.C264469C
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have some tables which have an extremely high amount of update
activity on them. I have changed autovacuum parameters (cost delay and
limit), and whereas before they would never be vacuumed and bloat they
are running fine.

However, as the platform scales, I am afraid I will reach the same
situation.

As a result, I have decided to partition the table and add to each
record a partition id, which can be used to route it to the correct
partition.

Presently, all of the records reside on what will ultimately become the
parent partition.

What would be the best way of moving the data to the pertinent
partitions?

I was thinking of copying the data to another table and then performing
a insert into partitionedtableparent select * from temporary table, and
then performing a delete from only partitionedtableparent.

Does this sound like a reasonable way of doing this? Is there a more
efficient way of doing this?

------_=_NextPart_001_01CA94B3.C264469C
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-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
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>

<p class=3DMsoNormal>I have some tables which have an extremely high =
amount of update
activity on them. I have changed autovacuum parameters (cost delay =
and
limit), and whereas before they would never be vacuumed and bloat they =
are
running fine.<o:p></o:p></p>

<p class=3DMsoNormal>However, as the platform scales, I am afraid I will =
reach
the same situation.<o:p></o:p></p>

<p class=3DMsoNormal>As a result, I have decided to partition the table =
and add
to each record a partition id, which can be used to route it to the =
correct
partition.<o:p></o:p></p>

<p class=3DMsoNormal>Presently, all of the records reside on what will =
ultimately
become the parent partition.<o:p></o:p></p>

<p class=3DMsoNormal>What would be the best way of moving the data to =
the
pertinent partitions?<o:p></o:p></p>

<p class=3DMsoNormal>I was thinking of copying the data to another table =
and then
performing a insert into partitionedtableparent select * from temporary =
table,
and then performing a delete from only =
partitionedtableparent.<o:p></o:p></p>

<p class=3DMsoNormal>Does this sound like a reasonable way of doing =
this? Is
there a more efficient way of doing this?<o:p></o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

</div>

</body>

</html>

------_=_NextPart_001_01CA94B3.C264469C--

On Wed, Jan 13, 2010 at 5:51 PM, Benjamin Krajmalnik <kraj [at] illumen.com> wro=
te:
> I have some tables which have an extremely high amount of update activity=
on
> them.=A0 I have changed autovacuum parameters (cost delay and limit), and
> whereas before they would never be vacuumed and bloat they are running fi=
ne.
>
> However, as the platform scales, I am afraid I will reach the same
> situation.
>
> As a result, I have decided to partition the table and add to each record=
a
> partition id, which can be used to route it to the correct partition.
>
> Presently, all of the records reside on what will ultimately become the
> parent partition.

Are you using table inheritance to do this? or are they all independent ta=
bles?

> What would be the best way of moving the data to the pertinent partitions?
>
> I was thinking of copying the data to another table and then performing a
> insert into partitionedtableparent select * from temporary table, and then
> performing a delete from only partitionedtableparent.
>
> Does this sound like a reasonable way of doing this? =A0Is there a more
> efficient way of doing this?

You can probably skip a few steps there if you copy straight to the
destination table.

At work, where we have partitioned out some tables, I made a trigger
based inherited table setup, and basically did something like:

insert into master_table select * from master_table where id between 1
and 100000;
delete from only master_table where id between 1 and 100000;

Then incremented the between values until all the tuples had been moved, th=
en I

truncate only master_table;

and it worked like a charm.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Yes, I will be using table inheritance and inheriting the current table whe=
re the data resides.
I was wondering if it would be "kosher" to perform the insert on itself, bu=
t I guess since the rules engine takes over there should not be a problem.
The tables are not huge per se (a little over 50K records). The problem is=
that each record gets updated at least 500 times per day, so the row versi=
ons are quite extensive and need to be vacuumed often. Can't afford to tak=
e chances on the tables bloating because, from experience, it will slow dow=
n the system and create a snowball effect where data coming in gets backed =
up. By keeping the number of records in each partition small, I can ensure=
that autovacuum will always be able to run. As the need arises, I can alw=
ays create additional partitions to accommodate for the growth.

As always, thank you very much Scott. You are always very helpful.

> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe [at] gmail.com]
> Sent: Wednesday, January 13, 2010 5:58 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin [at] postgresql.org
> Subject: Re: [ADMIN] Question on moving data to new partitions
>
> On Wed, Jan 13, 2010 at 5:51 PM, Benjamin Krajmalnik <kraj [at] illumen.com>
> wrote:
> > I have some tables which have an extremely high amount of update
> activity on
> > them.=A0 I have changed autovacuum parameters (cost delay and limit),
> and
> > whereas before they would never be vacuumed and bloat they are
> running fine.
> >
> > However, as the platform scales, I am afraid I will reach the same
> > situation.
> >
> > As a result, I have decided to partition the table and add to each
> record a
> > partition id, which can be used to route it to the correct partition.
> >
> > Presently, all of the records reside on what will ultimately become
> the
> > parent partition.
>
> Are you using table inheritance to do this? or are they all
> independent tables?
>
> > What would be the best way of moving the data to the pertinent
> partitions?
> >
> > I was thinking of copying the data to another table and then
> performing a
> > insert into partitionedtableparent select * from temporary table, and
> then
> > performing a delete from only partitionedtableparent.
> >
> > Does this sound like a reasonable way of doing this? =A0Is there a more
> > efficient way of doing this?
>
> You can probably skip a few steps there if you copy straight to the
> destination table.
>
> At work, where we have partitioned out some tables, I made a trigger
> based inherited table setup, and basically did something like:
>
> insert into master_table select * from master_table where id between 1
> and 100000;
> delete from only master_table where id between 1 and 100000;
>
> Then incremented the between values until all the tuples had been
> moved, then I
>
> truncate only master_table;
>
> and it worked like a charm.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, Jan 13, 2010 at 6:11 PM, Benjamin Krajmalnik <kraj [at] illumen.com> wro=
te:
> Yes, I will be using table inheritance and inheriting the current table w=
here the data resides.
> I was wondering if it would be "kosher" to perform the insert on itself, =
but I guess since the rules engine takes over there should not be a problem.
> The tables are not huge per se (a little over 50K records). =A0The proble=
m is that each record gets updated at least 500 times per day, so the row v=
ersions are quite extensive and need to be vacuumed often. =A0Can't afford =
to take chances on the tables bloating because, from experience, it will sl=
ow down the system and create a snowball effect where data coming in gets b=
acked up. =A0By keeping the number of records in each partition small, I ca=
n ensure that autovacuum will always be able to run. =A0As the need arises,=
I can always create additional partitions to accommodate for the growth.
>
> As always, thank you very much Scott. =A0You are always very helpful.

My one recommendation would be to look at using triggers over rules.
I have a simple cronjob written in php that creates new partitions and
triggers each night at midnight. Triggers are MUCH faster than rules
for partitioning, but making them fancy is a giant pain in plpgsql. I
just write a big trigger with an if/elseif/else tree that handles each
situation. It runs very fast.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Scott Marlowe wrote:
> On Wed, Jan 13, 2010 at 6:11 PM, Benjamin Krajmalnik <kraj [at] illumen.com> wrote:
>
>> Yes, I will be using table inheritance and inheriting the current table where the data resides.
>> I was wondering if it would be "kosher" to perform the insert on itself, but I guess since the rules engine takes over there should not be a problem.
>> The tables are not huge per se (a little over 50K records). The problem is that each record gets updated at least 500 times per day, so the row versions are quite extensive and need to be vacuumed often. Can't afford to take chances on the tables bloating because, from experience, it will slow down the system and create a snowball effect where data coming in gets backed up. By keeping the number of records in each partition small, I can ensure that autovacuum will always be able to run. As the need arises, I can always create additional partitions to accommodate for the growth.
>>
>> As always, thank you very much Scott. You are always very helpful.
>>
>
> My one recommendation would be to look at using triggers over rules.
> I have a simple cronjob written in php that creates new partitions and
> triggers each night at midnight. Triggers are MUCH faster than rules
> for partitioning, but making them fancy is a giant pain in plpgsql. I
> just write a big trigger with an if/elseif/else tree that handles each
> situation. It runs very fast.
>
>
Hi,
I am currently looking into partitioning a table of which 90% of the
lookups are for the prior week. It has about 9 million rows and
selects are a bit slow, since the table is joined to two other
tables. I am planning on doing a range partition ie each year starting
from 2005 will be its own partition. So the check constraints will be
year based. I have run tests and what I see is that the optimizer can
find the correct table when I search by year, but when I search by say
recid (PK), it does a seq scan on every single child table.
To have the optimizer recognize the recid, do I need to include that in
the check constraint?

2. When you say you wrote a trigger, was it instead of the insert rule?

This is pretty new stuff to me and any insight into this would be helpful.

Thanks,
Radhika

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, Jan 13, 2010 at 7:30 PM, Radhika Sambamurti <rs1 [at] speakeasy.net> wro=
te:
>
> Hi,
> I am currently looking into partitioning a table of which 90% of the look=
ups
> are for the prior week. It has about 9 million rows and =A0selects =A0are=
a bit
> slow, since =A0the table is joined to =A0two other tables. =A0I am planni=
ng on
> doing a range partition ie each year starting from 2005 will be its own
> partition. So the check constraints will be year based. I have run tests =
and
> what I see is that the optimizer can find the correct table when I search=
by
> year, but when I search by say recid (PK), it does a seq scan on every
> single child table.

Do you have an index on each of the tables on recid?

> To have the optimizer recognize the recid, do I need to include that in t=
he
> check constraint?

Not sure. I'd have to test it. I thought the query planner was smart
enough to tell if an index would be useful even if it had to hit it
for each table.

> 2. When you say you wrote a trigger, was it instead of the insert rule?

Yes. using rules results in much worse insert performance than a
trigger. Generally. However, since a rule re-writes queries, if a
single query were to insert many thousands of rows, a rule might be
faster than a trigger, which fires for each row even if they all come
from the same query.

> This is pretty new stuff to me and any insight into this would be helpful.

As Cole Porter would say, "Experiment"...

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Benjamin Krajmalnik" <kraj [at] illumen.com> writes:
> As a result, I have decided to partition the table and add to each record a partition id, which can be used to route it to the correct partition.
>
> Presently, all of the records reside on what will ultimately become the parent partition.
>
> What would be the best way of moving the data to the pertinent
> partitions?

What I use to do is to rename the current table partition_201001, say,
with a CHECK constraint date < 2010-01-01. In case of date based ranges
of course.

Then create the new parent table, empty, and set up the inheritance and
trigger. Then create some more future child tables, and commit.

New data is being routed, old data all packed. You can reshuffle the
archive like table later on, if needed.

Also, as noted down-thread, avoid rules, prefer triggers. One of the
reasons is locking behavior, where drop partition when using rules will
lock against running queries against parent table.

Regards,
--
dim

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Dear all,

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

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

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

I have created the folder: /data/pgsql/archives and changed ownership to po=
stgres: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 0000000100000000=
00000000
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 0000000100000000=
00000001
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 0000000100000000=
00000002
16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 0000000100000000=
00000003
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 0000000100000000=
00000004
4 -rw------- 1 postgres postgres 247 Jan 13 12:41 0000000100000000=
00000004.00C18E68.backup
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 0000000100000000=
00000005
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 0000000100000000=
00000006
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 0000000100000000=
00000007
16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 0000000100000000=
00000008

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 post=
gres/source, not sure how this would help me, if anyone could help me expla=
ining it to me, it would be really helpful.

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

Do I need to turn this "ON" somewhere, because there seems to be an inconsi=
stency on information around:
Some people says I have to turn archive_mode =3D on, some says I have to us=
e archive_command =3D on

Do I need to do similar for recovery.conf?

Once I have rsync shipping logs to the remote server, recovery.conf configu=
red, 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.oliveira [at] grant.co.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 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 http://www.grant.co.uk/Support/openxml.html

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Thu, 14 Jan 2010 14:33:52 +0000, Renato Oliveira
<renato.oliveira [at] grant.co.uk> wrote:
> 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).

Get off 8.1.18, use www.pgsqlrpms.org.

You want AT LEAST 8.3.

Also, make your life even easier:

https://projects.commandprompt.com/public/pitrtools

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

Julio,

Thank you for your reply, I was wondering if anyone has encountered problem=
s 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.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

From: Julio Leyva [mailto:jcleyva [at] hotmail.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.oliveira [at] grant.co.uk
> To: pgsql-admin [at] postgresql.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 rob=
ust 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 =3D on
> archive_command =3D '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 00000001000000=
0000000000
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 00000001000000=
0000000001
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 00000001000000=
0000000002
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 00000001000000=
0000000003
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 00000001000000=
0000000004
> 4 -rw------- 1 postgres postgres 247 Jan 13 12:41 00000001000000000000000=
4.00C18E68.backup
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 00000001000000=
0000000005
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 00000001000000=
0000000006
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 00000001000000=
0000000007
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59 00000001000000=
0000000008
>
> 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 po=
stgres/source, not sure how this would help me, if anyone could help me exp=
laining 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 =3D 'cp /data/pgsql/archives/%f %p'
>
> Do I need to turn this "ON" somewhere, because there seems to be an incon=
sistency on information around:
> Some people says I have to turn archive_mode =3D on, some says I have to =
use archive_command =3D on
>
> Do I need to do similar for recovery.conf?
>
> Once I have rsync shipping logs to the remote server, recovery.conf confi=
gured, 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.oliveira [at] grant.co.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 co=
nfidential. It is intended only for the named recipients(s). If you are not=
the named recipient please notify the sender immediately and do not disclo=
se 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 (Cam=
bridge) Ltd has taken every reasonable precaution to minimise this risk, we=
cannot accept liability for any damage which you sustain as a result of so=
ftware 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 Gran=
t Instruments please visit our http://www.grant.co.uk/Support/openxml.html
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.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 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_7965A9DCF12CC14984420BCC37B1608F2584EA3CCFElzargrantc 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=3D"Content-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><FONT FACE=3D"Arial" SIZE=3D"2">

<div class=3DSection1>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>Julio,<o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'><o:p> </o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>Thank you for your reply, I was wondering if anyone has
encountered problems setting up PITR with 8.2.4?<o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>Unfortunately our live system runs 8.2.4 and it is quite tri=
cky
to upgrade it right now.<o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'><o:p> </o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>By the way where can I find a how to use pitr-tools?<o:p></o=
:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'><o:p> </o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>Thank you very much<o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'><o:p> </o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>Best regards<o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'><o:p> </o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'>Renato<o:p></o:p></span></p>

<p class=3DMsoNormal><span style=3D'font-size:11.0pt;font-family:"Calibri",=
"sans-serif";
color:#1F497D'><o:p> </o:p></span></p>

<div>

</FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"></FONT> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2">Renato=
Oliveira<BR><BR>e-mail: renato.oliveira [at] grant.co.uk</FONT></FONT><FONT FAC=
E=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT></FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT=
></FONT> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2">Tel: +=
44 (0)1763 260811<BR>Fax: +44 (0)1763 262410<BR><A HREF=3D"http://www.grant=
..co.uk/">www.grant.co.uk</A></FONT></FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT=
></FONT> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2">Grant =
Instruments (Cambridge) Ltd <BR> <BR>Company registered in England, re=
gistration number 658133<BR> <BR>Registered office address:<BR>29 Stat=
ion Road, <BR>Shepreth, <BR>CAMBS SG8 6GB <BR>UK</FONT></FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT=
></FONT><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></F=
ONT></FONT> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT=
></FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT=
></FONT> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2">

<p class=3DMsoNormal><b><span lang=3DEN-US style=3D'font-size:10.0pt;font-f=
amily:
"Tahoma","sans-serif"'>From:</span></b><span lang=3DEN-US style=3D'font-siz=
e:10.0pt;
font-family:"Tahoma","sans-serif"'> Julio Leyva [mailto:jcleyva [at] hotmail.com=
] <br>
<b>Sent:</b> 14 January 2010 18:47<br>
<b>To:</b> Renato Oliveira<br>
<b>Subject:</b> RE: [ADMIN] PITR online backups Setup<o:p></o:p></span></p>

</div>

</div>

<p class=3DMsoNormal><o:p> </o:p></p>

<p class=3DMsoNormal><span style=3D'font-size:10.0pt;font-family:"Verdana",=
"sans-serif"'>You
better update to postgresql 8.3<br>
PITR works very well with this version<br>
<br>
I remember trying to setup it with 8.1 and It did not work.<br>
<br>
<br>
> From: renato.oliveira [at] grant.co.uk<br>
> To: pgsql-admin [at] postgresql.org<br>
> Date: Thu, 14 Jan 2010 14:33:52 +0000<br>
> Subject: [ADMIN] PITR online backups Setup<br>
> <br>
> Dear all,<br>
> <br>
> I am trying to setup PITR for online backup and also to create a more
robust setup, in case our primary Postgres dies.<br>
> <br>
> I am testing this setup on Centos 5.4, Postgres version: 8.1.18 (not s=
ure
why Centos has this old version by default).<br>
> <br>
> I have enabled PITR, with the following commands:<br>
> /var/lib/pgsql/data/postgresql.conf<br>
> archive_command =3D on<br>
> archive_command =3D 'cp -i %p /data/pgsql/archives/%f </dev/null'<b=
r>
> <br>
> I have created the folder: /data/pgsql/archives and changed ownership =
to
postgres:postgres<br>
> <br>
> I can see the archiving seems to be working:<br>
> Ls -ls /data/pgsql/archives<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 00000001000=
0000000000000<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12
000000010000000000000001<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12
000000010000000000000002<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 10:12 00000001000=
0000000000003<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59
000000010000000000000004<br>
> 4 -rw------- 1 postgres postgres 247 Jan 13 12:41
000000010000000000000004.00C18E68.backup<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59
000000010000000000000005<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59
000000010000000000000006<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59
000000010000000000000007<br>
> 16404 -rw------- 1 postgres postgres 16777216 Jan 13 12:59
000000010000000000000008<br>
> <br>
> I have done the base backup:<br>
> Psql<br>
> select pg_start_backup('Full Backup - Master');<br>
> tar -cvzf /var/lib/pgsql/data/potgresMASTER.tar /var/lib/pgsql/data/<b=
r>
> psql<br>
> select pg_stop_backup();<br>
> <br>
> Now I am trying to setup the secondary server, this is where I am stuc=
k.<br>
> <br>
> 1 - I tried to setup rsync to ship these logs across to the remote ser=
ver,
but I can't get postgres to work with authorized_keys<br>
> How you guys are doing this? NFS will not be an option.<br>
> <br>
> WARM server setup<br>
> On the Standby Server<br>
> I have restored the base backup<br>
> Tar -zxvf potgresMASTER.tar under /var/lib/pgsql/data<br>
> <br>
> 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.<br>
> <br>
> Then I need to create a /var/lib/pgsql/data/recovery.conf and add simi=
lar
lines<br>
> restore_command =3D 'cp /data/pgsql/archives/%f %p'<br>
> <br>
> Do I need to turn this "ON" somewhere, because there seems t=
o be
an inconsistency on information around:<br>
> Some people says I have to turn archive_mode =3D on, some says I have =
to use
archive_command =3D on<br>
> <br>
> Do I need to do similar for recovery.conf?<br>
> <br>
> 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?<b=
r>
> Are there any other aspect that I need to consider?<br>
> <br>
> I would be very thankful too all of you for any helps.<br>
> <br>
> Thank you very much for all your repplies<br>
> <br>
> Best regards<br>
> <br>
> <br>
> <br>
> <br>
> Renato Oliveira<br>
> <br>
> e-mail: renato.oliveira [at] grant.co.uk<br>
> <br>
> Tel: +44 (0)1763 260811<br>
> Fax: +44 (0)1763 262410<br>
> http://www.grant.co.uk/<br>
> <br>
> Grant Instruments (Cambridge) Ltd<br>
> <br>
> Company registered in England, registration number 658133<br>
> <br>
> Registered office address:<br>
> 29 Station Road,<br>
> Shepreth,<br>
> CAMBS SG8 6GB<br>
> UK<br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> P Please consider the environment before printing this email<br>
> CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you are n=
ot
the named recipient please notify the sender immediately and do not disclos=
e
the contents to another person or take copies.<br>
> <br>
> VIRUSES: The contents of this e-mail or attachment(s) may contain viru=
ses
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 sof=
tware
viruses. You should therefore carry out your own virus checks before openin=
g
the attachment(s).<br>
> <br>
> OpenXML: For information about the OpenXML file format in use within G=
rant
Instruments please visit our http://www.grant.co.uk/Support/openxml.html<br=
>
> <br>
> <br>
> -- <br>
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)<br>
> To make changes to your subscription:<br>
> http://www.postgresql.org/mailpref/pgsql-admin<o:p></o:p></span></p>

</div>

</FONT></FONT></DIV>
<DIV> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><FONT FACE=3D"Arial" SIZE=3D"2"></FONT=
></FONT></DIV>
<DIV> </DIV> </DIV>
<DIV>
<P CLASS=3D"MsoNormal"><EM><B><SPAN LANG=3D"EN-US" STYLE=3D"FONT-SIZE: 18pt=
; COLOR: green; FONT-FAMILY: Webdings"></SPAN></B></EM> </P>
<P CLASS=3D"MsoNormal"><EM><B><SPAN LANG=3D"EN-US" STYLE=3D"FONT-SIZE: 18pt=
; COLOR: green; FONT-FAMILY: Webdings">P</SPAN></B></EM><EM><B><SPAN LANG=
=3D"EN-US" STYLE=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Verdana','=
sans-serif'"> </SPAN></B></EM><STRONG><I><SPAN STYLE=3D"FONT-SIZE: 7.5pt; C=
OLOR: green; FONT-FAMILY: 'Arial','sans-serif'">Please consider the environ=
ment before printing this email</SPAN></I></STRONG></P></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><STRONG>CONFIDENTIALITY</STRONG>: 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. </FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"></FONT> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><STRONG></STRONG></FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><STRONG>VIRUSES:</STRONG> 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).</FONT></DIV>
<DIV> </DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"></FONT></DIV>
<DIV><FONT FACE=3D"Arial" SIZE=3D"2"><STRONG>OpenXML</STRONG>: 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>=
</FONT></DIV></DIV></BODY></HTML>

--_000_7965A9DCF12CC14984420BCC37B1608F2584EA3CCFElzargrantc ou_--

On Mon, 2010-01-18 at 13:41 +0000, Renato Oliveira wrote:
> Julio,
>

> 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?

https://projects.commandprompt.com/public/pitrtools

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Dear all,

I have been thinking about PostgreSQL backup for some time now.

I can't implement PITR right now on our live systems, for commercial reason=
s.

I have been backing up the server with PG_DUMP every two days, reason it ta=
kes some times more than 24 Hours to backup the full database.

I have an idea not sure how workable it is:
1 - Backup live server pipe the content of pg_dump to psql and restore it t=
o the second database server.
I have tested this with a small database on my test model and it works, not=
sure how long it will take though.

2 - I also thought, once I have backed up the full database, I am going to =
see if it is possible to check which tables have changed and only backup th=
ose to the remote server.
Not sure if it is possible to figure out which tables have changed, is ther=
e log or some command which can tell me which tables have changed?

3 - Another idea would be, to backup the full DB and then check when was th=
e last update and from there do a backup and restore remotely.
For example: Last update was at 13:00, so from 13:00 onwards I would copy a=
ll the records and restore on the remote server.
Is it possible to find out what was the last minute which we had an update,=
and then backup only the records which were updated to the current time?
If so, how would I go about to do that?

4 - Backup instead of time use transactionID
Do a full backup, mark what was the last transactionID to the minute of bac=
kup finish and then onwards to backups only for the updated transactionIDs.
For example: Full backup finishes at 13:00, the last transactionID at 13:00=
would be 00013, then from 13:01 onwards backup the updates, so on.

I am not sure if some of these things are possible, these are only ideas an=
d I would appreciate any input and help, in either build it or destroying i=
t.

If anyone has a backup script which handles failure and emails out and woul=
d like to share, for me to study it, I would very much appreciate it.

If you need more details why, reasons etc, please email me and I will clari=
fy.

I am trying to work around the problems I am facing currently.

Thank you very much.

Really appreciate any help and input

Best regards

Renato

Renato Oliveira

e-mail: renato.oliveira [at] grant.co.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 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 http://www.grant.co.uk/Support/openxml.html

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Renato Oliveira wrote:
> Dear all,
>
> I have been thinking about PostgreSQL backup for some time now.
>
> I can't implement PITR right now on our live systems, for commercial
> reasons.

I think you need to rethink this one...

> 4 - Backup instead of time use transactionID Do a full backup, mark
> what was the last transactionID to the minute of backup finish and
> then onwards to backups only for the updated transactionIDs. For
> example: Full backup finishes at 13:00, the last transactionID at
> 13:00 would be 00013, then from 13:01 onwards backup the updates, so
> on.

This is conceptually PITR.

--
Jesper

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Renato Oliveira wrote:

> I can't implement PITR right now on our live systems, for
> commercial reasons.

What do you mean? Most of your email seems to describe techniques
very much like PITR; why would that be OK but the existing, tested
PITR not be OK? It's hard to know what to suggest without
understanding the answers to those questions.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--0016367fa8f57c5c44047d973df6
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jan 20, 2010 at 6:48 AM, Kevin Grittner <Kevin.Grittner [at] wicourts.gov
> wrote:

> Renato Oliveira wrote:
>
> > I can't implement PITR right now on our live systems, for
> > commercial reasons.
>
> What do you mean? Most of your email seems to describe techniques
> very much like PITR; why would that be OK but the existing, tested
> PITR not be OK? It's hard to know what to suggest without
> understanding the answers to those questions.
>

PITR is a one or two line update to the postgresql.conf + a base backup.
What you're talking about is trying to build your own version of this
(significantly more complex). What are the 'commercial' reasons that you
have for not using PITR, ISTM that you'd really be wasting time not using
it.

--Scott

--0016367fa8f57c5c44047d973df6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><div class=3D"gmail_quote">On Wed, Jan 20, 2010 at 6:48 AM, Kevin Gritt=
ner <span dir=3D"ltr"><<a href=3D"mailto:Kevin.Grittner [at] wicourts.gov">Ke=
vin.Grittner [at] wicourts.gov</a>></span> wrote:<br><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex;">
<div class=3D"im">Renato Oliveira =A0wrote:<br>
<br>
> I can't implement PITR right now on our live systems, for<br>
> commercial reasons.<br>
<br>
</div>What do you mean? =A0Most of your email seems to describe techniques<=
br>
very much like PITR; why would that be OK but the existing, tested<br>
PITR not be OK? =A0It's hard to know what to suggest without<br>
understanding the answers to those questions.<br></blockquote><div><br></di=
v><div>=A0PITR is a one or two line update to the postgresql.conf + a base =
backup. =A0What you're talking about is trying to build your own versio=
n of this (significantly more complex). =A0What are the 'commercial'=
; reasons that you have for not using PITR, ISTM that you'd really be w=
asting time not using it.</div>
<div><br></div><div>--Scott=A0</div></div>

--0016367fa8f57c5c44047d973df6--

I just noticed that the list has not been copied on most of this
exchange. Please keep the list copied, as others are likely to
contribute useful ideas.

Renato Oliveira <renato.oliveira [at] grant.co.uk> wrote:

> Can I ask few questions about it, if possible?

Sure. Please keep it "on list", though.

> We are running Linux Redhat 4.

That should make building from source pretty easy.

> [postgres [at] 78674-db1 ~]$ pg_config
> VERSION = PostgreSQL 8.2.4

> [postgres [at] db2 ~]$ pg_config
> VERSION = PostgreSQL 8.3.4

Well, there's your problem, right there. Your primary is 8.2.4, but
your secondary is 8.3.4. The other information matches nicely and
there were no special build options -- it's just that there are two
different major versions of PostgreSQL here, and PITR backups
definitely won't deal with that.

Is there a reason for having your secondary server on a newer major
release (like there was some query which wouldn't run correctly or
within a reasonable amount of time without it)?

Wait -- I just had a thought. Old version copying to new version.
Slony in the mix. This sounds like a situation where the old DBA
was trying to use Slony to upgrade with minimal down time. Problem
was, you haven't been able to keep the newer version up-to-date
through Slony, so you couldn't cut over? If that's the situation,
it changes the focus. And it also means we need someone who
understands Slony in the discussion....

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, 2010-01-20 at 10:04 -0600, Kevin Grittner wrote:
> I just noticed that the list has not been copied on most of this
> exchange. Please keep the list copied, as others are likely to
> contribute useful ideas.
>
> Renato Oliveira <renato.oliveira [at] grant.co.uk> wrote:
>
> > Can I ask few questions about it, if possible?
>
> Sure. Please keep it "on list", though.
>
> > We are running Linux Redhat 4.
>
> That should make building from source pretty easy.
>
> > [postgres [at] 78674-db1 ~]$ pg_config
> > VERSION = PostgreSQL 8.2.4
>
> > [postgres [at] db2 ~]$ pg_config
> > VERSION = PostgreSQL 8.3.4
>
> Well, there's your problem, right there. Your primary is 8.2.4, but
> your secondary is 8.3.4. The other information matches nicely and
> there were no special build options -- it's just that there are two
> different major versions of PostgreSQL here, and PITR backups
> definitely won't deal with that.
>
> Is there a reason for having your secondary server on a newer major
> release (like there was some query which wouldn't run correctly or
> within a reasonable amount of time without it)?
>
> Wait -- I just had a thought. Old version copying to new version.
> Slony in the mix. This sounds like a situation where the old DBA
> was trying to use Slony to upgrade with minimal down time. Problem
> was, you haven't been able to keep the newer version up-to-date
> through Slony, so you couldn't cut over? If that's the situation,
> it changes the focus. And it also means we need someone who
> understands Slony in the discussion....

If the issue here is the OP wants to move from 8.2.4 to 8.3.9 (as
opposed to 8.3.4) with Slony, here is what to do.

1: Build 8.3.9 from source.
2: Build the same Slony version of slony that is running against the
8.3.9 tree
3 : Upgrade the 8.3.4 version to 8.3.9 (Slony is irrelevant here -
normal minor version PG upgrade)
4: When ready to upgrade, issue the Slony Move Master command to move
from the 8.2.4 node to the 8.3.9.
5: If you still need a Slony replica - drop the 8.2.4 node from
replication and rebuild it as an 8.3.9 replica.

OP if you need any more information about the Slony portions of this -
please ask them on the Slony list.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Question on moving data to new partitions

Re: Question on moving data to new partitions

Re: Question on moving data to new partitions

Re: Question on moving data to new partitions

Re: Question on moving data to new partitions

Re: Question on moving data to new partitions

Re: Question on moving data to new partitions

PITR online backups Setup

Re: PITR online backups Setup

Re: PITR online backups Setup

Re: PITR online backups Setup

PostgreSQL backup idea

Re: PostgreSQL backup idea

Re: PostgreSQL backup idea

Re: PostgreSQL backup idea

Re: PostgreSQL backup idea

Re: PostgreSQL backup idea