Re: [GENERAL] Replication and PITR

2006-09-29 Thread Bo Lorentsen
Andrew Sullivan wrote: Note that, the last time I looked at it, there was no interlock to ensure that your statement queue (which is basically just a log of statements as executed on the "master") was not accidentally blown away by your cleanup process before your target replicas were up to date.

Re: [GENERAL] Replication and PITR

2006-09-29 Thread Bo Lorentsen
Robert Treat wrote: Hmm almost sounds like what you really want is mammoth replicator... lower level than slony, built into the db, can handle ddl (iirc) not oss though. Yes, that may be true but I think I will try out Slony first, as the design of the DB (DDL) is quite static.

Re: [GENERAL] Replication and PITR

2006-09-29 Thread Bo Lorentsen
Jim Nasby wrote: You can work around it right now, too; you just need an external process that will find the active WAL file and periodically copy it to the backup. I'm pretty sure there's info in the archives about the details of setting this up, and there's also the PITRHA project on pgFound

Re: [GENERAL] Replication and PITR

2006-09-29 Thread Bo Lorentsen
Jeff Davis wrote: If it's a statement queue, what happens when you do "INSERT ... VALUES (random())"? Can the statements be executed out of order on the slave or are they serialized? That is very relevant, and my ref to MySQL replication was only the relatively ease of its setup. And in most

Re: [GENERAL] Replication and PITR

2006-09-27 Thread Robert Treat
On Monday 25 September 2006 07:48, Bo Lorentsen wrote: > Jeff Davis wrote: > > Standby mode means that the database is kept almost up to date with the > > master, but is not "up". When the master goes down, you can bring the > > standby machine up. Until then, you unfortunately can't even do read >

Re: [GENERAL] Replication and PITR

2006-09-26 Thread Jim Nasby
On Sep 22, 2006, at 1:34 PM, Jeff Davis wrote: On Fri, 2006-09-22 at 07:47 +0200, Bo Lorentsen wrote: Bill Moran wrote: - No reliability. On slow days, WAL logs could take a long time to rotate, so small but important transactions might not be replicated for a long time. So it is all r

Re: [GENERAL] Replication and PITR

2006-09-26 Thread Jeff Davis
On Tue, 2006-09-26 at 08:21 +0200, Bo Lorentsen wrote: > MySQL only takes care of the replication, not the failover ... but it > seems like they have some kind of statement queue (no trigger setup) and > a transfer protocol all integrated in the server, and that makes it > "simpel". There is no

Re: [GENERAL] Replication and PITR

2006-09-26 Thread Andrew Sullivan
On Tue, Sep 26, 2006 at 08:21:44AM +0200, Bo Lorentsen wrote: > seems like they have some kind of statement queue (no trigger setup) and > a transfer protocol all integrated in the server, and that makes it > "simpel". There is no understanding regarding transactions, as far as I > have seen. N

Re: [GENERAL] Replication and PITR

2006-09-26 Thread Martijn van Oosterhout
On Tue, Sep 26, 2006 at 11:00:56AM +0200, Csaba Nagy wrote: > Of course there might be other problems too. Another thing would be that the read-only transaction still needs a snapshot, and whatever transaction ID it uses will have been used by the server also. I think the visibility issue may be

Re: [GENERAL] Replication and PITR

2006-09-26 Thread Csaba Nagy
> I would think that the data pages are written and consistent while in > recovery mode, so maybe it's reasonable to do. However, I'm only > speculating and anything like this would probably not be coming soon. I was thinking at one point about what problems could prevent the standby to allow read

Re: [GENERAL] Replication and PITR

2006-09-26 Thread Andrew Sullivan
On Fri, Sep 22, 2006 at 07:58:41AM +0200, Bo Lorentsen wrote: > Interesting note ... do you know how fare PG would be from being able to > be in "read-only" state when receiving PITR data ? Is it a complex > problem or a simple one to solve ? I don't know that it's even possible. The PITR repli

Re: [GENERAL] Replication and PITR

2006-09-25 Thread Bo Lorentsen
Jeff Davis wrote: I don't know for sure, but I would guess not any time soon. A PITR standby works by operating in recovery mode while it's waiting for the WAL files to arrive. When you bring the database up, you're telling it there are no more files to wait for, and to finish recovering and star

Re: [GENERAL] Replication and PITR

2006-09-25 Thread Jeff Davis
On Mon, 2006-09-25 at 13:48 +0200, Bo Lorentsen wrote: > Jeff Davis wrote: > > Standby mode means that the database is kept almost up to date with the > > master, but is not "up". When the master goes down, you can bring the > > standby machine up. Until then, you unfortunately can't even do read >

Re: [GENERAL] Replication and PITR

