Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 4883C1179B44 for ; Wed, 6 Jun 2012 12:52:02 -0300 (ADT) Received: from delta01.pnet.xcon.it ([62.48.53.16]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ScIWf-0003OP-Bs for pgsql-admin@postgresql.org; Wed, 06 Jun 2012 15:52:02 +0000 Received: from localhost (localhost [127.0.0.1]) by delta01.pnet.xcon.it (Postfix) with ESMTP id 0CC0E10336D4D; Wed, 6 Jun 2012 17:51:47 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at delta01.pnet.xcon.it Received: from delta01.pnet.xcon.it ([127.0.0.1]) by localhost (delta01.pnet.xcon.it [127.0.0.1]) (amavisd-new, port 10024) with LMTP id ZVOo5z9In52P; Wed, 6 Jun 2012 17:51:46 +0200 (CEST) Received: from webmail.xcon.it (unknown [10.68.1.35]) (Authenticated sender: gabriele.bartolini@2ndquadrant.it) by delta01.pnet.xcon.it (Postfix) with ESMTPSA id D3F8C10336D87; Wed, 6 Jun 2012 17:51:46 +0200 (CEST) Received: from eggIB-wan.comune.prato.it ([193.43.108.2]) by webmail.xcon.it with HTTP (HTTP/1.1 POST); Wed, 06 Jun 2012 17:51:46 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Date: Wed, 06 Jun 2012 17:51:46 +0200 From: Gabriele Bartolini To: Manoj Govindassamy Cc: Subject: Re: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup In-Reply-To: References: Message-ID: X-Sender: Gabriele.Bartolini@2ndQuadrant.it User-Agent: [X]con Webmail/0.4.2 X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201206/12 X-Sequence-Number: 39603 Hi, On Wed, 6 Jun 2012 14:55:15 +0000, Manoj Govindassamy 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@2ndQuadrant.it - www.2ndQuadrant.it