Multihost - replica mode

[careless6666]

Now we have target session attributes and we can choose betwen master and replica, but how i can choose only sync or only async replica?

var conn = await (_npgsqlDataSource).OpenConnectionAsync(TargetSessionAttributes.Standby, token);

conn.Host => 10.41.64.64

`patronictl list

• Cluster: dns-discovery-test (7130583837434342880) ----+--------------+---------+-----+-----------+
  | Member | Host | Role | State | TL | Lag in MB |
  +-----------------------------------------+-------------+--------------+---------+-----+-----------+
  | devpostgres9b48x69d-1452z1.com | 10.41.72.37 | Leader | running | 206 | |
  | devpostgres9b48x69d-1463z1.com | 10.41.64.64 | Replica | running | 206 | 0 |
  | devpostgres9b48x69d-1464z1.com | 10.41.60.35 | Sync Standby | running | 206 | 0 |
  | devpostgres9b48x69d-1541z1.com | 10.41.80.36 | Sync Standby | running | 206 | 0 |
  +-----------------------------------------+-------------+--------------+---------+-----+-----------+
  `
  in conn.Host property we received async replica ip address instead of sync, what am I doing wrong?

Npgsql version: 7.0.4
PostgreSQL version:
Operating system:

[vonzshik]

I'm not sure it's even possible for us to implement that. From my understanding the main difference between synchronous and asynchronous replication is that while synchronous sends the data the moment a transaction is commited, asynchronous tends to aggregate and send it in batches depending on replication delay. The point is, standby isn't aware whether it's a synchronous or asynchronous replica, so we also can't determine that.

[careless6666]

Okay, if i have this information in my side, how to effective way to open connection?

It is okay if i have NpgsqlDatataSource and will be create connection with new NpgsqlConnection("User ID=postgres;Password=docker;Host=sync-replica-host;Port=5432;Database=postgres;Pooling=true;LoadTableComposites=true")

Will there be problems with the efficiency of the pool?

[Brar]

How about having multiple NpgsqlDataSources (Pools)?
E. g., one including the primary and the synchronous replica(s) which you use when your requirement is "up top date data" and one including only the async standbys (or all servers) when "somewhat recent data" is enough.
That way you can manage them separately.

[careless6666]

Maybe I'm wrong, but I saw an issue where they said that keeping several pools is inefficient, so they made a MultihostDataSource

[Brar]

Essentially a DataSource is a pool.
MultiHostDataSource is a concept that pools your connections across multiple hosts (internally it maintains a pool per host).
There is nothing wrong with having multiple (MultiHost-)DataSources as long as you make sure you don't go beyond your servers' connection limits (by adjusting the pooling settings in the DataSources' connection strings appropriately).

[careless6666]

Okay, thanks a lot

[Brar]

BTW: If you end up using multiple DataSources and use dependency injection, you may be interested in following #5134.