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

Hi,

I have configured PG master and slave to run under synchronous replication mode and they are mostly working fine.
Exceptduring the setup phase. Please read thru my setup, procedure and let me know if I am doing something stupid. 


PG master :
  wal_level = hot_standby
  max_wal_senders = 5
  wal_keep_segments = 10
  synchronous_standby_names = 'standby'

PG Slave
  wal_level = hot_standby
  max_wal_senders = 5
  wal_keep_segments = 10
  synchronous_standby_names = ''

PG master has right hba entries for Slave
PG Slave gets fresh backup from PG master using pg_backup utility everytime before it starts up

When PG Master is restarted with above config for synchronous replication (pg_log file shows following happenings ..)
1. starts to accept connections
2. gets notified about standby connected
3. and after some time like 20 - 30 sec, both PG master and PG slave are in good sync replication setup. I noticed the
pg_replication_statusmoving from potential -->async-->sync during this time. 
4. statements are sync replicated

And, Here is the problem during PG Master startup under sync replication setup

-- Many SQL queries (including select) that are executed between (2) and (3) of above are getting stuck totally
-- I am triggering DB checkpoint once in 5 seconds during the Master startup phase so that it can get into sync
replicationsetup with slave sooner 


Questions:

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. 
B. How do I make these statements timeout faster than stuck forever. statement_timeout config param is not helping
here.

any help is much appreciated.

thanks,
Manoj

 Hi,

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

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
masterand 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@postgresql.org [pgsql-admin-owner@postgresql.org] on behalf of Gabriele Bartolini
[Gabriele.Bartolini@2ndQuadrant.it]
Sent: Wednesday, June 06, 2012 8:51 AM
To: Manoj Govindassamy
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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@nimblestorage.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.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin