Re: Sync Rep: First Thoughts on Code

On Thu, 2008-12-11 at 09:44 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > When the WAL starts streaming the *primary* can immediately perform
> > synchronous replication, i.e. commit waits for transfer.
>
> Until the standby has obtained all the missing log files, it's not
> up-to-date, and there's no guarantee that it can finish the replay. For
> example, imagine that your archive_command is an scp from the primary to
> the standby. If a lightning strikes the primary before some WAL file has
> been copied over to the archive directory in the standby, the standby
> can't catch up. In the primary then, what's the point for a commit to
> wait for transfer, if the reply from the standby doesn't guarantee that
> the transaction is safe in the standby?

The WAL files will have already left the primary.

Timeline is this in my understanding
1 [Primary] Set up continuous archiving
2 [Primary] Take base backup
3 [Standby] Connect to primary to initiate streaming
4 [Primary] Log switch and, optionally, turn off archiving
5 [Standby] Begin replaying files, initially from archive
6 [Standby] Switch to replaying WAL records immediately after streaming

So sync rep would turn on after step 4, so that all intermediate WAL
files have been sent to the archive. If we lose the Primary after this
point then all transactions are accessible to standby. If we lose the
Standby or Archive, then we need to replace them and re-run the above.

The above was outlined on thread "Synchronous Log Shipping Replication"
and pretty much all agreed on 18 Sep.

Recent changes I have requested in the architecture are:
* making archiving optional on primary, so we don't need to send WAL
data *twice*.
* allowing streaming/startup process to work together via shared memory,
to reduce average replication delay and improve performance
* skip archiving/de-archiving step on standby because it's superfluous
(all on this thread)

All of those are fairly minor code changes, but reduce complexity of
solution and significantly reduce the amount of copying of WAL files (3
copy actions to/from archive removed without loss of robustness). I
would have made the suggestions earlier but it wasn't until I saw the
architecture diagrams that I understood the intention of the code.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support