Postgres Pro
Downloads
Home   >   mailing lists

Thread: Synchronous Replication Timeout

Synchronous Replication Timeout

From
Teresa Bradbury
Date:

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

 

 

Re: Synchronous Replication Timeout

From
Glyn Astill
Date:
> From: Teresa Bradbury <TB@quintessencelabs.com>
>To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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)
Iwould like any transaction on the master that requires writing to fail so I can roll it back. At the moment, when I
commitit just hangs forever or (if I cancel it using ^C in psql or using kill) it commits locally and not on the
synchronousslave. Neither of these options are ok in my use case. I have tried setting statement_timeout but it does
notwork. 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
replicationworks by shipping/streaming the WAL records to the standby, which then applies the changes to it's own WAL.
AFAIKThe commit has to happen on the master first, and the master is just blocking and waiting for the standby to
confirmthat 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
takeover as the synchronous standby when the first standby disconnects/fails.

Re: Synchronous Replication Timeout

From
Sameer Kumar
Date:

On Fri, Nov 28, 2014 at 10:24 AM, Teresa Bradbury <TB@quintessencelabs.com> wrote:

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.


​I did that for a customer and I am using a tool (pgpool) to change the config file if the master is going down. You can keep additional configurations in
synchronous_master.conf and add include header in postgresql.conf

You just need to write a shell script (or use something like pgpool)​ to keep a watch if the slave goes down change synchronous_master.conf to an empty file and reload the postgres config (pg_ctl reload).

 

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?


​No, AFAIK it would have already committed to WAL files on on master.​
 
​It is just blocked till the slave confirms the ​same being done at its end.

 

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.




Best Regards,

Sameer Kumar | Database Consultant

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: +65 8110 0350  T: +65 6438 3504 | www.ashnik.com

icons

 

Email patch

 

This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).

Attachment