Re: Replication and PITR - Mailing list pgsql-general

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