Re: Replication with 9.4

On 10/3/2015 6:55 AM, Michael Paquier wrote:
> 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.

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.