0

I have a requirement to route the incoming query to respective DB instances. I'm new to managing Databases.

eg: I have 4 Postgres RDS. say rds_master, rds1, rds2 and rds3

rds_master contains schema table which has mapping of which subdomain points to which schema

  • rds1 contains schema 1 to 10 (host - rds1.abc.com)
  • rds2 contains schema 11 to 20 (host - rds2.abc.com)
  • rds3 contains schema 21 to 30 (host - rds3.abc.com)

I'm planning to have a middleman from application server to rds (some db proxy). So when the request comes from application server (host - rds.abc.com), db proxy should identify the schema information and send the request to respective rds instance.

eg: 10.abc.com running some query, then proxy should route the incoming query to rds2.abc.com

How can I achieve this routing algorithm? is there any db proxy that can do this? Help is much needed for this. Thanks in advance.

Improve this question
3
  • 2
    Before inventing your own sharding solution, have a look at Citus and PL/Proxy. Commented May 2, 2024 at 5:46
  • Do you control the schemas of all those 40 databases? They could have the same or different schemas... Commented May 2, 2024 at 10:24
  • All schemas are same. Its just each schema represents 1 customer for us. Commented May 3, 2024 at 9:23
Related questions
1251
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
754
Postgres: upgrade a user to be a superuser?
648
Postgres could not connect to server
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.