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

See original GitHub issue

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?

Issue Analytics

State:
Created 5 years ago
Reactions:1
Comments:14 (5 by maintainers)

Top GitHub Comments

1reaction

elpranscommented, Dec 27, 2019

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

1reaction

elpranscommented, Aug 29, 2018

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.

Top Results From Across the Web

SQL Server Connection Pooling - ADO.NET - Microsoft Learn

Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration.

PgBouncer FAQ

PgBouncer does not have an internal multi-host configuration. It is possible via external tools: DNS round-robin. Use several IPs behind one DNS name....

34.1. Database Connection Control Functions - PostgreSQL

It is possible to specify multiple hosts to connect to, so that they are tried in the given order. In the Keyword/Value format,...

Creating or Changing Connection Pools

In the Physical layer of the Administration Tool, right-click a database and select New Object, then select Connection Pool. Or, double-click an existing ......

MySQL Connection String for C# .NET Core Programs

Multiple hosts can be specified in a comma-delimited list. On Unix-like systems, this can be a fully qualified path to a MySQL socket...