From: Dianne Skoll <dfs@roaringpenguin.com>
To: pgsql-admin@postgresql.org
Subject: What causes streaming replication delay?
Date: Wed, 31 Aug 2016 11:44:17 -0400
Message-ID: <20160831114417.7fc1040d@hydrogen.roaringpenguin.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dfs@roaringpenguin.com
Subject: Re: What causes streaming replication delay?
In-Reply-To: <20160831114417.7fc1040d@hydrogen.roaringpenguin.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox