Re: Replication with 9.4

On Sat, Oct 3, 2015 at 10:20 PM, Madovsky <infos(at)madovsky(dot)org> wrote:
>
>
> On 10/3/2015 4:48 AM, Michael Paquier wrote:
>>
>> On Sat, Oct 3, 2015 at 8:09 PM, Madovsky <infos(at)madovsky(dot)org> 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.
--
Michael