Skip site navigation (1) Skip section navigation (2)

Re: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup

From: Gabriele Bartolini <Gabriele(dot)Bartolini(at)2ndQuadrant(dot)it>
To: Manoj Govindassamy <manoj(at)nimblestorage(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup
Date: 2012-06-06 15:51:46
Message-ID: d629d3e88a253854ba07ae54beda45c4@2ndquadrant.it (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
 Hi,

 On Wed, 6 Jun 2012 14:55:15 +0000, Manoj Govindassamy 
 <manoj(at)nimblestorage(dot)com> 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(dot)Bartolini(at)2ndQuadrant(dot)it - www.2ndQuadrant.it

In response to

Responses

pgsql-admin by date

Next:From: Mike BroersDate: 2012-06-06 15:54:40
Subject: postgres block size alignment with filesystem block size
Previous:From: Manoj GovindassamyDate: 2012-06-06 14:55:15
Subject: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup

pgsql-general by date

Next:From: Frank LanitzDate: 2012-06-06 15:58:28
Subject: Re: pg_database_size differs from df -s
Previous:From: Tom LaneDate: 2012-06-06 15:49:47
Subject: Re: pg_database_size differs from df -s

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group