Thoughts on having virtual databases

[drdrsh]

Hey,

I was thinking about how to best integrate Pgcat with Rails 6.1 without requiring any changes to the application and one way I thought it can be done is to create virtual databases, e.g. one for primaries and one for replicas.

So if the client connects to postgres://pgcat:5432/read, the connection would be set to use replicas exclusively. If the client connects to postgres://pgcat:5432/write the client would be set to use primary exclusively. Maybe also do this for shards as well. Under this mode, Pgcat will reject calls to SET SERVER ROLE TO and SET SHARD TO

This can live side by side with the current implementation, so if the client connects with the default database name, Pgcat just works normally. If they connect to one of the reserved virtual database names, we go into the proposed mode.

[levkk]

Hey,

I think we just need to implement multiple databases support like PgBouncer has. Then the client can connect to whichever and execute read/write queries. This doesn't need to be Rails-specific either, i.e. we don't have to disallow the SET SERVER ROLE TO queries. Multiple databases support should have sharding included in it as well, so having multiple databases will add another dimension to the databases structure in the pool.

[drdrsh]

Based on Lev's comment above and the discussion on this PR, this issue is no longer needed. We can achieve this by creating separate pools for each role and using default_role to do the routing for clients that are not pgcat-aware.