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
