Thread: setting separate values of replication parameters to each standby to provide more granularity
setting separate values of replication parameters to each standby to provide more granularity
From
Samrat Revagade
Date:
Hi,
How about providing more granularity to replication, by setting separate values of replication parameters to each standby
for example:
standby1.wal_sender_timeout= 50s
standby2.wal_sender_timeout= 40s
The idea is to allow configuration of standby servers such that they have there own set of replication parameters as per requirements.
If they are not specified then configure standby with default settings.
I have already raised this point when I made proposal about introducing the ini file, but there was no feedback on this point (setting separate values to each standby) here is the link for that:
As per discussion use of ini file is not necessary. Currently PostgreSQL provides infrastructure which allows level-2 nesting
( If we need further nesting of parameters we can use Andres patch http://www.postgresql.org/message-id/20130225211533.GD3849@awork2.anarazel.de)
we can do setting as follows:
----------postgresql.conf--------------
st1.wal_sender_timeout = 40s
st2.wal_sender_timeout = 50s
-------------------------------------------
postgres=# show st1.wal_sender_timeout ;
st1.wal_sender_timeout
------------------------
40s
(1 row)
postgres=# show st2.wal_sender_timeout ;
st2.wal_sender_timeout
------------------------
50s
(1 row)
But this just handles the parser stage, we need to write the underlying code which actually configures standby server with corresponding parameter values.
I think that use of postgresql.conf is much better option to do this.
one can easily do argument about the size and complexity of postgresql.conf if it allows such setting.
In that case we can use *include* directive and separate out replication parameters to another sub file.
But after all of this, I think it will be a good change and will provide more granularity to the replication.
Greetings,
Samrat Revagade,
NTT DATA OSS Center Pune, India.
Samrat Revagade,
NTT DATA OSS Center Pune, India.
Re: setting separate values of replication parameters to each standby to provide more granularity
From
Pavan Deolasee
Date:
On Thu, Sep 26, 2013 at 6:00 PM, Samrat Revagade <revagade.samrat@gmail.com> wrote:
Hi,How about providing more granularity to replication, by setting separate values of replication parameters to each standbyfor example:standby1.wal_sender_timeout= 50sstandby2.wal_sender_timeout= 40sThe idea is to allow configuration of standby servers such that they have there own set of replication parameters as per requirements.
How does this interplay with the synchronous_standby_names parameter ? Or do you think that becomes irrelevant if we do like what you are suggesting above ? AFAIK synchronous_standby_names was added because we wanted to give master control on which standbys can become SYNC standbys and in what order of priority. But if we do what you are suggesting above, I wonder if we can just have an explicit knob to make a standby a SYNC standby. Say something like: standby1.sync_mode = SYNC|ASYNC.
The other purpose of synchronous_standby_names is to define order of preference for sync standbys. Even that can then be explicitly specified using this mechanism.
I am not sure but may be we can even allow to set synchronous_commit at each standby level. So depending on the synchronous_commit setting of each standby and their priorities also explicitly defined by this mechanism, master will decide which standbys to wait for at the commit time.
Thanks,
Pavan
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
Re: setting separate values of replication parameters to each standby to provide more granularity
From
Samrat Revagade
Date:
The idea is to allow configuration of standby servers such that they have there own set of replication parameters as per requirements.How does this interplay with the synchronous_standby_names parameter ? Or do you think that becomes irrelevant if we do like what you are suggesting above ? AFAIK synchronous_standby_names was added because we wanted to give master control on which standbys can become SYNC standbys and in what order of priority. But if we do what you are suggesting above, I wonder if we can just have an explicit knob to make a standby a SYNC standby. Say something like: standby1.sync_mode = SYNC|ASYNC.
I have same thought in my mind.
We can handover these controls to user and he will decide the nature and priority of standby (SYNC or ASYNC)
The user will be then more sure about his settings (currently is is depend on time at which standby connects to master)
The other purpose of synchronous_standby_names is to define order of preference for sync standbys. Even that can then be explicitly specified using this mechanism.
Yes.
In that case the setup would be
standby1.sync_mode = SYNC|ASYNC.
standby1.priority = 1
standby1.wal_Sender_timeout = 40s
.
.
standby1.guc = value
.
I am not sure but may be we can even allow to set synchronous_commit at each standby level. So depending on the synchronous_commit setting of each standby and their priorities also explicitly defined by this mechanism, master will decide which standbys to wait for at the commit time.
That's a really good suggestion.
If we become successful in combining behavior of synchronous_commit with each standby standby then that would be great change and each standby has its own synchronization level.