Re: [GENERAL] Replication and PITR
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. This might have improved recently, but when I looked at it MySQL's async replication was high on the "ease of use" and low on the "works in sticky situations". As I say, they may have fixed it; but I advise people to look very carefully at how it works before deciding it is adequate. I know the MySQL scheme is not perfect, but the setup of one is relatively easy, but you still have to know what is going on, otherwise you are not going to get a good night sleep :-) The important thing to remember about database replicas is that you're _already_ planning for the small percentage of cases where things break. Therefore, an 80/20 solution is not good enough: the thing has to work when most things have broken, or it's no use to you. I agree, and that is why you have to be very careful about your choice :-) Well the nice thing about using a slave DB for reporting is the focus to keep it in sync. If it is a backup server you may ignore it for a while, and then Murphy strikes at you :-) No. I suggest you have a look at the docs, and take these questions to the (again functioning) Slony list, where people can advise about that. Thanks, I willl ! /BL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replication and PITR
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. I dislike getting locked up to some non OSS version at the moment. But thanks for the advise. /BL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replication and PITR
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 pgFoundry. I have seen the PITRHA project, but it looked a bit to much like a hack to me. By doing to much hacking I just end up locking me into a fixed PG version, and that I don't like the sound of. /BL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Replication and PITR
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 of the situation it works OK, but it has its limits and is not to be trusted 100% (I had to make special test records to see if the queue was stock). I Just imagined PITR data used instead of SQL update queues, for replication. The updates are queued on the master and transferred over the network to the slave. You don't need to do any nfs tricks. Ok, nice ... as long as Slony don't write local files there is no problem. I really have to start reading about Slony, to understand it better ... I may get surprised :-) Slony is designed to improve read performance. If you want better write performance pretty much all you can do is use a better I/O system or partition the data so that all the data is not stored on every server. Classic for databases :-) Often, read queries are the bottleneck, so that's why so many people find replication like slony useful. Yes and that goes for me too. /BL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Replication and PITR
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
> > queries on that machine.
>
> Do you know if this will change in the future ?
>
> > If you want more of a master/slave setup for performance, you should
> > take a second look at Slony. A PITR standby doesn't help you with
> > performance at all.
>
> Ok, I can see that ... so PITR is for a standby backup DB, with at the
> best ... manual fail over ?
>
> > Why don't you like the fact that Slony is trigger-based? Does that cause
> > you a problem?
>
> Hmm, well i guess i dislike the idea of having a high level mechanism to
> collect data, not a rational argument, sorry. The PITR just seemed so
> right as it has a more prober low level approach, but it sound to me
> like Slony is the only real choice at the moment, and it will do the job
> with a relatively low overhead.
>
> > And missing DDL is mainly a problem when you want to provide postgresql
> > to many people, and you have no idea how they will use it. If that's the
> > case, standby PITR might be a better solution for you. Slony has nice
> > "execute script" functionality that is useful for making DDL changes on
> > all machines.
>
> Ok, I think that the only thing I really need to do is to try to work
> more with Slony and learn to understand it. And the DDL problem is more
> when others need to maintain the system, and I then have to explain how
> to do this and that, and I think I am a bit spoiled by the easy working
> of the mysql replication :-)
>
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.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication and PITR
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 right for backup but for replication it could end up laking too much behind, and a fail over could be hours behind. So PITR can be used, but one of the cons is the unpredictable delay of data. I thought one of the ideas behind the PITR system was to get rather reason backups of data ... 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!). 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 pgFoundry. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Replication and PITR
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 understanding regarding transactions, as far as I > have seen. 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? > > As I understand it, Slony does batch updates on the slaves, which would > > be better performance than re-executing every transaction. > > > That makes sense ... then the only thing to worry about is where these > "baches" are written. On the same disk as the master database or on the > client side, or will it be advisable to use a NFS mount between these to > machines to balance the disk writing ? > The updates are queued on the master and transferred over the network to the slave. You don't need to do any nfs tricks. Slony is designed to improve read performance. If you want better write performance pretty much all you can do is use a better I/O system or partition the data so that all the data is not stored on every server. Often, read queries are the bottleneck, so that's why so many people find replication like slony useful. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replication and PITR
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. 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. This might have improved recently, but when I looked at it MySQL's async replication was high on the "ease of use" and low on the "works in sticky situations". As I say, they may have fixed it; but I advise people to look very carefully at how it works before deciding it is adequate. The important thing to remember about database replicas is that you're _already_ planning for the small percentage of cases where things break. Therefore, an 80/20 solution is not good enough: the thing has to work when most things have broken, or it's no use to you. > That makes sense ... then the only thing to worry about is where these > "baches" are written. On the same disk as the master database or on the > client side, or will it be advisable to use a NFS mount between these to > machines to balance the disk writing ? No. I suggest you have a look at the docs, and take these questions to the (again functioning) Slony list, where people can advise about that. The short answer is that the things to write are stored in the origin for the table (don't think of it as a database replica, because you can have different tables originating in different nodes). You can _also_ write sets out to disk, if you like. Someone (my colleagues, in fact) appear to have a nasty bug in that functionality that they can't nail down; nobody else has reproduced it. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication and PITR
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 solvable as long as the transaction ID on the slave doesn't pass the VACUUM horizon of the server. But it would require careful studying of the WAL write order to be confident it would actually work. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Replication and PITR
> 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 only access, and got to the conclusion that it would be difficult to assure consistency. Think about it: you start a read only transaction on the standby, but if the standby continues to apply changes from the master, it is entirely possible it will rewrite/delete some pages which contain data which your read only transaction should be able to see, but it won't see it. So to make the read only transactions consistent, the standby would be required to stop applying changes which are newer than the oldest read only transaction running. That would mean you will have a backlog of WAL records to apply at least as big as the age of your oldest running read only transaction, therefore the data will be delayed the same compared to the master, which might be OK for some scenarios... Of course there might be other problems too. Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Replication and PITR
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 replica is essentially a node in permanent crash-recovery mode until it's caught up. Think of Oracle's comparable product -- you can't read from those replicas either. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
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 start up. I have no idea how difficult it would be to try to allow read queries while in recovery mode. In recovery mode, I don't think you can create new backends. 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. Ok, but this gives me a clear picture of what I am able to do at the moment, and no matter what I think, Slony is the replication method I will be using and PITR is nice for backup, as it is designed for. Since we're talking about async replication, a failover is the process that could result in lost transactions. That's the reason the database can't make the decision to fail over automatically. Ok, makes sense, it has to be some external logic that makes this failover happened, and that logic must be related to whatever system the database is supporting. Sometimes "easy working" means that it's not doing what you think it's doing. Replication is complicated and heavily dependent on what your business needs it for, and what should be done in the case of failure. There are no perfect answers to those questions, and if MySQL is making the decisions for you maybe it's making choices wrong for your business. 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 understanding regarding transactions, as far as I have seen. Disclaimer: I don't know much about MySQL's replication. That is ok. As I understand it, Slony does batch updates on the slaves, which would be better performance than re-executing every transaction. That makes sense ... then the only thing to worry about is where these "baches" are written. On the same disk as the master database or on the client side, or will it be advisable to use a NFS mount between these to machines to balance the disk writing ? Thanks for your valuable answers ! /BL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Replication and PITR
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 > > queries on that machine. > > > Do you know if this will change in the future ? 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 start up. I have no idea how difficult it would be to try to allow read queries while in recovery mode. In recovery mode, I don't think you can create new backends. 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. > > If you want more of a master/slave setup for performance, you should > > take a second look at Slony. A PITR standby doesn't help you with > > performance at all. > > > Ok, I can see that ... so PITR is for a standby backup DB, with at the > best ... manual fail over ? There's no reason it can't be automated. But the database doesn't know when you want to fail over, so you just need to tell it. In 8.1, you can have restore_command return a non-zero exit status and that will stop the recovery mode and start up the backup database. Then have some scripts redirect the traffic from the other database to the backup database. Since we're talking about async replication, a failover is the process that could result in lost transactions. That's the reason the database can't make the decision to fail over automatically. > > And missing DDL is mainly a problem when you want to provide postgresql > > to many people, and you have no idea how they will use it. If that's the > > case, standby PITR might be a better solution for you. Slony has nice > > "execute script" functionality that is useful for making DDL changes on > > all machines. > > > Ok, I think that the only thing I really need to do is to try to work > more with Slony and learn to understand it. And the DDL problem is more > when others need to maintain the system, and I then have to explain how > to do this and that, and I think I am a bit spoiled by the easy working > of the mysql replication :-) Sometimes "easy working" means that it's not doing what you think it's doing. Replication is complicated and heavily dependent on what your business needs it for, and what should be done in the case of failure. There are no perfect answers to those questions, and if MySQL is making the decisions for you maybe it's making choices wrong for your business. Disclaimer: I don't know much about MySQL's replication. > > Asynchronous does have very good performance. > > > So, Slony also do some queuing to gain low overhead ? As I understand it, Slony does batch updates on the slaves, which would be better performance than re-executing every transaction. Regards, Jeff Davis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Replication and PITR
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 future ? If you want more of a master/slave setup for performance, you should take a second look at Slony. A PITR standby doesn't help you with performance at all. Ok, I can see that ... so PITR is for a standby backup DB, with at the best ... manual fail over ? Why don't you like the fact that Slony is trigger-based? Does that cause you a problem? Hmm, well i guess i dislike the idea of having a high level mechanism to collect data, not a rational argument, sorry. The PITR just seemed so right as it has a more prober low level approach, but it sound to me like Slony is the only real choice at the moment, and it will do the job with a relatively low overhead. And missing DDL is mainly a problem when you want to provide postgresql to many people, and you have no idea how they will use it. If that's the case, standby PITR might be a better solution for you. Slony has nice "execute script" functionality that is useful for making DDL changes on all machines. Ok, I think that the only thing I really need to do is to try to work more with Slony and learn to understand it. And the DDL problem is more when others need to maintain the system, and I then have to explain how to do this and that, and I think I am a bit spoiled by the easy working of the mysql replication :-) Asynchronous does have very good performance. So, Slony also do some queuing to gain low overhead ? I prefer working with async when possible because it's easier to do well. I was just making sure you knew that it is possible to lose transactions. Thanks, one never know :-) By the way, no matter what you do, you probably do want to use the PITR to at least do backups for you. It won't help to use replication if someone accidentally does an unqualified "DELETE FROM mytable". Yeps, a master and and slave with Slony replication and backup from the slave database, that seems to be the plan at the moment. /BL ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Replication and PITR
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 (or the CVS) and try it out. It never hurts to actually simulate a failure and see how quickly and effectively you actually can recover. That is especially true in 8.1, where PITR is still somewhat rough around the edges. Yes, this is a good idea, and I will try it some time. /BL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
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 replication it could end up laking > too much behind, and a fail over could be hours behind. > > So PITR can be used, but one of the cons is the unpredictable delay of > data. I thought one of the ideas behind the PITR system was to get > rather reason backups of data ... > 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!). The beta should be out soon enough. Download it (or the CVS) and try it out. It never hurts to actually simulate a failure and see how quickly and effectively you actually can recover. That is especially true in 8.1, where PITR is still somewhat rough around the edges. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
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 > > standby mode. > > > This sounds really neat ! > > To me this sound like we may be able to replicate using different > priorities, balancing performance and data integrity. But what do you > mean by "standby mode" ? That PG maý be able to serve as a readonly DB > and replication client at the same time ? 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. If you want more of a master/slave setup for performance, you should take a second look at Slony. A PITR standby doesn't help you with performance at all. > > Slony is a good system now, and it's nice because you can use different > > versions of PostgreSQL. PITR requires that it's the same version. > > > The PITR requirement all makes sense, and I don't like the missing DDL > updates and the fact that Slony is trigger. Why don't you like the fact that Slony is trigger-based? Does that cause you a problem? And missing DDL is mainly a problem when you want to provide postgresql to many people, and you have no idea how they will use it. If that's the case, standby PITR might be a better solution for you. Slony has nice "execute script" functionality that is useful for making DDL changes on all machines. > > However, if you're working with a payment system or accounting system, > > you may need synchronous replication. With any asynchronous solution > > (Slony or PITR standby), there is a possibility (although not likely) to > > lose *committed* transactions. > > > Hmm, yes but again this have to be balanced with performance, and at the > moment I can live with a replication that is a little behind but, I will > try to keep the overhead down. Asynchronous does have very good performance. > > If you do need synchronous replication, consider using two-phase commit > > to prepare transactions on several machines before committing them. This > > ensures that the data will be on multiple machines before committing to > > any of them. > > > Hmm, I saw that feature announced in 8.1, and I am sure it will come in > handy one day, but right now async is acceptable. > I prefer working with async when possible because it's easier to do well. I was just making sure you knew that it is possible to lose transactions. By the way, no matter what you do, you probably do want to use the PITR to at least do backups for you. It won't help to use replication if someone accidentally does an unqualified "DELETE FROM mytable". Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Replication and PITR
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 we may be able to replicate using different priorities, balancing performance and data integrity. But what do you mean by "standby mode" ? That PG maý be able to serve as a readonly DB and replication client at the same time ? Slony is a good system now, and it's nice because you can use different versions of PostgreSQL. PITR requires that it's the same version. The PITR requirement all makes sense, and I don't like the missing DDL updates and the fact that Slony is trigger. However, if you're working with a payment system or accounting system, you may need synchronous replication. With any asynchronous solution (Slony or PITR standby), there is a possibility (although not likely) to lose *committed* transactions. Hmm, yes but again this have to be balanced with performance, and at the moment I can live with a replication that is a little behind but, I will try to keep the overhead down. If you do need synchronous replication, consider using two-phase commit to prepare transactions on several machines before committing them. This ensures that the data will be on multiple machines before committing to any of them. Hmm, I saw that feature announced in 8.1, and I am sure it will come in handy one day, but right now async is acceptable. /BL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Replication and PITR
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), but would be unable to service queries (essentially, you'd have a server that was sitting with postmaster in a recovery state). 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 ? /BL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Replication and PITR
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 hours behind. So PITR can be used, but one of the cons is the unpredictable delay of data. I thought one of the ideas behind the PITR system was to get rather reason backups of data ... /BL ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Replication and PITR
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, where can I find more info about this ... and when is 8.2 expected to be released ? And are there anyone else that plays with making build in replication for PG 8.2 ? The big improvement would be indeed to have the infrastructure to start up a standby by simply pointing it to the master server, no other setup needed. Implement that, make it reliable, and any beginner to postgres will be able to easily set up a WAL shipping based standby. Right now you still have to do some complicated scripting to make it work (no idea how much 8.2 will help here, didn't try yet). Do you know what the motivation for these changes have been ? Better PITR or is replication a direct goal ? /BL ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Replication and PITR
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 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. 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), but would be unable to service queries (essentially, you'd have a server that was sitting with postmaster in a recovery state). Chander Ganesan Open Technology Group, Inc. - Expert PostgreSQL Training One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com The big improvement would be indeed to have the infrastructure to start up a standby by simply pointing it to the master server, no other setup needed. Implement that, make it reliable, and any beginner to postgres will be able to easily set up a WAL shipping based standby. Right now you still have to do some complicated scripting to make it work (no idea how much 8.2 will help here, didn't try yet). Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
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 comment of PITR used as a replication tool. > > I also saw the "pgpitrha" project, and this sounds really nice too, but > is this a good way to go ? Will PITR be more replication friendly > in the future or even form the basis for a future buildin async > replication form ? > 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. Slony is a good system now, and it's nice because you can use different versions of PostgreSQL. PITR requires that it's the same version. However, if you're working with a payment system or accounting system, you may need synchronous replication. With any asynchronous solution (Slony or PITR standby), there is a possibility (although not likely) to lose *committed* transactions. If you do need synchronous replication, consider using two-phase commit to prepare transactions on several machines before committing them. This ensures that the data will be on multiple machines before committing to any of them. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
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 > 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. > > The big improvement would be indeed to have the infrastructure to start > up a standby by simply pointing it to the master server, no other setup > needed. Implement that, make it reliable, and any beginner to postgres > will be able to easily set up a WAL shipping based standby. Right now > you still have to do some complicated scripting to make it work (no idea > how much 8.2 will help here, didn't try yet). That will be some neat stuff. I didn't know that was coming up. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication and PITR
> > 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 fits you better. There is some new infrastructure which allows these things, although I didn't have the time to play with them. The big improvement would be indeed to have the infrastructure to start up a standby by simply pointing it to the master server, no other setup needed. Implement that, make it reliable, and any beginner to postgres will be able to easily set up a WAL shipping based standby. Right now you still have to do some complicated scripting to make it work (no idea how much 8.2 will help here, didn't try yet). Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
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 PITR used as a replication tool. > > I also saw the "pgpitrha" project, and this sounds really nice too, but > is this a good way to go ? Will PITR be more replication friendly > in the future or even form the basis for a future buildin async > replication form ? > > I may be naive, but to me it sound like we/I only need some kind of > protocol (or API in postgres) to move PITR data from one server to > another, and we could end up with a nice async replication system. > > pros : > - DDL replications > - low overhead > - no trickers > > Cons: > - binary alike master slave - 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. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Replication and PITR
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" project, and this sounds really nice too, but is this a good way to go ? Will PITR be more replication friendly in the future or even form the basis for a future buildin async replication form ? I may be naive, but to me it sound like we/I only need some kind of protocol (or API in postgres) to move PITR data from one server to another, and we could end up with a nice async replication system. pros : - DDL replications - low overhead - no trickers Cons: - binary alike master slave Is this possible, or is it possible to write a module for PG that provide this kind of transportation, or have I been spending too much time together with MySQL :-) Regards /BL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
