Thread: synchronous_standby_names with '*'
Hi! I try create master for replication and in his config I set synchronous_standby_names = '*' so I can connect with other slave's (name of this slave I don't know at this moment) But if I try execute some commands (I hung on 'create database') my program hang and after some time I see in processes ... create database waiting for 0/XXXXXX and when I strace to wal I see ... select ... timeout getpid() select ... timeout getpid() ... and so on. What so '*' mean in this GUC? version 9.1beta3 linux x86_64 -- View this message in context: http://postgresql.1045698.n5.nabble.com/synchronous-standby-names-with-tp4715117p4715117.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Aug 19, 2011 at 7:06 PM, alexondi <alexondi@rambler.ru> wrote: > Hi! > I try create master for replication and in his config I set > synchronous_standby_names = '*' > so I can connect with other slave's (name of this slave I don't know at this > moment) > But if I try execute some commands (I hung on 'create database') my program > hang and after some time I see > in processes > ... create database waiting for 0/XXXXXX > and when I strace to wal I see > ... > select ... timeout > getpid() > select ... timeout > getpid() > ... > and so on. > What so '*' mean in this GUC? Setting synchronous_standby_names forces the master to perform synchronous replication. If synchronous_commit is set to 'on', all the transactions must wait for their WAL to be replicated to the standby. Since you set synchronous_standby_names to '*', "create database" hanged, i.e., was waiting for its WAL to be replicated to the standby. Only standby whose application_name matches synchronous_standby_names can run as synchronous standby. '*' matches any application_name, so '*' means that any standby can run as synchronous one. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
but if I don't have any slave at this time why my command hang? -- View this message in context: http://postgresql.1045698.n5.nabble.com/synchronous-standby-names-with-tp4715117p4715224.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Aug 19, 2011 at 8:04 PM, alexondi <alexondi@rambler.ru> wrote: > but if I don't have any slave at this time why my command hang? Because there is no standby at that time ;) If synchronous replication is enabled but there is no standby connecting to the master, a write transaction waits until at least one synchronous standby has appeared and its WAL has been replicated to it. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
so can I somehow disable|enable synchronous replication at runtime (not only for session but for the server)? -- View this message in context: http://postgresql.1045698.n5.nabble.com/synchronous-standby-names-with-tp4715117p4715296.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Aug 19, 2011 at 8:39 PM, alexondi <alexondi@rambler.ru> wrote: > so can I somehow disable|enable synchronous replication at runtime (not only > for session but for the server)? Yes. To disable; Empty synchronous_standby_names, or set synchronous_commit to "local" or "off". To enable; Set synchronous_standby_names or set synchronous_commit to "on". And then reload the configuration file. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
I think if master has not slave's at this time then server must use "local" mode. But if he has >=1 slave then he must wait until slave accept transaction. And "synchronous_standby_names" select behaviour about how master shoud select which slave must be synchronous or not. And when this GUC is '*' master should use first in the list IMHO.
In docs you (developers) write
PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server.
But I think you don't have provide software to identify AND standby failure (only notification about this situation's) - and this master without slave is this failure (slave don't started)
In docs you (developers) write
PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server.
But I think you don't have provide software to identify AND standby failure (only notification about this situation's) - and this master without slave is this failure (slave don't started)
I think
set synchronous_commit to "off"
Disable synchronous commit only for this session (connect) not for postgres instance, right?
Maybe I must chnage configuration and call "pg_ctl reload"? to change deafult behaviour for postgres instance?
set synchronous_commit to "off"
Disable synchronous commit only for this session (connect) not for postgres instance, right?
Maybe I must chnage configuration and call "pg_ctl reload"? to change deafult behaviour for postgres instance?
2011/8/19 Fujii Masao <masao.fujii@gmail.com>
On Fri, Aug 19, 2011 at 8:39 PM, alexondi <alexondi@rambler.ru> wrote:
> so can I somehow disable|enable synchronous replication at runtime (not only
> for session but for the server)?
Yes.
To disable;
Empty synchronous_standby_names, or set synchronous_commit to "local" or "off".
To enable;
Set synchronous_standby_names or set synchronous_commit to "on".
And then reload the configuration file.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
On Fri, Aug 19, 2011 at 12:04 PM, alexondi <alexondi@rambler.ru> wrote: > but if I don't have any slave at this time why my command hang? Setting synchronous_standby_names means "wait until my standby replies". If you don't have a standby, then you just made a request to wait for a long time. Suggest different approach. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services