On 10/3/2015 6:55 AM, Michael Paquier wrote:
On Sat, Oct 3, 2015 at 10:20 PM, Madovsky <infos@xxxxxxxxxxxx> wrote:
On 10/3/2015 4:48 AM, Michael Paquier wrote:
On Sat, Oct 3, 2015 at 8:09 PM, Madovsky <infos@xxxxxxxxxxxx> wrote:
I would like to fix a issue I'm facing of with the version 9.4 streaming
replication.
is it possible to set on the fly the synchronous commit on the master (or
standby?)
which only sync commit the hot standby node used by the client who has a
read only sql session on?
By referring to the docs:
http://www.postgresql.org/docs/devel/static/warm-standby.html#SYNCHRONOUS-REPLICATION
Synchronous replication gives the insurance that a transaction has
been flushed to the disk of the standby which is in sync, aka the one
with the lowest priority depending on the nodes currently connected.
This does not ensure that the transaction has been *replayed* on the
standby. You are sure that the transaction data is available. Hence if
you wish to know that a transaction in a standby is running a
transaction with enough data replayed, you should make the WAL
position of the master necessary for the transaction of the standby
something that your application is aware of.
I really well understood Michael thanks,
the docs doesn't cover if the sync priorities can be changed
so one node can be considered fully sync and the other only async
thus to minimize sync request overhead...
The amount of overhead of a node is something that needs to be
evaluated externally of the Postgres backend, then you could always
adjust synchronous_standby_names to change the priorities as you wish.
You can for example do so with libpq or psql using ALTER SYSTEM
combined with "SELECT pg_reload_conf();". The configuration will be be
reloaded at the next query loop in a backup once it catches the
changes of the parameter via SIGHUP.
usually a client connect to a node would like to see the results
on the node where he has a session on.
I just wanted to avoid a SELECT request to the master and
stay on the HOT STANDBY for all read requests.
my script open 2 session, on on the master and one on the hot standby
in case of block transactions.
Requesting the master would be necessary, still I don't really get why
you don't want to query the master for read queries... You could for
example plug on top of the master pgbouncer if you have many
connections, but well at this stage I have no idea of what is your use
case.
Your idea is interesting, but unfortunately not dynamic and not for a
per user basis.
like we can change synchronous_commit on the fly and per block
transactions so
why not the same for standby priority?
I'm trying to use the master for write only.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
