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

Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup

From: Manoj Govindassamy <manoj(at)nimblestorage(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup
Date: 2012-06-06 14:55:15
Message-ID: ADD354851455FC44A5D8923AD5152DE307A0FA5C@coloex01.nimblestorage.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
Hi,

I have configured PG master and slave to run under synchronous replication mode and they are mostly working fine. Except during 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_status moving 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 replication setup 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


In response to

Responses

pgsql-admin by date

Next:From: Gabriele BartoliniDate: 2012-06-06 15:51:46
Subject: Re: Postgres 9.1 Synchronous Replication and stuck queries during sync repl setup
Previous:From: hari.fuchsDate: 2012-06-06 08:06:25
Subject: Re: Can schemas be ordered regarding their creation time ?

pgsql-general by date

Next:From: Alban HertroysDate: 2012-06-06 15:01:24
Subject: Re: problem after upgrade db missing
Previous:From: Frank LanitzDate: 2012-06-06 14:33:53
Subject: pg_database_size differs from df -s

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