Thread: Mirroring databases in v8
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: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations"
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/postgres/backup-online.html And we would be very eager to know your experience..:-) HTH Shridhar
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/postgres/backup-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: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations"
Glen Eustace <geustace@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
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.