synchronous_standby_names with '*'

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 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

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)

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?

2011/8/19 Fujii Masao <masao.fujii@gmail.com>