Gabriele, Thanks for the quick reply. > That's not necessary. Usually you do this only the first time you set > it up, then take advantage of the wal_keep_segments on the master and > allow the standby to resync. we are not sure about when the standby will come again and sync with master. also, we have space constraints on the master and can't keep indefinite segments. > This behaviour is perfectly fine. Until the master and the standby are > in sync, But, even after the standby came to sync mode, those statements that are executed between (1) and (2) remain stuck only. they don't complete at all. > Then uncomment the 'synchronous_standby_names' line on the > master and issue a reload. doesn't any change to 'synchronous_standby_names' need a server restart ?? Just a reload config command is sufficient here ? yes, all the servers are in same LAN. Thanks for your time. - manoj ________________________________________ From: pgsql-admin-owner@xxxxxxxxxxxxxx [pgsql-admin-owner@xxxxxxxxxxxxxx] on behalf of Gabriele Bartolini [Gabriele.Bartolini@xxxxxxxxxxxxxx] Sent: Wednesday, June 06, 2012 8:51 AM To: Manoj Govindassamy Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup Hi, On Wed, 6 Jun 2012 14:55:15 +0000, Manoj Govindassamy <manoj@xxxxxxxxxxxxxxxxx> wrote: > PG Slave gets fresh backup from PG master using pg_backup utility > everytime before it starts up That's not necessary. Usually you do this only the first time you set it up, then take advantage of the wal_keep_segments on the master and allow the standby to resync. > A. I need to know why PG master started accepting connections at (1) > and still NOT able to fully commit the transactions. Statements that > are executed after (3) are not seeing this problem. This behaviour is perfectly fine. Until the master and the standby are in sync, given that you set the standby to be the synchronous one, the master MUST wait until the standby writes on disk the transaction information. I suggest that first you remove the standby server from the list of synchronous servers by commenting the 'synchronous_standby_names' line and wait until the standby catches up (asynchronous streaming replication). Then uncomment the 'synchronous_standby_names' line on the master and issue a reload. From that moment on you will have synchronous streaming replication in place. Cheers, Gabriele P.S.: I took it for granted that the two servers are in the same LAN. -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support Gabriele.Bartolini@xxxxxxxxxxxxxx - www.2ndQuadrant.it -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
