Date: Wed, 31 Aug 2016 11:44:17 -0400
From: Dianne Skoll <dfs@roaringpenguin.com>
To: pgsql-admin@postgresql.org
Subject: What causes streaming replication delay?
Message-ID: <20160831114417.7fc1040d@hydrogen.roaringpenguin.com>
Organization: Roaring Penguin Software Inc.
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi,

I have a problem that's driving me insane.  I have two PostgreSQL 9.1
installations (yes, I know... very old.)  One is configured as a master
and the other is a streaming-replication hot-standby that supports
read-only queries.

Every now and then, although the standby claims to be streaming transactions
if you look with "ps", it seems to stall and the replication delay grows
by one second for each second of elapsed time.

If I restart the hot-standby, it consumes WAL files and very quickly
catches up.  And then a few minutes later, it happens again.

So, what are the possible causes of this behaviour?  There are no
long transactions on the standby, nor on the master.  And how can
I find out exactly what query is causing the problem, assuming
it's a query?

The relevant settings in the hot-standby postgresql.conf are:

hot_standby = on
max_standby_archive_delay = 10s
max_standby_streaming_delay = 10s
hot_standby_feedback = on

and on the master:

wal_level = hot_standby
archive_mode = on
archive_command = (a command to send WAL files to the standby)
max_wal_senders = 8

and all other parameters are at their defaults.

Regards,

Dianne.


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