Re: Synchronous Replication Timeout

> From: Teresa Bradbury <TB(at)quintessencelabs(dot)com>
>To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
>Sent: Friday, 28 November 2014, 2:24
>Subject: [GENERAL] Synchronous Replication Timeout
>
>
>Hi,
>
>I have a replication setup with a master and a single synchronous slave. If the slave dies (or the network goes down) I would like any transaction on the master that requires writing to fail so I can roll it back. At the moment, when I commit it just hangs forever or (if I cancel it using ^C in psql or using kill) it commits locally and not on the synchronous slave. Neither of these options are ok in my use case. I have tried setting statement_timeout but it does not work. So my questions are:
>
>1) Is it possible to rollback transactions that fail to commit after a certain amount of time waiting for the slave?
>
>2) If not, is there any intension of implementing such a feature in the near future?
>
>3) Do any of the answers above change if we are dealing with two-phase commits instead? At the moment it hangs forever on ‘prepare transaction’, ‘commit prepared’ and ‘rollback prepared’ commands.
>
>Thanks,
>
>Tessa
>

>

I don't think this is possible; my understanding (which may or may not be correct) is that PostgreSQL's synchronous replication works by shipping/streaming the WAL records to the standby, which then applies the changes to it's own WAL. AFAIK The commit has to happen on the master first, and the master is just blocking and waiting for the standby to confirm that it has reached the position in the XLOG and applied that commit.

I think the recommended method might be to have another standby, and specify it in synchronous_standby_names so it can take over as the synchronous standby when the first standby disconnects/fails.