Re: Replication and PITR - Mailing list pgsql-general

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