Re: streaming replication and recovery

On Sat, Apr 5, 2014 at 3:48 AM, Anupama Ramaswamy <anumr_0123(at)yahoo(dot)com> wrote:
> Scenario 1
> ----------------
> Suppose the secondary server is lagging behind the primary at the time of
> primary failure, will the secondary completely catch up to the primary
> state, before stopping replication. Or what in the process ensures this
> happens ?

There are two lag types to consider about in case of a normal
streaming replication - delivery lag and replay lag. The secondary
will completely catch up to what have been delivered, but what have
not been is going to be lost. See [1][2].

> Scenario 2
> ------------------
> Both primary and secondary are running.
> Secondary goes down at time t1.
> Now primary goes down at t2. At this point, the trigger file is created.
> Secondary node restarts at time t3.
> What happens to all the transactions that occurred between t1 and t2.

They are going to be lost in case of a normal streaming replication.

Note that there is an ability to setup so called synchronous
replication [3] that guarantees delivery of changes that were commit
in exchange of some performance. Literally, it wont commit until the
changes are transferred.

So, if you have a mission critical or other high importance data you
should to setup synchronous replication, otherwise, if you can
sacrifice some latest commits, normal streaming replication.

[1] http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW
[2] http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL
[3] http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com