Is it possible to specify multi hosts dsn for connection pool?

[alvassin]

Is it possible to connect to several hosts (one master and replicas) from ConnectionPool (interface like libpq provides)?

I mean the following: https://www.postgresql.org/docs/current/static/libpq-connect.html

# Read sessions
postgresql://host1:123,host2:456/somedb?target_session_attrs=any

# Read-write sessions
postgresql://host1:123,host2:456/somedb?target_session_attrs=read-write

I enumerate all postgresql hosts and in target_session_attrs parameter specify read-write if i need master.

Or i should create separate connection pools for master & replicas servers?

How that functionality (switching between hosts depending on target_session_attrs attrs, re-connecting on master switch) can be implemented in asyncpg?

[alvassin]

As i understood, to implement multi-host dsn i need to execute SHOW transaction_read_only; command after Connection instance establishes connection.

If command returns on - it means it is replicate, otherwise - master. So, i would have two connection pools with same hosts:

• writeable pool (requires SHOW transaction_read_only == on)
• readable pool (requires SHOW transaction_read_only == off)

But there is open question - how to handle master switch? When master goes down, i should re-connect all Connection instances.

[elprans]

There is, unfortunately, no way to detect a master switch without polling. I wrote a patch to add that feature a while ago, but that effort got sidetracked. I plan to get back to it and hopefully get it into PostgreSQL 12.

Some initial effort to support multiple hosts in the connection string was done in #257, but more work is needed.

[alvassin]

@elprans Thank you for response! Unfortunately link leading to the patch returns 404.

So, for current postgresql versions master swich can be detected only via polling connections with SHOW transaction_read_only. You implemented postgresql patch, that allows to detect master switch without it, right?

Sounds cool, but postgresql 12 release would take a long time, i need solution for current production.

As there is no other way - perhaps it would be nice to implement polling, as other drivers do, and then switch to your non-polling patch in the future? We could implement some specific kind of Pool.

What is the best way to poll connections? Is polling SHOW transaction_read_only on acquire() good solution for now?

[elprans]

Thank you for response! Unfortunately link leading to the patch returns 404.

Fixed.

You implemented postgresql patch, that allows to detect master switch without it, right?

Yes. That patch makes Postgres send a special notification to all connected clients about the change.

What is the best way to poll connections? Is polling SHOW transaction_read_only on acquire() good solution for now?

I would make a pool wrapper, which contains a list of regular pools, one per host. In the wrapper you can implement whatever host selection logic (round-robin etc). Then, have a standalone task, which polls all your hosts periodically, and upon failover, adds the former master host pool to the list of standbys, and removes the new master from the standby pool list.

Something like:

read_only_pools = PoolWrapper(standby_1_pool, standby_2_pool, ...)
master_pool = asyncpg.Pool(master)

async def polling_task():
    ...
    # master changed
    read_only_pools.add(master_pool)
    read_only_pools.remove(new_master_pool)
    master_pool = new_master_pool

You can implement dynamic host changes in a similar fashion.

[alvassin]

Thank you for advise!

Periodic task does not look reliable, imagine it is being executed every 2 seconds.

With 500 RPS we would have about ~1 thousand requests failed, until PoolWrapper detects master change. Perhaps it is better to check master switch on demand, when connection is being acquired?

It would cause some performance loss, but should not fail any requests. What do you think?

[elprans]

You can check on acquire(), sure, it's just going to be a tad more complicated and would require subclassing the Pool.

[matemax]

Is there any progress by this issue? Postgres 12 was released )

[elprans]

It didn't make it into 12, but there's an active discussion on including the functionality in 13.

[Smosker]

Any news here?

[Smosker]

I have couple of hosts - one master and two replicas.

I want to pass string like postgresql://host1:123,host2:456/somedb?target_session_attrs=read-write to asyncpg, i understand correctly what it is not possible for now? And because where is not libpq underneath i cannot simply change code to pass target_session_attrs but have to implement support for target_session_attrs myself?

[elprans]

have to implement support for target_session_attrs myself

I'd be happy to accept a PR to add support for this to asyncpg.

[sreenandan]

Postgresql 13 was released and I just tested this with PG 13/asyncpg and looks like there is no support for target_session_attrs

error: unrecognized configuration parameter "target_session_attrs"

@elprans any tips on how to go about subclassing Pool?

Thanks and I highly appreciate your response.

[sreenandan]

Actually, I got an idea here. I am using Zalando/patroni for PGSQL HA. When a failover happens, there is on_role_change callback routine which could run and notify who is the current Primary. Now, how can that callback tell asyncpg about the new Primary/leader for connection pool wrapper switch? Is there some kind of callback that asyncpg can listen to?

[Niccolum]

@ronyb29 do you need any help?

[funkindy]

Hi! Can anyone share an experience on how to handle master switch now (PG14)? AFAIS target_session_attrs is not yet supported.

[alvassin]

Just in case: there is library that supports multi-dsn connection pooling with ability to switch between master & replica on the fly with different strategies: https://github.com/aiokitchen/hasql

[funkindy]

Just want to note, that psycopg3 also supports target_session_attrs connection param, which is crucial in a lot of multihost setups. Would be nice to have it in asyncpg