Thread: Transaction ordering on log-shipping standby
I've got a question about transaction ordering in a log-shipping replication environment. Here's the setup... - A pair of PostgreSQL 9 servers in active/standby configuration, using log-shipping - A single client, using a single connection - The client commits transaction 1 - The client commits transaction 2 - The active server fails and the standby is promoted to be the active server The client re-establishes the connection and is attempting to determine which transactions have made it onto the standbyserver (and which never made it because of replication delays). Here's the question... If the client can confirm that transaction 2 has happened on the standby, does that imply that transaction 1 has also madeit onto the standby? Or to put the question another way, is the ordering of transactions on the active and standby servers guaranteed to be thesame? Thanks, Andrew
On Tue, Sep 20, 2011 at 13:46, Andrew Rose <andrew.rose@metaswitch.com> wrote: > Or to put the question another way, is the ordering of transactions on the active and standby servers guaranteed to bethe same? Yes. The WAL serializes the order of transactions. It is applied to slaves in the same order that it's written on the master. Regards, Marti
On Tue, Sep 20, 2011 at 11:46 AM, Andrew Rose <andrew.rose@metaswitch.com> wrote: > I've got a question about transaction ordering in a log-shipping replication environment. > > Here's the setup... > > - A pair of PostgreSQL 9 servers in active/standby configuration, using log-shipping > - A single client, using a single connection > - The client commits transaction 1 > - The client commits transaction 2 > - The active server fails and the standby is promoted to be the active server > > The client re-establishes the connection and is attempting to determine which transactions have made it onto the standbyserver (and which never made it because of replication delays). > > Here's the question... > > If the client can confirm that transaction 2 has happened on the standby, does that imply that transaction 1 has also madeit onto the standby? > > Or to put the question another way, is the ordering of transactions on the active and standby servers guaranteed to bethe same? Yes, the transaction ordering is log serializable because that's the only way that provably works. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services