From: Williams, Alex <awilliams@teamdrg.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Question on Hot Standby in PostgreSQL
Date: Fri, 20 Nov 2015 19:50:41 +0000
Message-ID: <D274E3C1.1113E8%awilliams@dresourcesgroup.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi,
I have a question concerning using the Postgres Hot Standby feature. We
are currently using that feature to sync any changes from one master to
one slave. The slave role is as a reporting db server with queries
constantly and concurrently running (some in ms, some in seconds,
some in minutes,)
We have adjusted these two params to allow long queries on the hot standby:
[http://www.postgresql.org/docs/current/static/hot-standby.html]
max_standby_archive_delay = -1 # max delay before canceling queries
max_standby_streaming_delay = -1 # max delay before canceling
queries
And looking at an archived mail question similar to ours:
http://www.postgresql.org/message-id/AANLkTinLg+bpzcjzdndsnGGNFC=D1OsVh+hKb
85A-s=n@mail.gmail.com<mailto:85A-s=n@mail.gmail.com>
response:
http://www.postgresql.org/message-id/201102272005.00234.jens@wilke.org
It looks like WAL updates cannot be applied while queries are running.
I've tested that to be true. In our case, the reporting server has a
good chance of not being synced for hours or possible longer because
of all the queries that are running. Is it possible to run long running
queries while having WAL updates applied to the slave?
I understand the concept of preventing WAL updates not being applied to
slaves while queries are running. However, I thought with the use of MVCC,
an active query on the slave (long running, 30 seconds+) can run reading
from row xid versions/snapshot, while the WAL update is being applied, so
subsequent queries will get the WAL updates when that WAL transaction is
committed. I haven't fully digested the MVCC model used in PostgreSQL yet
[http://www.onlamp.com/pub/a/onlamp/2001/05/25/postgresql_mvcc.html and
https://devcenter.heroku.com/articles/postgresql-concurrency], so this is
just my assumption -- that even if a table is dropped / truncated during a
WAL update, the current running query should still work as it's using a
version/snapshot of the table(s) it's querying?
Is there any way (even with a third party extension) we can sync slaves
from a master and have those updates from the master be applied to the
slave right away while letting queries of any execution time continue to
run till they complete on the standby/slave? If Hot Standby can't do that,
what would you recommend for this situation? Our scenario is that we are
hitting postgres with queries constantly and concurrently running (some in
ms, some in seconds, some in minutes,) leaving almost no time for a WAL
update to be applied.
Any help would be greatly appreciated.
Thanks,
Alex
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: awilliams@teamdrg.com
Subject: Re: Question on Hot Standby in PostgreSQL
In-Reply-To: <D274E3C1.1113E8%awilliams@dresourcesgroup.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