Hello, Disaster Recovery testing for Synchronous replication setup -
When the standby site is down, transactions at the production site started hanging (this is after the successful setup of synchronous replication). We changed synchronous_commit to 'local' to over-come this situation. - No transactions are hanging at the production site even when the standby is down - Standby is automatically getting synced when it is back up again. Can someone let us know if there are any "-ve" effects of putting synchronous_commit='local' ?? I am assuming that this as good as putting "synchronous_commit=on" on an stand-alone system. We need to get this setup live on production shortly. Thanks VB On Fri, Feb 10, 2012 at 4:47 PM, Venkat Balaji <venkat.bal...@verse.in>wrote: > > This issue stays resolved !!! > > The statements are no more hanging on production now :) > > The suspected problem was - > > Our brand new production server did not have the port 5432 open. > > I had opened the port using "iptables" command and everything started > working. > > synchronous replication is fast and awesome. > > Thanks > VB > > > On Fri, Feb 3, 2012 at 9:45 PM, Adrian Klaver <adrian.kla...@gmail.com>wrote: > >> On Thursday, February 02, 2012 10:21:28 pm Venkat Balaji wrote: >> >> > >> > Connection is working fine between primary and standby, ping is working >> > fine and wal archive file transfer is working without any issues. >> > >> > I tried CREATE TABLE and CREATE DATABASE, both were hanging. >> > >> > Apart from regular streaming replication settings, I did the following >> on >> > primary to enable synchronous replication - >> > >> > synchronous_standby_names='*' >> > >> > Commands started hanging after that. Is there anything else i need to >> do. >> >> From here: >> >> http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html >> >> " >> synchronous_standby_names (string) >> ... The synchronous standby will be the first standby named in this list >> that is >> both currently connected and streaming data in real-time (as shown by a >> state of >> streaming in the pg_stat_replication view). Other standby servers >> appearing >> later in this list represent potential synchronous standbys.... >> >> The name of a standby server for this purpose is the application_name >> setting of >> the standby, as set in the primary_conninfo of the standby's walreceiver. >> There >> is no mechanism to enforce uniqueness. In case of duplicates one of the >> matching >> standbys will be chosen to be the synchronous standby, though exactly >> which one >> is indeterminate. The special entry * matches any application_name, >> including >> the default application name of walreceiver. >> >> " >> >> So I would check the pg_stat_replication view to see if Postgres is >> seeing the >> standby as streaming. >> >> >> > >> > Thanks >> > VB >> >> -- >> Adrian Klaver >> adrian.kla...@gmail.com >> > >
