chinnaobi wrote:
> I have been testing streaming replication in windows with postgres
9.1.1.
> For few scenario's I haven't found a solution. Please advice me.
>
> 1. Precautions before promoting standby server to primary manually
> considering the dead primary server ??
You don't have to take any precautions; the standby can be promoted
as soon as "consistent recovery state reached" appears in the log.
> 2. How could we ensure the standby has received all transactions sent
by
> primary till the point primary server is dead. (Meaning the dead
primary and
> standby server are exactly same, so that the dead primary comes back
it can
> be turned to standby without any issues).
If the primary is truy dead, there is no way (unless you use synchronous
replication, then it is always the case).
You can use pg_last_xlog_receive_location() on the standby to see the
last
replayed transaction ID and pg_last_xact_replay_timestamp() for
the timestamp.
If the primary is still there, you can use txid_current() to get the
current transaction ID.
> 3. When the dead primary is switchedto standby the streaming is not
> happening due to current_wal_location is ahead in the standby server
is
> ahead of wal_sent_location. In this case how can I start streaming
without
> taking a fresh base backup from current primary ??
The parenthesis in your previous question and this question suggest
that you missed out on one thing:
When the standby is promoted, it starts a new time line, so it
is on a different time line from the old master. The old
primary cannot be turned to a standby without a new base backup
(possibly via rsync to speed up things).
This time line switch is a mechanism that prevents precisely
the problems you are anticipating.
> 4. When the dead primary comes back the DB still accepts data and it
goes to
> out of sync with the current primary and streaming won't start. Is
there any
> solution for this case ??
You should lock out connections to the old primary, perhaps
via pg_hba.conf.
Yours,
Laurenz Albe
