As applications scale, the database layer is often the first place teams look to improve resilience and performance. A common and effective approach is to separate read traffic from write traffic, sending writes to a single authoritative server while spreading reads across one or more replicas.

The challenge is rarely the database itself. PostgreSQL's streaming replication handles that part well. The real challenge is deciding, for every single query an application sends, which server should actually receive it.

This is precisely the problem ProxySQL was built to solve.

ProxySQL is a high-performance, open-source proxy that sits between an application and its database servers. Originally developed for MySQL, recent versions introduced native support for the PostgreSQL wire protocol, allowing the same proven proxy layer to be placed in front of a PostgreSQL cluster.

From the application's point of view, nothing changes. It simply connects to ProxySQL as though it were talking to the database directly. Behind that single connection point, ProxySQL quietly takes on the responsibility of routing, pooling, and monitoring the actual database servers.

A few benefits make this worth adopting on top of an existing replicated PostgreSQL environment:

• ProxySQL separates read and write traffic automatically, without requiring any changes to application code. 
• It inspects every incoming query and routes it according to configurable rules, writing to the primary and reading to the replicas while the application continues to send ordinary SQL to a single address.
• It also manages connection pooling and multiplexing, which matters considerably under concurrent load, as every PostgreSQL connection consumes a backend process and those resources are limited.

Perhaps most usefully, ProxySQL continuously monitors the replication topology. It also provides a centralised place to observe traffic, including connection pool health, query patterns, and replication status through SQL queries against its internal tables.

This guide assumes a working PostgreSQL environment already exists, with physical streaming replication configured between one writer node and one standby node. Setting up that replication is not covered here. What follows is a complete walkthrough of configuring ProxySQL correctly on top of such an environment and validating that it behaves as intended at every stage.

For clarity, the two PostgreSQL nodes referenced throughout are identified by hostname rather than IP address:

• mydbopslab1 — the writer (primary) & ProxySQL 
• mydbopslab2 — the standby (replica)

ProxySQL runs alongside mydbopslab1 in this setup, exposing its administrative interface on port 6132 and its client-facing listener on port 6133. Each step below indicates clearly where it should be executed on mydbopslab1, on mydbopslab2, or within the ProxySQL Admin console since keeping this distinction clear is essential to following the guide correctly.

ProxySQL PostgreSQL Read/Write Architecture

Transparent query routing and physical streaming replication topology

Client Application

Connects to Port 6133

ProxySQL Engine

Admin: 6132 | Proxy: 6133

mydbopslab1 HG 11

Writer (Primary) : 5432

mydbopslab2 HG 12

Standby (Replica) : 5432

Physical Streaming Replication

Step 1: Creating the application user on PostgreSQL

Before ProxySQL can route any traffic, the database itself needs a user for the application to connect as.

On mydbopslab1:

postgres -c 'psql -d mydatabase'

CREATE USER appuser WITH PASSWORD 'appuser';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO appuser;
GRANT ALL ON SCHEMA public TO appuser;

The schema grant deserves a brief mention: since PostgreSQL 15, the public schema no longer grants CREATE privileges to all users by default. Without this line, any attempt by appuser to create a table later will fail with a schema permission error a small detail, but one that is easy to overlook.

Because this user is created on the writer, it will replicate down to mydbopslab2 automatically through streaming replication. There is no need to create it a second time there.

Step 2: Creating ProxySQL's monitor user

It is worth understanding early on that ProxySQL does not use appuser, or any application identity, to perform its own health checks. It relies on a separate, dedicated identity for that purpose, configured through the pgsql-monitor_username and pgsql-monitor_password variables (which default to monitor and monitor). This role needs to genuinely exist inside PostgreSQL before ProxySQL's monitoring can succeed - it is not created automatically.

On mydbopslab1:

postgres -c 'psql'
CREATE USER monitor WITH PASSWORD 'monitor';
GRANT pg_monitor TO monitor;

This is sufficient for ProxySQL's connectivity and read-only checks, without extending full superuser privileges. As with appuser, this role will replicate to mydbopslab2 on its own.

Step 3: Authorising both identities in pg_hba.conf on both nodes

At this point, two distinct identities need to be able to connect to PostgreSQL from the ProxySQL host: the application user, appuser, and ProxySQL's monitor user. Both require explicit authorisation, and this is easy to miss; both need it on both nodes, not only the one where a particular test happens to be run.

On mydbopslab1, edit /etc/postgresql/18/main/pg_hba.conf and add:

host    mydatabase      appuser         mydbopslab1/32        scram-sha-256
host    mydatabase      appuser         127.0.0.1/32           scram-sha-256
host    all             monitor         mydbopslab1/32        scram-sha-256

On mydbopslab2, edit the same file and add:

host    mydatabase      appuser         mydbopslab1/32        scram-sha-256
host    all             monitor         mydbopslab1/32        scram-sha-256

The source address in these rules should reflect wherever ProxySQL actually connects from in this environment, that is, mydbopslab1, since ProxySQL runs on the same host as the writer.

Once both files are updated, reload PostgreSQL on each node (a restart is not required for pg_hba.conf changes):

systemctl reload postgresql

It is worth confirming this step properly before moving on. From the ProxySQL host, a direct connection as appuser against the standby should succeed cleanly:

psql -h mydbopslab2 -p 5432 -U appuser -d mydatabase -c "SELECT 1;"
Password for user appuser:
 ?column?
----------
        1
(1 row)

The same test should be repeated for appuser against mydbopslab1, and for monitor against both nodes before continuing. Every subsequent step in this guide depends on this connectivity being solid.

Step 4: Pointing the monitor at the correct credentials

With servers and users registered, ProxySQL needs to be told which credentials to use for its own internal health checks.

Inside ProxySQL Admin:

SET pgsql-monitor_username='monitor';
SET pgsql-monitor_password='monitor';
LOAD PGSQL VARIABLES TO RUNTIME;
SAVE PGSQL VARIABLES TO DISK;

A useful way to confirm this is working is to look at the connection pool statistics and check that latency figures are genuinely populated, rather than sitting at zero:

ProxySQLAdmin> SELECT * FROM stats_pgsql_connection_pool;
+-----------+-------------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host    | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-------------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------+
| 11        | mydbopslab1 | 5432     | ONLINE | 0        | 1        | 1      | 22      | 1           | 1       | 102             | 125             | 640        |
| 12        | mydbopslab2 | 5432     | ONLINE | 0        | 0        | 1      | 44      | 1           | 1       | 32              | 86              | 1471       |
+-----------+-------------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)

Step 5: Registering the backend servers

With the user in place, the next step is telling ProxySQL which physical servers exist, and which role each one plays. Two hostgroups are used here: 11 for writes, and 12 for reads.

Inside ProxySQL Admin:

INSERT INTO pgsql_servers (hostgroup_id, hostname, port) VALUES (11, 'mydbopslab1', 5432);
INSERT INTO pgsql_servers (hostgroup_id, hostname, port) VALUES (12, 'mydbopslab2', 5432);
LOAD PGSQL SERVERS TO RUNTIME;
SAVE PGSQL SERVERS TO DISK;

The result of this configuration can be confirmed directly:

ProxySQLAdmin> SELECT * FROM runtime_pgsql_servers;
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11           | mydbopslab1 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 12           | mydbopslab2 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

Step 6: Registering the application user inside ProxySQL

With PostgreSQL prepared, attention turns to ProxySQL itself. The first thing it needs to know is which users are permitted to connect through it, and where their traffic should default to.

Inside ProxySQL Admin:

INSERT INTO pgsql_users (username, password, default_hostgroup) VALUES ('appuser', 'appuser', 11);
LOAD PGSQL USERS TO RUNTIME;
SAVE PGSQL USERS TO DISK;

Here, '11' refers to the host group defined as the writer host group. It is worth being precise here: a mismatch between this default_hostgroup value and the hostgroup IDs actually used for the backend servers is one of the more common causes of a "Max connect timeout reached while reaching hostgroup N" error, since ProxySQL will attempt to route to a hostgroup that has no servers registered in it at all.

Step 7: Defining query rules for read/write splitting

This is the step that gives ProxySQL its routing intelligence. Query rules examine the shape of each incoming query and decide where it belongs.

Inside ProxySQL Admin:

INSERT INTO pgsql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE$', 11, 1);
INSERT INTO pgsql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 12, 1);
LOAD PGSQL QUERY RULES TO RUNTIME;
SAVE PGSQL QUERY RULES TO DISK;

The first rule ensures that locking reads - SELECT ... FOR UPDATE, are still sent to the writer, since they need to participate in write-side locking semantics. The second rule sends everything else that begins with SELECT to the reader hostgroup. Anything that does not match a SELECT pattern at all inserts, updates, deletes, and transactions falls through to the user's default_hostgroup, which was already set to 11 in Step 4.

ProxySQLAdmin> select * from pgsql_query_rules;
+---------+--------+----------+----------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+------------+---------+
| rule_id | active | username | database | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest         | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | attributes | comment |
+---------+--------+----------+----------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+------------+---------+
| 20      | 1      | NULL     | NULL     | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*FOR UPDATE$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 11                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | 1   | 1     |            | NULL    |
| 21      | 1      | NULL     | NULL     | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*            | NULL          | 0                    | CASELESS     | NULL    | NULL            | 12                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | 1   | 1     |            | NULL    |
+---------+--------+----------+----------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+------------+---------+
2 rows in set (0.00 sec)

Step 8: Enabling automatic writer and reader detection

So far, ProxySQL has been told, statically, which server plays which role. This next step is what allows that assignment to become dynamic; ProxySQL will continuously check each server's actual replication state and reassign its hostgroup automatically if that state ever changes, which is precisely the mechanism that makes automatic failover detection possible.

Inside ProxySQL Admin:

INSERT INTO pgsql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES (11, 12, 'read_only', 'pg cluster - writer/reader');
LOAD PGSQL SERVERS TO RUNTIME;
SAVE PGSQL SERVERS TO DISK;

This can be confirmed immediately afterwards:

ProxySQLAdmin> SELECT * FROM runtime_pgsql_replication_hostgroups;
+------------------+------------------+------------+-----------------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment                     |
+------------------+------------------+------------+-----------------------------+
| 11               | 12               | read_only  | pg cluster - writer/reader  |
+------------------+------------------+------------+-----------------------------+
1 row in set (0.00 sec)

Step 9: Validating read and write splitting end to end

With the configuration complete, it is time to confirm that it actually behaves as intended, under real queries rather than assumptions.

On mydbopslab1, a small validation table is created directly against the writer:

psql -h mydbopslab1 -p 5432 -U appuser -d mydatabase -c "CREATE TABLE IF NOT EXISTS routing_test (id serial PRIMARY KEY, note text, created_at timestamp DEFAULT now());"
Password for user appuser:
CREATE TABLE

From here, all traffic is sent through ProxySQL's client-facing port, 6133, rather than directly to either PostgreSQL node.

A write, issued inside a transaction, should land on the writer:

psql -h 127.0.0.1 -p 6133 -U appuser -d mydatabase -c "BEGIN; INSERT INTO routing_test (note) VALUES ('reset-test'); SELECT inet_server_addr(); COMMIT;"
Password for user appuser:
BEGIN
INSERT 0 1
 inet_server_addr
------------------
 mydbopslab1
(1 row)
COMMIT

A plain, standalone read should land on the reader:

psql -h 127.0.0.1 -p 6133 -U appuser -d mydatabase -c "SELECT inet_server_addr();"
Password for user appuser:
 inet_server_addr
------------------
 mydbopslab2
(1 row)

This confirms the central objective of the entire setup: writes are directed to mydbopslab1, reads are directed to mydbopslab2, and the application never needs to know the difference – it simply talks to ProxySQL on port 6133.

Step 10: Adding the writer as a fallback reader

There is a subtle gap worth addressing before considering this configuration production-ready. If the standby becomes unavailable for any reason a crash, planned maintenance, or a transient network issue reads currently have nowhere to go, even though the writer remains perfectly capable of serving them. This is worth closing proactively, rather than discovering it during an actual incident.

Inside ProxySQL Admin:

INSERT INTO pgsql_servers (hostgroup_id, hostname, port, weight, comment) VALUES (12, 'mydbopslab1', 5432, 1, 'writer-as-reader-fallback');

LOAD PGSQL SERVERS TO RUNTIME;
SAVE PGSQL SERVERS TO DISK;

The resulting server list reflects the addition clearly:

ProxySQLAdmin> select * from pgsql_servers;
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------------------+
| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                    |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------------------+
| 11           | mydbopslab1 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |                             |
| 12           | mydbopslab2 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |                             |
| 12           | mydbopslab1 | 5432 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | writer-as-reader-fallback  |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------------------+
3 rows in set (0.00 sec)

Where the vast majority of reads should continue to favour the standby, the relative weighting between these two entries can be adjusted for instance, assigning a weight of 100 to mydbopslab2 and 1 to the writer's reader entry so that roughly ninety-nine per cent of reads prefer the healthy standby, while the remainder occasionally exercise the fallback path even under normal conditions.

Step 11: Validating automatic failure detection

This step is what ultimately demonstrates that the setup is not merely correctly configured, but genuinely resilient. A standby outage is simulated, and two things are confirmed: that ProxySQL detects it promptly, and that reads continue to succeed by falling back to the writer, as configured in the previous step.

On mydbopslab2, the moment of the outage is recorded, and PostgreSQL is stopped:

Within seconds, ProxySQL's monitor registers the failure. The following is the actual log captured immediately afterwards:

ProxySQLAdmin> SELECT * FROM monitor.pgsql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------+
| hostname    | port | time_start_us    | success_time_us | read_only | error                                                                                      |
+-------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------+
| mydbopslab1 | 5432 | 1784056908354474 | 390             | 0         | NULL                                                                                       |
| mydbopslab1 | 5432 | 1784056907354536 | 264             | 0         | NULL                                                                                       |
| mydbopslab1 | 5432 | 1784056906354269 | 306             | 0         | NULL                                                                                       |
| mydbopslab1 | 5432 | 1784056905354273 | 271             | 0         | NULL                                                                                       |
| mydbopslab1 | 5432 | 1784056904354037 | 262             | 0         | NULL                                                                                       |
| mydbopslab1 | 5432 | 1784056903354340 | 291             | 0         | NULL                                                                                       |
| mydbopslab2 | 5432 | 1784056903354182 | 0               | NULL      | Connection refused  Is the server running on that host and accepting TCP/IP connections?  |
| mydbopslab2 | 5432 | 1784056902354068 | 0               | NULL      | Connection refused  Is the server running on that host and accepting TCP/IP connections?  |
| mydbopslab1 | 5432 | 1784056902353944 | 299             | 0         | NULL                                                                                       |
| mydbopslab2 | 5432 | 1784056901354173 | 0               | NULL      | Connection refused  Is the server running on that host and accepting TCP/IP connections?  |
+-------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mydbopslab1 continues reporting read_only = 0 cleanly, once every second, entirely unaffected by the standby's outage the write path remains stable throughout. mydbopslab2, meanwhile, begins failing immediately with a connection-refused error.

After a short run of repeated failures, ProxySQL marks the unreachable node accordingly:

ProxySQLAdmin> SELECT * FROM stats_pgsql_connection_pool;
+-----------+-------------+----------+---------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host    | srv_port | status  | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-------------+----------+---------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------+
| 11        | mydbopslab1 | 5432     | ONLINE  | 0        | 0        | 0      | 22      | 1           | 2       | 204             | 250             | 213        |
| 12        | mydbopslab2 | 5432     | SHUNNED | 0        | 0        | 2      | 44      | 1           | 2       | 64              | 172             | 395        |
+-----------+-------------+----------+---------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)
ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM runtime_pgsql_servers;
+--------------+-------------+---------+
| hostgroup_id | hostname    | status  |
+--------------+-------------+---------+
| 11           | mydbopslab1 | ONLINE  |
| 12           | mydbopslab2 | SHUNNED |
+--------------+-------------+---------+
2 rows in set (0.00 sec)

With the writer-as-reader-fallback entry from Step 10 already in place, however, the identical read succeeds instead:

psql -h 127.0.0.1 -p 6133 -U appuser -d mydatabase -c "SELECT inet_server_addr();"
Password for user appuser:
 inet_server_addr
------------------
 mydbopslab1
(1 row)

Reads degrade gracefully to the writer for as long as the standby remains unavailable, with no error surfaced to the application at all. Once mydbopslab2 returns and passes health checks again, ProxySQL naturally shifts read traffic back to it, without any manual intervention.

The same underlying mechanism applies, symmetrically, to a failure of the writer. Should mydbopslab1 become unreachable, monitor.pgsql_server_read_only_log would begin reporting connection failures against it instead; and if mydbopslab2 is subsequently promoted, its next read_only check will return 0, at which point the pgsql-monitor_writer_is_also_reader setting enabled by default automatically places it into both the writer and reader hostgroups. runtime_pgsql_servers reflects this new topology without requiring any manual reconfiguration.

There is, however, one operational principle that must always be respected: the failing writer needs to be genuinely stopped, or otherwise fenced, before or during promotion. If it is left running and reachable while a standby is promoted, ProxySQL has no means of knowing which of the two is the legitimate primary it simply trusts what each node reports about itself, independently of the other. A promotion should therefore always be paired with fencing the node being replaced, never carried out on its own.

ntegrating ProxySQL with PostgreSQL provides transparent query routing, connection management, and automatic backend monitoring without requiring code changes in application services. By setting up proper query rules, hostgroups, and fallback paths, database infrastructure maintains stability and high availability during node failures and routine maintenance.

Read more about scaling PostgreSQL environments on the Mydbops Blog or learn about custom PostgreSQL Database Consulting Services to optimize database infrastructure.

Need help scaling your PostgreSQL infrastructure or setting up high availability?
Partner with our database experts to optimize performance, setup intelligent query proxying, and ensure 99.999% uptime for your critical database environments.