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