Mirroring databases in v8

[Glen Eustace]

I thought I had seen something that indicated that v8 was going to
provide a means to mirror databases without needing all the triggers
etc. I downloaded beta 3 but couldn't see anything that would do this.

My goal is to mirror a database from one server to another, hopefully
from the logs so that there is nothing added to the database. The
mirrored db will be used by the remote server in a read-only fashion and
will become read-write if the master is no longer available. The
transition from RO to RW will involve a reboot of the server as there
are lots of other things that need to be started with different
properties as well.

Is this functionality available in v8 ?
If not, is it likely to be at any stage ?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: [phone], Mob: [phone], Web: www.godzone.net.nz

"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Shridhar Daithankar]

On Wednesday 06 Oct 2004 12:20 pm, Glen Eustace wrote:

I thought I had seen something that indicated that v8 was going to
provide a means to mirror databases without needing all the triggers
etc. I downloaded beta 3 but couldn't see anything that would do this.

My goal is to mirror a database from one server to another, hopefully
from the logs so that there is nothing added to the database. The
mirrored db will be used by the remote server in a read-only fashion and
will become read-write if the master is no longer available. The
transition from RO to RW will involve a reboot of the server as there
are lots of other things that need to be started with different
properties as well.

Well, you could start off with PITR capabilities of 8.0. But you may need to
do quite a bit of customization and experimentation.

Check http://developer.postgresql.org/docs...up-online.html

And we would be very eager to know your experience..:-)

HTH

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Glen Eustace]

On Wed, 2004-10-06 at 20:03, Shridhar Daithankar wrote:

Well, you could start off with PITR capabilities of 8.0. But you may need to
do quite a bit of customization and experimentation.

Check http://developer.postgresql.org/docs...up-online.html

And we would be very eager to know your experience..:-)

Having read this, it would seem I might be jumping the gun a bit. The
method given could be used to copy a running database to another system
but I'm not sure how it would work if the mirrored database is required
to be online at the same time.

I suppose what I was looking for was a utility that I could use to pick
up the WAL segments from one server and then immediately 'run' them
against the database on the remote db (online), if not in real time
certainly not too far behind. It would appear that such a utility
doesn't exist yet.

I am rather committed with other activities at the moment and my
knowledge of the pg internals isn't upto speed but I may have a look at
how this might work in the not too distant future.

If anyone else has already headed down this road, I'd be happy to talk
with them.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: [phone], Mob: [phone], Web: www.godzone.net.nz

"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Glen Eustace <ge******@godzone.net.nz> writes:

Having read this, it would seem I might be jumping the gun a bit. The
method given could be used to copy a running database to another system
but I'm not sure how it would work if the mirrored database is required
to be online at the same time.

It wouldn't. You can have a "hot spare" database via log shipping, but
there is zero chance of allowing the backup to run any independent
transactions. (If it did, its xact counter and pg_clog would get out of
sync with the master, causing all subsequently copied data to be
invalid. AFAICS, even read-only transactions would be problematic.)

Not sure if you are familiar with the Slony project, but that is
probably your best bet for data propagation to a live slave at present.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Glen Eustace]

On Thu, 2004-10-07 at 03:06, Tom Lane wrote:

Not sure if you are familiar with the Slony project, but that is
probably your best bet for data propagation to a live slave at present

Hi Tom,

Thanks for the comment. I was aware of Slony but didn't want the
mirroring to require addition of triggers etc. if it could be avoided.

I had come across a system in Ingres, and I know Ingres isn't PostgreSQL
but it was still a cool idea.

The logging system in Ingres produces journal files which are then
available for roll-forward recovery, but there is a tool called dbaudit
( I think ) which allows one to determine what the changes where. From
this info, ordinary SQL can be generated that one can then use to do
other things. In this case the data was used to generate a who changed
what when report that was feed back into the database into an audit
table.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster