CODEWITHSUNDEEP
postgresqlkubernetesdatabase-replication
Shauryagoel
Shauryagoel

Reputation: 63

How to perform read queries on read replica in postgres?

I deployed a PostgreSQL instance in k8s with a master pod and a single replica pod. I want to know how can I perform read queries on the read replica and write queries on the master? Currently, all the requests I send to the DB are served by the master (after connecting to the master). Do I need to explicitly connect to the read replica for queries to go there or is there some mechanism in Postgres which can route traffic to the read replica?

Upvotes: 0

Views: 1795

Answers (4)

Suhas Adhav
Suhas Adhav

Reputation: 46

For routing your queries specifically to any replica - you can use connection pooler like pgbouncer which can be configured based on the connections you define. For connections to get routed specifically to read replica you can follow below approach in Kubernetes:

  1. You must be using some mechanism to identify read and write replicas in Kubernetes - possibly labels, use these for identification. We use Patroni for HA and DR.
  2. Ceate a read-service in Kubernetes which will specifically point to read replicas based on labels. So even if going forward read replica gets changed read will be pointed towards that specific read-pod based on label.
  3. In pgbouncer configure read and write connections separately based on the services you created. In pgbouncer.ini you can have below configuration specific to read and write service:

[databases]
read-db = host=read-service.svc.cluster.local port=5432 dbname=mydb
write-db = host=write-service.svc.cluster.local port=5432 dbname=mydb

For read application we use HAProxy as redirector to read and write queries. I have written an article to solve this on Patroni VM setup - Configure Read Only Apps with Patroni

Upvotes: 0

Alireza Alami
Alireza Alami

Reputation: 87

You can use PgBouncer or Pgpool-II to manage connections efficiently, but for routing read and write queries automatically, Pgpool-II is a better choice.

  • Routes write queries to the master.
  • Distributes read queries to replicas.
  • Provides load balancing for read replicas.
  • Offers connection pooling to improve performance. Additionally, you can check out Patroni for managing high availability (HA) and automatic failover in PostgreSQL. Patroni uses etcd, Consul, or Zookeeper to manage leader election and ensure that your master-replica setup is resilient.

Upvotes: 0

Roman
Roman

Reputation: 34

Yes! You can use connection pooler, for example PgCat, which will route your queries using sqlparser written on Rust. Write queries to primary instance, read queries to replica. Be careful and write additional tests before using it in production

Links: https://github.com/postgresml/pgcat

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247625

There is no reliable way to automatically redirect read requests to the standby, since in PostgreSQL you cannot reliably tell if an SQL statement writes or not (example: you can create a function that deletes rows and call it in a SEKECT statement). You will have to teach your application to connect to the standby to perform read requests.

Note that horizontal scaling in that fashion comes with some problems:

  • since replication is asynchronous, and replication conflicts can delay replay, changes on the primary might not be visible on the standby right away

  • if you have long-running queries on the standby, they might be canceled due to replication conflicts, and you have to retry them

Upvotes: 3

Related Questions