2006-09-25 Thread Bo Lorentsen
Jeff Davis wrote: Standby mode means that the database is kept almost up to date with the master, but is not "up". When the master goes down, you can bring the standby machine up. Until then, you unfortunately can't even do read queries on that machine. Do you know if this will change in the

Re: [GENERAL] Replication and PITR

2006-09-25 Thread Bo Lorentsen
Jeff Davis wrote: 8.2 will fix this. You can send the WALs periodically even if they're not full. In general, PITR will be substantially improved in 8.2 (thanks Simon!). This sounds very nice, and this will make PG an even more reliable tool. The beta should be out soon enough. Download it (

Re: [GENERAL] Replication and PITR

2006-09-22 Thread Jeff Davis
On Fri, 2006-09-22 at 07:47 +0200, Bo Lorentsen wrote: > Bill Moran wrote: > > - No reliability. On slow days, WAL logs could take a long time to > > rotate, so small but important transactions might not be replicated > > for a long time. > > > So it is all right for backup but for replicat

Re: [GENERAL] Replication and PITR

2006-09-22 Thread Jeff Davis
On Fri, 2006-09-22 at 08:12 +0200, Bo Lorentsen wrote: > Jeff Davis wrote: > > 8.2 makes PITR much easier to use for the situation you'd like. In 8.1, > > a WAL might sit around for a while before it becomes full and then sent > > off. 8.2 allows you to force a WAL to be sent, and it also allows a

Re: [GENERAL] Replication and PITR

2006-09-22 Thread Bo Lorentsen
Jeff Davis wrote: 8.2 makes PITR much easier to use for the situation you'd like. In 8.1, a WAL might sit around for a while before it becomes full and then sent off. 8.2 allows you to force a WAL to be sent, and it also allows a standby mode. This sounds really neat ! To me this sound like

Re: [GENERAL] Replication and PITR

2006-09-22 Thread Bo Lorentsen
Chander Ganesan wrote: Keep in mind that while Slony-I provides you with a read-only replica (able to service queries). At present WAL log replication (in 8.2 or otherwise) would allow you to have a 'warm standby' type database - which would be somewhat in sync (pending the latest transactions

Re: [GENERAL] Replication and PITR

2006-09-22 Thread Bo Lorentsen
Bill Moran wrote: - No reliability. On slow days, WAL logs could take a long time to rotate, so small but important transactions might not be replicated for a long time. So it is all right for backup but for replication it could end up laking too much behind, and a fail over could be hou

Re: [GENERAL] Replication and PITR

2006-09-22 Thread Bo Lorentsen
Csaba Nagy wrote: That's gone with 8.2, it will be possible to stream the last modifications, or force a WAL recycle periodically, whatever fits you better. There is some new infrastructure which allows these things, although I didn't have the time to play with them. This sound very nice, wher

Re: [GENERAL] Replication and PITR

2006-09-21 Thread Chander Ganesan
Csaba Nagy wrote: Cons: - No reliability. On slow days, WAL logs could take a long time to rotate, so small but important transactions might not be replicated for a long time. That's gone with 8.2, it will be possible to stream the last modificatio

Re: [GENERAL] Replication and PITR

2006-09-21 Thread Jeff Davis
On Thu, 2006-09-21 at 17:30 +0200, Bo Lorentsen wrote: > Hi ... > > I have been trying to find a replication to a payment system at the > company I work, and Slony-I is of cause the first thing that game into > my attention. But when reading chapter 23.3 in the PG manual, there is > this commen

Re: [GENERAL] Replication and PITR

2006-09-21 Thread Bill Moran
In response to Csaba Nagy <[EMAIL PROTECTED]>: > > > Cons: > > - No reliability. On slow days, WAL logs could take a long time to > > rotate, so small but important transactions might not be replicated > > for a long time. > > That's gone with 8.2, it will be possible to stream the last > mo

Re: [GENERAL] Replication and PITR

2006-09-21 Thread Csaba Nagy
> > Cons: > - No reliability. On slow days, WAL logs could take a long time to > rotate, so small but important transactions might not be replicated > for a long time. That's gone with 8.2, it will be possible to stream the last modifications, or force a WAL recycle periodically, whatever fit

Re: [GENERAL] Replication and PITR

2006-09-21 Thread Bill Moran
In response to Bo Lorentsen <[EMAIL PROTECTED]>: > Hi ... > > I have been trying to find a replication to a payment system at the > company I work, and Slony-I is of cause the first thing that game into > my attention. But when reading chapter 23.3 in the PG manual, there is > this comment of

[GENERAL] Replication and PITR

2006-09-21 Thread Bo Lorentsen
Hi ... I have been trying to find a replication to a payment system at the company I work, and Slony-I is of cause the first thing that game into my attention. But when reading chapter 23.3 in the PG manual, there is this comment of PITR used as a replication tool. I also saw the "pgpitrha"