Re: 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