This blogpost dives into the latest advancements in ProxySQL‘s monitoring capabilities, specifically about the recently added monitoring support for PostgreSQL, as well as serve as a guide for you through ProxySQL configuration process, showcasing inspecting monitoring data, error handling and how ProxySQL will ensure high availability and resilience for your PostgreSQL deployments.
As a final demostration of these capabilities, thanks to the recently added support for Read-Only monitoring, we will simulate an unplanned failover in a PostgreSQL replication cluster, letting ProxySQL handle the automatic failover detection and traffic redirection.
Primary-Replica Setup
We are going to use a very simple PostgreSQL replication setup to revisit several ProxySQL monitoring principles by example. The infra of choice is going to be a single primary and a single replica, and in the following order, we will:
- Configure
ProxySQLto enable monitoring for backend servers. - Verify monitoring is working as expected: tables, metrics and logs.
- Learn how
ProxySQLresponds topingmonitoring errors. -
Configure
ProxySQLto take advantage of the replication setup:- Configure
read-onlymonitoring for ourprimaryandreplicaservers. - Configure basic query routing for our
readerhostgroup. - Test our setup with
sysbench. - Simulate a failover under load and verify how
ProxySQLhandled it.
- Configure
For a more in depth reading and understanding about these core-concepts and principles, please refer to the reference documentation, or the user guide.
ProxySQL Configuration
Let’s clone the repo holding the infra and config that we will be using:
git clone https://github.com/ProxySQL/non-prod-demo-infras/
PostgreSQL config is condensed in the following file:
00_init.sql:
CREATE USER repluser WITH replication encrypted password 'replpass';
SELECT pg_create_physical_replication_slot('replication_slot');
CREATE USER proxymon WITH encrypted password 'proxymon';
GRANT pg_monitor TO proxymon;
-- For testing 'pgsql-monitor_dbname'
CREATE DATABASE proxymondb;
And containers are initialized with these common values:
---
POSTGRES_USER: postgres
POSTGRES_DB: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_HOST_AUTH_METHOD: "scram-sha-256\nhost replication all 0.0.0.0/0 md5"
POSTGRES_INITDB_ARGS: "--auth-host=scram-sha-256"
To start/stop the infra just use the two following usual docker commands:
docker compose up -d # Start the infra in the background
docker compose down -v # Destroy the infra and the volumes
After starting the infra, let’s start a ProxySQL instance with the config present in the repo:
$ export INFRA=$PATH_TO_REPO/pg-mon-primary-replica/
$ proxysql --clickhouse-server --sqlite3-server --idle-threads -f -c $INFRA/conf/proxysql/proxysql.cnf -D $INFRA/datadir
After launching the instance, we can check that there are no servers yet, for this we use the Admin interface. The configuration present in the config file allowed for two users, and defined the port:
# Administration variables
admin_variables=
{
...
# IMPORTANT: Remember that default 'admin:admin' credentials can only be used from localhost. These are
# sample defaults and should be changed for your configuration.
admin_credentials="admin:admin;radmin:radmin"
...
# Port for accessing Admin interface using `PostgreSQL` Protocol
pgsql_ifaces="0.0.0.0:6132"
}
We verify that we can connect to that port with such credentials, and we check that we have no servers configured:
$ PGPASSWORD='radmin' psql -h127.0.0.1 -p6132 -Uradmin
psql (16.3, server 16.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------
(0 rows)
Since there are no servers configured, there is yet no monitoring performed by ProxySQL. Since monitoring is enabled by default, and not changed in our configuration, server monitoring (ping and connect checks), should start as soon as they are configured. Let’s double check the configuration variables:
radmin=# SELECT * FROM global_variables WHERE variable_name='pgsql-monitor_enabled';
variable_name | variable_value
-----------------------+----------------
pgsql-monitor_enabled | true
(1 row)
So, monitoring is enabled as expected, let’s check the rest of the monitoring config:
radmin=# SELECT * FROM global_variables WHERE variable_name LIKE 'pgsql-monitor_%';
variable_name | variable_value
-------------------------------------------+----------------
pgsql-monitor_enabled | true
pgsql-monitor_connect_interval_window | 50
pgsql-monitor_ping_interval_window | 10
pgsql-monitor_ping_max_failures | 3
pgsql-monitor_ping_timeout | 1000
pgsql-monitor_read_only_interval | 1000
pgsql-monitor_read_only_interval_window | 10
pgsql-monitor_read_only_timeout | 800
pgsql-monitor_read_only_max_timeout_count | 3
pgsql-monitor_username | monitor
pgsql-monitor_password | monitor
pgsql-monitor_threads | 2
pgsql-monitor_history | 600000
pgsql-monitor_connect_interval | 60000
pgsql-monitor_connect_timeout | 600
pgsql-monitor_ping_interval | 8000
pgsql-monitor_dbname | postgres
(17 rows)
All defaults seems correct, we will later explore and play with these values. Let’s configure both servers, the primary and replica, and check that monitoring is now properly running:
$ PGPASSWORD='radmin' psql -h127.0.0.1 -p6132 -Uradmin < pg-mon-primary-replica/conf/proxysql/servers-single_primary_replica.sql
DELETE 0
INSERT 0 2
LOAD
SAVE
For checking the servers status we query table runtime_pgsql_servers:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(2 rows)
And also the stats_pgsql_connection_pool
radmin=# 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
-----------+-----------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
1 | 127.0.0.1 | 15433 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(2 rows)
After performing any configuration, it’s sensible to check the Error Log to double check that the configuration was indeed updated and that no errors took or are taking place after the change:
...... --> Config promotion command is acknowledge in the next line
2024-10-28 13:37:11 Admin_Handler.cpp:281:is_admin_command_or_alias(): [INFO] Received LOAD PGSQL SERVERS TO RUNTIME command
2024-10-28 13:37:11 PgSQL_HostGroups_Manager.cpp:1209:commit(): [INFO] Generating runtime pgsql servers and pgsql servers v2 records.
...... --> Servers regeneration information is taking place
...... --> Second config promotion command is acknowledge in the next line
2024-10-28 13:37:11 Admin_Handler.cpp:281:is_admin_command_or_alias(): [INFO] Received SAVE PGSQL SERVERS TO DISK command
...... --> After monitoring starts, errors start to accumulate in the error log
2024-10-28 13:37:13 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15433' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:13 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:21 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15433' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:21 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:29 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15433' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:29 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:37 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15433' error='FATAL: password authentication failed for user "monitor"'
2024-10-28 13:37:37 PgSQL_Monitor.cpp:628:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='FATAL: password authentication failed for user "monitor"'
As we saw in the Error Log, there is still missing configuration, we haven’t updated the monitoring username and password, you can check the current ones in the previous command we executed for dumping monitoring config. Because of this, we see monitoring errors accumulate in the Error Log, matching the monitoring checks intervals (8000ms by default for ping).
Before fixing this scenario, we are going to check the rest of the places from which we can inspect the monitoring actions. From tables monitor.pgsql_server_connect_log and monitor.pgsql_server_ping_log we can check the direct result of each check:
radmin=# SELECT * FROM monitor.pgsql_server_connect_log LIMIT 5;
hostname | port | time_start_us | connect_success_time_us | connect_error
-----------+-------+------------------+-------------------------+-----------------------------------------------------------
127.0.0.1 | 15433 | 1729858325226277 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15432 | 1729858325226394 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15432 | 1729858385226389 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15433 | 1729858385226313 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15433 | 1729858445226396 | 0 | FATAL: password authentication failed for user "monitor"
(5 rows)
radmin=# SELECT * FROM monitor.pgsql_server_ping_log LIMIT 5;
hostname | port | time_start_us | ping_success_time_us | ping_error
-----------+-------+------------------+----------------------+-----------------------------------------------------------
127.0.0.1 | 15433 | 1729858273243186 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15432 | 1729858273243181 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15433 | 1729858281243252 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15432 | 1729858281243239 | 0 | FATAL: password authentication failed for user "monitor"
127.0.0.1 | 15433 | 1729858289243360 | 0 | FATAL: password authentication failed for user "monitor"
(5 rows)
And from metrics we can see if the actions are being successful or resulting in failures:
radmin=# SELECT * FROM stats_pgsql_global WHERE variable_name LIKE '%PgSQL_Monitor%';
Variable_Name | Variable_Value
-----------------------------------+----------------
PgSQL_Monitor_connect_check_OK | 0
PgSQL_Monitor_connect_check_ERR | 4
PgSQL_Monitor_ping_check_OK | 0
PgSQL_Monitor_ping_check_ERR | 18
PgSQL_Monitor_read_only_check_OK | 0
PgSQL_Monitor_read_only_check_ERR | 0
(6 rows)
Even from stats_pgsql_connection_pool we could hint that something was off in the setup, despite of servers being configured, latency_us was 0. In a healthy setup, latency_us will be updated by ping checks, and it’s value will never be 0.
Now, let’s address the issue by updating the monitoring credentials. We are only required to update username and password, since the default database that is used for monitoring connections is postgres. If we wanted to connect to other custom database, we could do it by changing pgsql-monitor_dbname:
radmin=# SET pgsql-monitor_password='proxymon';
UPDATE 1
radmin=# SET pgsql-monitor_username='proxymon';
UPDATE 1
radmin=# LOAD PGSQL VARIABLES TO RUNTIME;
LOAD
radmin=# SAVE PGSQL VARIABLES TO DISK;
INSERT 0 141
NOTE: Monitoring user is required to have pg_monitor privileges in order to perform the readonly checks.
If re-check the stats now, we can see that the number succeeded operation is increasing:
radmin=# SELECT * FROM stats_pgsql_global WHERE variable_name LIKE '%PgSQL_Monitor%';
Variable_Name | Variable_Value
-----------------------------------+----------------
PgSQL_Monitor_connect_check_OK | 2
PgSQL_Monitor_connect_check_ERR | 4
PgSQL_Monitor_ping_check_OK | 12
PgSQL_Monitor_ping_check_ERR | 28
PgSQL_Monitor_read_only_check_OK | 0
PgSQL_Monitor_read_only_check_ERR | 0
(6 rows)
We can also directly check the monitoring tables, in order to see the results of each check:
radmin=# SELECT * FROM monitor.pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
hostname | port | time_start_us | ping_success_time_us | ping_error
-----------+-------+------------------+----------------------+------------
127.0.0.1 | 15433 | 1729871929021524 | 414 |
127.0.0.1 | 15432 | 1729871929021411 | 475 |
127.0.0.1 | 15432 | 1729871921021480 | 446 |
127.0.0.1 | 15433 | 1729871921021392 | 447 |
127.0.0.1 | 15433 | 1729871913021424 | 419 |
(5 rows)
radmin=# SELECT * FROM monitor.pgsql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
hostname | port | time_start_us | connect_success_time_us | connect_error
-----------+-------+------------------+-------------------------+-----------------------------------------------------------
127.0.0.1 | 15433 | 1729871877019975 | 19320 |
127.0.0.1 | 15432 | 1729871877019836 | 16824 |
127.0.0.1 | 15433 | 1729871817019995 | 17701 |
127.0.0.1 | 15432 | 1729871817019536 | 21815 |
127.0.0.1 | 15433 | 1729871757019843 | 0 | FATAL: password authentication failed for user "monitor"
(5 rows)
The new operations now show proper success times for both servers, and no errors. We can also revisit stats_pgsql_connection_pool and check that latency_us is now being updated thanks to ping checks:
radmin=# 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
-----------+-----------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 434
1 | 127.0.0.1 | 15433 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 427
(2 rows)
Let’s change the last connection creation config value, pgsql-monitor_dbname, and check that ProxySQL is renovating the monitoring connections connecting against this db. First we check the current connections present in PostgreSQL:
postgres=# SELECT pid,datname,application_name,state FROM pg_stat_activity;
pid | datname | application_name | state
--------+----------+------------------+--------
203632 | postgres | psql | active
204663 | postgres | ProxySQL-Monitor | idle
...
(6 rows)
Now, we change the current config for ProxySQL:
radmin=# SET pgsql-monitor_dbname='proxymondb';
UPDATE 1
radmin=# LOAD PGSQL VARIABLES TO RUNTIME;
LOAD
New monitoring connections will use the new user, eventually replacing previous ones:
postgres=# SELECT pid,datname,application_name,state FROM pg_stat_activity;
pid | datname | application_name | state
--------+------------+------------------+--------
203632 | postgres | psql | active
204712 | proxymondb | ProxySQL-Monitor | idle
...
(6 rows)
Ping Errors – Servers SHUNNING
Once we have a healthy setup. Let’s create a source of errors and see how ProxySQL reacts to those errors:
# Prevent traffic from reach the replica
sudo iptables -I OUTPUT 1 -p tcp -d 127.0.0.1 --dport 15433 -j DROP
Now let’s check how monitoring table record this event:
radmin=# SELECT * FROM monitor.pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 6;
hostname | port | time_start_us | ping_success_time_us | ping_error
-----------+-------+------------------+----------------------+---------------------
127.0.0.1 | 15433 | 1729947305791361 | 0 | Operation timed out
127.0.0.1 | 15432 | 1729947305791335 | 328 |
127.0.0.1 | 15433 | 1729947297791230 | 0 | Operation timed out
127.0.0.1 | 15432 | 1729947297791219 | 453 |
127.0.0.1 | 15433 | 1729947289791258 | 0 | Operation timed out
127.0.0.1 | 15432 | 1729947289791241 | 354 |
(6 rows)
radmin=# SELECT * FROM monitor.pgsql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;
hostname | port | time_start_us | connect_success_time_us | connect_error
-----------+-------+------------------+-------------------------+---------------
127.0.0.1 | 15432 | 1729947357115316 | 20614 |
127.0.0.1 | 15432 | 1729947297115226 | 20946 |
127.0.0.1 | 15432 | 1729947237115265 | 18975 |
127.0.0.1 | 15432 | 1729947177115203 | 15218 |
127.0.0.1 | 15432 | 1729947117114955 | 15655 |
127.0.0.1 | 15432 | 1729947057115016 | 17177 |
(6 rows)
As we can see, ping operations are timing out, and connect operations have stopped. This is expected, since only responsive servers (servers which respond to ping) are considered for further monitoring actions, as always we can resort to the Error Log to find the motivation for the actions taken:
proxysql.log:
...
2024-10-28 09:16:43 PgSQL_Monitor.cpp:1403:shunn_non_resp_srv(): [ERROR] Server 127.0.0.1:15433 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
...
Now that the server is SHUNNED, ProxySQL will attempt to pick other server in the hostgroup when a client tries to send traffic to such hostgroup. Since the only server in hostgroup 1 is this server, it will make a last attempt to wake the server in a best effort for serving the traffic. Let’s try and verify this process with the help of the Error Log:
PGPASSWORD='postgres' psql -h127.0.0.1 -p6133 -Upostgres
psql: error: connection to server at "127.0.0.1", port 6133 failed: FATAL: User not found
connection to server at "127.0.0.1", port 6133 failed: FATAL: User not found
Looks like first we need to configure the correct users in ProxySQL, so let’s do that:
radmin=# SHOW CREATE TABLE pgsql_users;
table | Create Table
-------------+-----------------------------------------------------------------------------------------------
pgsql_users | CREATE TABLE pgsql_users ( +
| username VARCHAR NOT NULL, +
| password VARCHAR, +
| active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, +
| use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0, +
| default_hostgroup INT NOT NULL DEFAULT 0, +
| transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1, +
| fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0, +
| backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1, +
| frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1, +
| max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000, +
| attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',+
| comment VARCHAR NOT NULL DEFAULT '', +
| PRIMARY KEY (username, backend), +
| UNIQUE (username, frontend))
(1 row)
radmin=# INSERT INTO pgsql_users (username,password,default_hostgroup) VALUES ('postgres','postgres',0);
INSERT 0 1
radmin=# LOAD PGSQL USERS TO RUNTIME;
LOAD
We should be able to log into the PostgreSQL interface and perform a query:
PGPASSWORD='postgres' psql -h127.0.0.1 -p6133 -Upostgres
psql (16.3, server 16.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=> /* hostgroup=1 */ DO $$ BEGIN END $$;
ERROR: Max connect timeout reached while reaching hostgroup 1 after 10000ms
postgres=>
In the previous query we are forcing hostgroup 1 as the destination, instead of letting ProxySQL routing it to the default hostgroup 0. In the Error Log we can check ProxySQL attempt to find a healthy server in the hostgroup, and in the absence of one, trying to create a connection against the currently SHUNNED server, this will be attempted until either pgsql-connect_retries_on_failure or pgsql-pgsql-connect_timeout_server_max are exceeded:
proxysql.log:
2024-10-28 09:18:04 PgSQL_HostGroups_Manager.cpp:1994:get_random_MySrvC(): [ERROR] Hostgroup 1 has no servers available! Checking servers shunned for more than 1 second
2024-10-28 09:18:08 PgSQL_Connection.cpp:1693:handler(): [ERROR] Connect timeout on 127.0.0.1:15433 : exceeded by 357us
2024-10-28 09:18:11 PgSQL_Connection.cpp:1693:handler(): [ERROR] Connect timeout on 127.0.0.1:15433 : exceeded by 447us
2024-10-28 09:18:11 PgSQL_Monitor.cpp:1403:shunn_non_resp_srv(): [ERROR] Server 127.0.0.1:15433 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
2024-10-28 09:18:14 PgSQL_Connection.cpp:1693:handler(): [ERROR] Connect timeout on 127.0.0.1:15433 : exceeded by 296us
2024-10-28 09:18:14 PgSQL_HostGroups_Manager.cpp:1994:get_random_MySrvC(): [ERROR] Hostgroup 1 has no servers available! Checking servers shunned for more than 1 second
2024-10-28 09:18:14 PgSQL_Session.cpp:1977:handler_again___status_CONNECTING_SERVER(): [ERROR] Max connect timeout reached while reaching hostgroup 1 after 10000ms . HG status: [{"Bytes_recv":"0","Bytes_sent":"0","ConnERR":"3","ConnFree":"0","ConnOK":"0","ConnUsed":"0","Latency_us":"0","MaxConnUsed":"1","Queries":"0","hostgroup":"1","srv_host":"127.0.0.1","srv_port":"15433","status":"SHUNNED"}]
We can modify the previous behavior by, for example, changing pgsql-connect_retries_on_failure, setting it’s value to 1 will result in a different error in client side, and report in ProxySQL Error Log:
postgres=> /* hostgroup=1 */ DO $$ BEGIN END $$;
ERROR: Max connect failure while reaching hostgroup 1
proxysql.log:
2024-10-28 09:39:59 Admin_Handler.cpp:774:admin_handler_command_set(): [INFO] Received command SET pgsql-connect_retries_on_failure=1
2024-10-28 09:40:01 Admin_Handler.cpp:281:is_admin_command_or_alias(): [INFO] Received LOAD PGSQL VARIABLES TO RUNTIME command
2024-10-28 09:40:12 PgSQL_HostGroups_Manager.cpp:1994:get_random_MySrvC(): [ERROR] Hostgroup 1 has no servers available! Checking servers shunned for more than 1 second
2024-10-28 09:40:15 PgSQL_Connection.cpp:1693:handler(): [ERROR] Connect timeout on 127.0.0.1:15433 : exceeded by 1050us
2024-10-28 09:40:18 PgSQL_Connection.cpp:1693:handler(): [ERROR] Connect timeout on 127.0.0.1:15433 : exceeded by 335us
2024-10-28 09:40:18 PgSQL_Monitor.cpp:1403:shunn_non_resp_srv(): [ERROR] Server 127.0.0.1:15433 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
Let’s verify the servers status by querying runtime_pgsql_servers, we find the server still as SHUNNED:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(2 rows)
If we remove our iptable rule, we should see that the monitoring table stops having errors, and that once this is detected by ProxySQL, the server will be picked without issues when traffic is redirected to its hostgroup:
sudo iptables -t nat -D OUTPUT 1
radmin=# SELECT * FROM monitor.pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 6;
hostname | port | time_start_us | ping_success_time_us | ping_error
-----------+-------+------------------+----------------------+---------------------
127.0.0.1 | 15432 | 1730105681958812 | 503 |
127.0.0.1 | 15433 | 1730105681958768 | 456 |
127.0.0.1 | 15432 | 1730105673958577 | 373 |
127.0.0.1 | 15433 | 1730105673958572 | 16043 | || Higher time points to connection creation; First connection after iptables rule deletion.
127.0.0.1 | 15432 | 1730105665958708 | 497 |
127.0.0.1 | 15433 | 1730105665958704 | 0 | Operation timed out
(6 rows)
If we now check the runtime_pgsql_servers table, we will still see the server as SHUNNED, this is expected:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(2 rows)
ProxySQL follows a lazy approach regarding server state modification, only when the server is detected as suitable for serving traffic, and picked for that purpose its status will change back to ONLINE:
postgres=> /* hostgroup=1 */ DO $$ BEGIN END $$;
DO
Since the only server in hostgroup 1 is our previously SHUNNED server, this must be the server that was picked for serving the query. Server should have brought back as ONLINE and Error Log will be clear:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(2 rows)
radmin=# 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
-----------+-----------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 0 | 1 | 1 | 0 | 1 | 6 | 243 | 393 | 482
1 | 127.0.0.1 | 15433 | ONLINE | 0 | 1 | 1 | 12 | 1 | 1 | 43 | 14 | 437
(2 rows)
The stats also reflect our previously issued queries and connection errors.
Replication Hostgroups – Failover Handling
We have revisited how to setup server monitoring and how ProxySQL deals with monitoring errors. Now we will configure our replication setup in ProxySQL, and improve our understanding on read-only checks. Let’s start by enabling them by adding the required entries to pgsql_replication_hostgroups. Since no configuration is in place, our monitoring table for read_only should be empty:
radmin=# SELECT COUNT(*) FROM pgsql_replication_hostgroups;
COUNT(*)
----------
0
(1 row)
radmin=# SELECT COUNT(*) FROM monitor.pgsql_server_read_only_log;
COUNT(*)
----------
0
(1 row)
Let’s insert one entry and activate read-only monitoring:
-- $ PGPASSWORD='radmin' psql -h127.0.0.1 -p6132 -Uradmin < pg-mon-primary-replica/conf/proxysql/replication_hostgroups-single_primary_replica.sql
DELETE FROM pgsql_replication_hostgroups;
INSERT INTO
pgsql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES
(0, 1, 'read_only', 'pg-replication');
LOAD PGSQL SERVERS TO RUNTIME;
SAVE PGSQL SERVERS TO DISK;
Checking the Error Log we verify that our new replication_hostgroups config took effect:
proxysql.log:
...
2025-05-13 20:53:28 PgSQL_HostGroups_Manager.cpp:3673:read_only_action_v2(): [INFO] read_only_action_v2() detected RO=0 on server 127.0.0.1:15432 for the first time after commit(), but no need to reconfigure
...
As the Error Log states, there was no need for server reconfiguration after the read_only detection since the server was already placed in the correct hostgroup (1) for the replica. Now we check that read-only monitoring actions had started as expected:
radmin=# SELECT COUNT(*) FROM monitor.pgsql_server_read_only_log;
COUNT(*)
----------
18
(1 row)
radmin=# SELECT * FROM monitor.pgsql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5;
hostname | port | time_start_us | success_time_us | read_only | error
-----------+-------+------------------+-----------------+-----------+-------
127.0.0.1 | 15433 | 1747162504265964 | 334 | 1 |
127.0.0.1 | 15432 | 1747162504265879 | 361 | 0 |
127.0.0.1 | 15433 | 1747162503265892 | 263 | 1 |
127.0.0.1 | 15432 | 1747162503265783 | 282 | 0 |
127.0.0.1 | 15432 | 1747162502265705 | 335 | 0 |
(5 rows)
We can confirm that the read_only values matches the expected for our primary and replica. And that stats also reflect the success of these actions:
radmin=# SELECT * FROM stats_pgsql_global WHERE variable_name LIKE '%PgSQL_Monitor%';
Variable_Name | Variable_Value
-----------------------------------+----------------
PgSQL_Monitor_connect_check_OK | 12
PgSQL_Monitor_connect_check_ERR | 0
PgSQL_Monitor_ping_check_OK | 80
PgSQL_Monitor_ping_check_ERR | 12
PgSQL_Monitor_read_only_check_OK | 242
PgSQL_Monitor_read_only_check_ERR | 0
(6 rows)
Using the same technique as before, now that read-only checks are in place, we can check how ProxySQL deals with monitoring errors in our replica. For this we will add a simple iptable rule:
sudo iptables -I OUTPUT 1 -p tcp -d 127.0.0.1 --dport 15433 -j DROP
As always, actions taken over servers will be reported in the Error Log:
proxysql.log:
2025-05-13 20:53:28 PgSQL_HostGroups_Manager.cpp:3673:read_only_action_v2(): [INFO] read_only_action_v2() detected RO=0 on server 127.0.0.1:15432 for the first time after commit(), but no need to reconfigure
2025-05-13 20:56:09 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15433 missed 3 read_only checks. Assuming read_only=1
2025-05-13 20:56:10 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15433 missed 3 read_only checks. Assuming read_only=1
2025-05-13 20:56:11 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15433 missed 3 read_only checks. Assuming read_only=1
... // Error will be reported at each 'readonly' action until server is permanently shunned
2025-05-13 20:56:24 PgSQL_Monitor.cpp:1447:shunn_non_resp_srv(): [ERROR] Server 127.0.0.1:15433 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
No further actions will be performed on the server until is detected back as ONLINE. We resort to Admin to check the monitoring info and servers status:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(2 rows)
radmin=# SELECT from_unixtime(time_start_us/1000/1000),* FROM monitor.pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
from_unixtime(time_start_us/1000/1000) | hostname | port | time_start_us | ping_success_time_us | ping_error
----------------------------------------+-----------+-------+------------------+----------------------+---------------------
2025-05-13 18:57:43 | 127.0.0.1 | 15433 | 1747162663258491 | 0 | Operation timed out
2025-05-13 18:57:43 | 127.0.0.1 | 15432 | 1747162663258470 | 279 |
2025-05-13 18:57:35 | 127.0.0.1 | 15433 | 1747162655258380 | 0 | Operation timed out
2025-05-13 18:57:35 | 127.0.0.1 | 15432 | 1747162655258354 | 266 |
2025-05-13 18:57:27 | 127.0.0.1 | 15433 | 1747162647258313 | 0 | Operation timed out
2025-05-13 18:57:27 | 127.0.0.1 | 15432 | 1747162647258262 | 288 |
2025-05-13 18:57:19 | 127.0.0.1 | 15433 | 1747162639258129 | 0 | Operation timed out
2025-05-13 18:57:19 | 127.0.0.1 | 15432 | 1747162639258095 | 227 |
2025-05-13 18:57:11 | 127.0.0.1 | 15433 | 1747162631258417 | 0 | Operation timed out
2025-05-13 18:57:11 | 127.0.0.1 | 15432 | 1747162631258409 | 301 |
(10 rows)
Apparently, the only operation that have been performed by ProxySQL is server SHUNNING. Same as previously when we didn’t have read-only configured, yet, the error is reporting us about the following assumption:
proxysql.log:
...
2025-05-13 20:56:09 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15433 missed 3 read_only checks. Assuming read_only=1
...
This assumption has consequences, we just don’t see them because the server we were targetting was the replica, which read_only value was already 1. Let’s remove the rule for the replica and impose it for the primary to check this behavior again:
sudo iptables -D OUTPUT 1
sudo iptables -I OUTPUT 1 -p tcp -d 127.0.0.1 --dport 15432 -j DROP
NOTE: Remember that in the previous section we explained why the replica won’t appear as ONLINE till it has received traffic again.
The Error Log will now report different operations over the servers:
proxysql.log:
2025-05-13 20:56:24 PgSQL_Monitor.cpp:1447:shunn_non_resp_srv(): [ERROR] Server 127.0.0.1:15433 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
2025-05-13 20:58:34 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15432 missed 3 read_only checks. Assuming read_only=1
2025-05-13 20:58:34 PgSQL_HostGroups_Manager.cpp:3701:read_only_action_v2(): [INFO] Server '127.0.0.1:15432' found with 'read_only=1', but not found as reader
2025-05-13 20:58:34 PgSQL_HostGroups_Manager.cpp:4588:insert_HGM(): [INFO] Creating new server in HG 1 : 127.0.0.1:15432 , weight=1, status=0
2025-05-13 20:58:34 PgSQL_HostGroups_Manager.cpp:4603:remove_HGM(): [WARNING] Removed server at address 0x50e000102860, hostgroup 0, address 127.0.0.1 port 15432. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2025-05-13 20:58:34 PgSQL_HostGroups_Manager.cpp:3709:read_only_action_v2(): [INFO] Regenerating table 'pgsql_servers' due to actions on server '127.0.0.1:15432'
2025-05-13 20:58:34 PgSQL_HostGroups_Manager.cpp:3736:read_only_action_v2(): [INFO] Checksum for table pgsql_servers is 0xC35A6D6EDD68A64C
2025-05-13 20:58:35 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15432 missed 3 read_only checks. Assuming read_only=1
2025-05-13 20:58:36 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15432 missed 3 read_only checks. Assuming read_only=1
2025-05-13 20:58:37 PgSQL_Monitor.cpp:1556:perf_readonly_actions(): [ERROR] Server 127.0.0.1:15432 missed 3 read_only checks. Assuming read_only=1
... // Error will be reported at each 'readonly' action until server is permanently shunned
2025-05-13 20:58:48 PgSQL_Monitor.cpp:1447:shunn_non_resp_srv(): [ERROR] Server 127.0.0.1:15432 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
The assumption of read_only=1 leads to:
- Server being removed from the
writer_hostgroup, hostgroup0. - Server being added to
reader_hostgroup, hostgroup1.
This mechanism is a protection that ProxySQL offers, that prevents serving traffic to a server, which read_only value is unknown, since it’s failing to respond to the read_only checks. The threshold for these actions is controlled by pgsql-monitor_read_only_max_timeout_count, which by default is 3.
Let’s now remove our iptable rule and recheck the Error Log:
proxysql.log:
2025-05-13 21:00:55 PgSQL_HostGroups_Manager.cpp:3639:read_only_action_v2(): [INFO] Server '127.0.0.1:15432' found with 'read_only=0', but not found as writer
2025-05-13 21:00:55 PgSQL_HostGroups_Manager.cpp:4588:insert_HGM(): [INFO] Creating new server in HG 0 : 127.0.0.1:15432 , weight=1, status=0
2025-05-13 21:00:55 PgSQL_HostGroups_Manager.cpp:3649:read_only_action_v2(): [INFO] Regenerating table 'pgsql_servers' due to actions on server '127.0.0.1:15432'
2025-05-13 21:00:55 PgSQL_HostGroups_Manager.cpp:3736:read_only_action_v2(): [INFO] Checksum for table pgsql_servers is 0x3D4560A9FDED397C
Our server has now has being found again as writer (read_only=0). Thus, a new server has been created in hostgroup 0 for it. We can see how this reflects in the runtime_pgsql_servers:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(3 rows)
Server is now present in both, hostgroup_id 1, and 0. This is a consequence of the setting pgsql-monitor_writer_is_also_reader, which by default is true. This setting is only considered whenever a node (server) is detected to change it’s read_only value from 1 to 0. The reason for this, is that this is the same scenario we encounter when a failover is taking place. This is, a server which previously has a read_only value of 1 (replica) changes its read_only value to 0 while being promoted as a new primary.
This setting allows us to control if during this promotion we would like the new primary to be placed in the writer_hostgroup only, or to be duplicated from the reader_hostgroup into the writer_hostgroup.
When we think on server placement between our defined hostgroups for read_only, we should keep in mind that ProxySQL will at first assume that the read_only value corresponds with the placement you are performing. And that the previously described read_only actions will take place when further changes are detected to this read_only values.
This means, that if we manually place our primary just in the reader_hostgroup, and reload our servers configurations, we will also achieve the previous status for runtime_pgsql_servers. ProxySQL will assume that a transition for read_only have taken place in the server (1 to 0) and will place it in both hostgroups:
radmin=# SELECT hostgroup_id,hostname,port,status,weight,comment FROM pgsql_servers;
hostgroup_id | hostname | port | status | weight | comment
--------------+-----------+-------+--------+--------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | pg-primary
1 | 127.0.0.1 | 15433 | ONLINE | 1 | pg-replica
(2 rows)
radmin=# UPDATE pgsql_servers SET hostgroup_id=1;
UPDATE 2
radmin=# LOAD PGSQL SERVERS TO RUNTIME;
LOAD
radmin=# SELECT hostgroup_id,hostname,port,status,weight,comment FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | comment
--------------+-----------+-------+--------+--------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | pg-primary
1 | 127.0.0.1 | 15432 | ONLINE | 1 | pg-primary
1 | 127.0.0.1 | 15433 | ONLINE | 1 | pg-replica
(3 rows)
proxysql.log:
2025-05-13 21:03:50 Admin_Handler.cpp:284:is_admin_command_or_alias(): [INFO] Received LOAD PGSQL SERVERS TO RUNTIME command
... // Checksum computations and table rebuilding
2025-05-13 21:03:50 PgSQL_HostGroups_Manager.cpp:1464:commit(): [INFO] PgSQL_HostGroups_Manager::commit() locked for 3ms
2025-05-13 21:03:51 PgSQL_HostGroups_Manager.cpp:3639:read_only_action_v2(): [INFO] Server '127.0.0.1:15432' found with 'read_only=0', but not found as writer
2025-05-13 21:03:51 PgSQL_HostGroups_Manager.cpp:4571:insert_HGM(): [INFO] Found server node in Host Group Container 127.0.0.1:15432 as 'OFFLINE_HARD', setting back as 'ONLINE' with: hostgroup_id=0, weight=1, compression=0, max_connections=1000, use_ssl=0, max_replication_lag=0, max_latency_ms=0, comment=pg-primary
2025-05-13 21:03:51 PgSQL_HostGroups_Manager.cpp:3649:read_only_action_v2(): [INFO] Regenerating table 'pgsql_servers' due to actions on server '127.0.0.1:15432'
2025-05-13 21:03:51 PgSQL_HostGroups_Manager.cpp:3736:read_only_action_v2(): [INFO] Checksum for table pgsql_servers is 0x3D4560A9FDED397C
2025-05-13 21:03:52 PgSQL_HostGroups_Manager.cpp:3673:read_only_action_v2(): [INFO] read_only_action_v2() detected RO=0 on server 127.0.0.1:15432 for the first time after commit(), but no need to reconfigure
With this knowledge, and insights on the behavior that we are expecting from ProxySQL we are now ready for simulate our first failover. Let’s revert this, and continue with our initial server configuration:
radmin=# LOAD PGSQL SERVERS FROM DISK;
LOAD
radmin=# LOAD PGSQL SERVERS TO RUNTIME;
LOAD
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
1 | 127.0.0.1 | 15433 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(2 rows)
We need to setup a database for sysbench, we will be doing this connecting to ProxySQL:
PGPASSWORD='postgres' psql -h127.0.0.1 -p6133 -Upostgres -dpostgres
psql (16.3, server 16.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=> CREATE DATABASE sysbench;
CREATE DATABASE
We also need to prepare the tables we will be using for sysbench to run. We will also be routing this traffic through ProxySQL:
sysbench --db-driver=pgsql --db-ps-mode=disable --pgsql-user=postgres --pgsql_password=postgres --pgsql-db=sysbench --pgsql-host=127.0.0.1 --pgsql-port=6133 --rate=1 --time=0 --tables=10 --table-size=1000 --threads=16 --report-interval=1 --pgsql-sslmode="disable" oltp_read_only prepare
To make the exercise more complete, let’s create some simple query rules that will redirect part of our sysbench traffic to the read_only hostgroup, so it only target our replica. For this, lets briefly run sysbench:
sysbench --db-driver=pgsql --db-ps-mode=disable --pgsql-user=postgres --pgsql_password=postgres --pgsql-db=sysbench --pgsql-host=127.0.0.1 --pgsql-port=6133 --rate=1000 --time=2 --tables=10 --table-size=1000 --threads=16 --report-interval=1 --pgsql-sslmode="disable" oltp_read_only run
sysbench 1.1.0-de18a03 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 16
Target transaction rate: 1000/sec
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 16 tps: 1011.52 qps: 16227.21 (r/w/o: 14200.18/0.00/2027.03) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 1s ] queue length: 0, concurrency: 3
[ 2s ] thds: 16 tps: 969.95 qps: 15563.23 (r/w/o: 13618.32/0.00/1944.90) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 2s ] queue length: 1, concurrency: 8
SQL statistics:
...
After a couple of seconds, we should have gathered enough info inside ProxySQL for creating the rules we are interested into:
radmin=# SELECT hostgroup,database,username,digest,digest_text,count_star FROM stats.stats_pgsql_query_digest LIMIT 10;
hostgroup | database | username | digest | digest_text | count_star
-----------+----------+----------+--------------------+--------------------------------------------------------------------+------------
0 | sysbench | postgres | 0x2890ffd6808a3149 | SELECT SUM(k) FROM sbtest9 WHERE id BETWEEN ? AND ? | 195
0 | sysbench | postgres | 0x2396d00b02545841 | SELECT SUM(k) FROM sbtest4 WHERE id BETWEEN ? AND ? | 186
0 | sysbench | postgres | 0x569d075505d85717 | SELECT c FROM sbtest6 WHERE id BETWEEN ? AND ? ORDER BY c | 208
0 | sysbench | postgres | 0x9d058b6f3bc2f754 | SELECT c FROM sbtest4 WHERE id=? | 1990
0 | sysbench | postgres | 0x578ba02c8a0c4542 | SELECT DISTINCT c FROM sbtest6 WHERE id BETWEEN ? AND ? ORDER BY c | 208
0 | sysbench | postgres | 0x9af59b998a3688ed | SELECT c FROM sbtest2 WHERE id=? | 1950
0 | sysbench | postgres | 0x847cd40ba8ea5175 | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c | 215
0 | sysbench | postgres | 0x4607134a412a3661 | SELECT c FROM sbtest3 WHERE id BETWEEN ? AND ? | 225
0 | sysbench | postgres | 0x381aad21f4326865 | SELECT c FROM sbtest2 WHERE id BETWEEN ? AND ? | 206
0 | sysbench | postgres | 0xd3830cc26b680e5 | SELECT c FROM sbtest4 WHERE id BETWEEN ? AND ? ORDER BY c | 217
(10 rows)
As expected, all queries are now redirected to the default hostgroup 0, let’s take advantage of ProxySQL routing and redirect some read traffic to the replica, as we would do in a real scenario:
radmin=# INSERT INTO pgsql_query_rules (active,database,match_pattern,destination_hostgroup,apply) VALUES (1,'sysbench','SELECT c FROM sbtest*',1,1);
INSERT 0 1
radmin=# LOAD PGSQL QUERY RULES TO RUNTIME;
LOAD
If we launch against sysbench, we will see that now part of the traffic is reaching hostgroup 1, we should also verify that our query rules are being exercised:
sysbench:
sysbench --db-driver=pgsql --db-ps-mode=disable --skip-trx=1 --pgsql-user=postgres --pgsql_password=postgres --pgsql-db=sysbench --pgsql-host=127.0.0.1 --pgsql-port=6133 --rate=1000 --time=1 --tables=10 --table-size=1000 --threads=16 --report-interval=1 --pgsql-sslmode="disable" oltp_read_only run
Remember that it’s important to set --skip-trx, otherwise transactions will be started and the connection will be bounded to the default hostgroup 0, thus preventing our query_rules to redirect the traffic to the correct hostgroups.
admin:
radmin=# SELECT * FROM stats_pgsql_query_rules;
rule_id | hits
---------+-------
1 | 11712
(1 row)
radmin=# 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
-----------+-----------+----------+--------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------
0 | 127.0.0.1 | 15432 | ONLINE | 0 | 17 | 17 | 0 | 16 | 34079 | 3426100 | 91216357 | 266
1 | 127.0.0.1 | 15433 | ONLINE | 0 | 15 | 15 | 0 | 15 | 11712 | 510627 | 26156013 | 270
(2 rows)
We can now simulate traffic to both hostgroups using sysbench, and simulate a failover scenario to see how it will be handled by ProxySQL. We are going to use a custom script for continuously launching sysbench to check the amount of time it took to recover traffic, this is required for two main reasons:
sysbenchfails to gracefully report an error when a connection to a server is lost. Instead, it segfaults, this can be verified directly connecting toPostgreSQL, and manually killing one of the connections.sysbencmodule forPostgreSQLdoesn’t offer--mysql-ignore-errors.
Our convenience script is going to continuously attempt to relaunch sysbench after it goes down due to the connection failure, and this way, measure the amount of time it took for ProxySQL to detect the fail-over and perform the server switch:
sysbench:
./pg-mon-primary-replica/scripts/sysbench-relaunch.sh
2025.05.14 08:50:27.536
WARNING: Both event and time limits are disabled, running an endless test
sysbench 1.1.0-de18a03 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 16
Target transaction rate: 1000/sec
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 16 tps: 967.58 qps: 13588.00 (r/w/o: 13588.00/0.00/0.00) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 1s ] queue length: 0, concurrency: 4
[ 2s ] thds: 16 tps: 995.06 qps: 13931.83 (r/w/o: 13931.83/0.00/0.00) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 2s ] queue length: 0, concurrency: 8
[ 3s ] thds: 16 tps: 979.00 qps: 13690.06 (r/w/o: 13690.06/0.00/0.00) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 3s ] queue length: 0, concurrency: 3
[ 4s ] thds: 16 tps: 1044.98 qps: 14622.68 (r/w/o: 14622.68/0.00/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 4s ] queue length: 0, concurrency: 1
...
Perform the failover in the cluster:
failover:
$ date "+%Y.%m.%d %H:%M:%S.%3N"; docker stop pg-mon-primary-replica-pg_primary-1 && docker exec pg-mon-primary-replica-pg_replica-1 pg_ctl promote -D /var/lib/postgresql/data/
2025.05.14 08:50:46.702
pg-mon-primary-replica-pg_primary-1
waiting for server to promote.... done
server promoted
Back in our script, we might see sysbench crashing and being respawning several times. This is due to the previously mentioned invalid handling of broken (disconnected) connections on sysbench side, and the fact that ProxySQL holds a connection pool for serving traffic. If sysbench was able to handle this correctly, we would just see a couple of error lines with a message like server closed the connection unexpectedly. We are interested in the latest timestamp before sysbench was able to serve traffic again:
./pg-mon-primary-replica/scripts/sysbench-relaunch.sh: line 7: 1470640 Segmentation fault (core dumped) sysbench --db-driver=pgsql --db-ps-mode=disable --skip-trx=1 --pgsql-user=postgres --pgsql_password=postgres --pgsql-db=sysbench --pgsql-host=127.0.0.1 --pgsql-port=6133 --rate=1000 --time=0 --tables=10 --table-size=1000 --threads=16 --report-interval=1 --pgsql-sslmode="disable" oltp_read_only run
2025.05.14 08:50:46.980
... // Initialization again
./pg-mon-primary-replica/scripts/sysbench-relaunch.sh: line 7: 1471054 Segmentation fault (core dumped) sysbench --db-driver=pgsql --db-ps-mode=disable --skip-trx=1 --pgsql-user=postgres --pgsql_password=postgres --pgsql-db=sysbench --pgsql-host=127.0.0.1 --pgsql-port=6133 --rate=1000 --time=0 --tables=10 --table-size=1000 --threads=16 --report-interval=1 --pgsql-sslmode="disable" oltp_read_only run
2025.05.14 08:50:47.701
WARNING: Both event and time limits are disabled, running an endless test
sysbench 1.1.0-de18a03 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 16
Target transaction rate: 1000/sec
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
... // Serving traffic again
[ 1s ] thds: 16 tps: 1021.40 qps: 14360.45 (r/w/o: 14360.45/0.00/0.00) lat (ms,95%): 22.69 err/s: 0.00 reconn/s: 0.00
[ 1s ] queue length: 0, concurrency: 3
[ 2s ] thds: 16 tps: 962.11 qps: 13447.52 (r/w/o: 13447.52/0.00/0.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 2s ] queue length: 0, concurrency: 4
[ 3s ] thds: 16 tps: 1040.01 qps: 14541.14 (r/w/o: 14541.14/0.00/0.00) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 3s ] queue length: 0, concurrency: 4
...
Using the previous timestamps we can check how fast traffic recovered after the fail-over:
2025.05.14 08:50:46.980 // We triggered the failover
2025.05.14 08:50:47.701 // Sysbench was already serving traffic again
In this case, in less than one second since we triggered the fail-over, ProxySQL reconfigured the servers using the new read-only values found and was able to resume serving traffic. Let’s first take a look at the events registered in the Error Log, let’s study by parts the log itself:
proxysql.log:
// Errors take place at the same time the failover is triggered
2025-05-14 08:50:46 PgSQL_Connection.cpp:528:handler(): [ERROR] Error: [57P01] terminating connection due to administrator command, Multi-Statement: 0
2025-05-14 08:50:46 PgSQL_Session.cpp:2868:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (0,127.0.0.1,15432,0) , FD (Conn:94 , MyDS:94) , user postgres , last_used 9ms ago : 57P01, terminating connection due to administrator command
2025-05-14 08:50:46 PgSQL_Session.cpp:2881:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
...
2025-05-14 08:50:46 PgSQL_Connection.cpp:528:handler(): [ERROR] Error: [57P01] terminating connection due to administrator command, Multi-Statement: 0
2025-05-14 08:50:46 PgSQL_Session.cpp:2868:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (0,127.0.0.1,15432,0) , FD (Conn:91 , MyDS:91) , user postgres , last_used 9ms ago : 57P01, terminating connection due to administrator command
2025-05-14 08:50:46 PgSQL_Thread.cpp:3725:process_all_sessions(): [WARNING] Closing unhealthy client connection 127.0.0.1:56936
2025-05-14 08:50:46 PgSQL_Thread.cpp:3725:process_all_sessions(): [WARNING] Closing unhealthy client connection 127.0.0.1:56942
...
... // Previous Monitoring checks fails due to server shutdown
2025-05-14 08:50:47 PgSQL_Monitor.cpp:589:handle_async_check_cont(): [ERROR] Monitor readonly failed addr='127.0.0.1:15432' status=7 error='FATAL: terminating connection due to administrator command'
... // Monitor detects the status change (read_only 1 -> 0) in the replica and promotes it as a new writer
2025-05-14 08:50:47 PgSQL_HostGroups_Manager.cpp:3639:read_only_action_v2(): [INFO] Server '127.0.0.1:15433' found with 'read_only=0', but not found as writer
2025-05-14 08:50:47 PgSQL_HostGroups_Manager.cpp:4588:insert_HGM(): [INFO] Creating new server in HG 0 : 127.0.0.1:15433 , weight=1, status=0
2025-05-14 08:50:47 PgSQL_HostGroups_Manager.cpp:3649:read_only_action_v2(): [INFO] Regenerating table 'pgsql_servers' due to actions on server '127.0.0.1:15433'
2025-05-14 08:50:47 PgSQL_HostGroups_Manager.cpp:3736:read_only_action_v2(): [INFO] Checksum for table pgsql_servers is 0xF51E868B7F7B444B
2025-05-14 08:50:47 PgSQL_Connection.cpp:528:handler(): [ERROR] Error: [57P01] terminating connection due to administrator command, Multi-Statement: 0
2025-05-14 08:50:47 PgSQL_Session.cpp:2868:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (0,127.0.0.1,15432,0) , FD (Conn:97 , MyDS:97) , user postgres , last_used 430ms ago : 57P01, terminating connection due to administrator command
2025-05-14 08:50:47 PgSQL_Session.cpp:2881:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2025-05-14 08:50:47 PgSQL_Connection.cpp:528:handler(): [ERROR] Error: [57P01] terminating connection due to administrator command, Multi-Statement: 0
2025-05-14 08:50:47 PgSQL_Session.cpp:2868:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (0,127.0.0.1,15432,0) , FD (Conn:95 , MyDS:95) , user postgres , last_used 457ms ago : 57P01, terminating connection due to administrator command
... // Query retrials due to the sudden server shutdown still ongoing
2025-05-14 08:50:47 PgSQL_HostGroups_Manager.cpp:266:connect_error(): [ERROR] Shunning server 127.0.0.1:15432 with 5 errors/sec. Shunning for 10 seconds
... // Monitor fails to connect to the old primary
2025-05-14 08:50:48 PgSQL_Monitor.cpp:642:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='Connection refused Is the server running on that host and accepting TCP/IP connections?'
2025-05-14 08:50:48 PgSQL_HostGroups_Manager.cpp:3673:read_only_action_v2(): [INFO] read_only_action_v2() detected RO=0 on server 127.0.0.1:15433 for the first time after commit(), but no need to reconfigure
... // Monitor keeps attempting the connections to the server at ping intervals and reporting the errors, since the old primary is still down
2025-05-14 08:50:49 PgSQL_Monitor.cpp:642:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='Connection refused Is the server running on that host and accepting TCP/IP connections?'
2025-05-14 08:50:49 PgSQL_Monitor.cpp:642:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='Connection refused Is the server running on that host and accepting TCP/IP connections?'
... // Server gets SHUNNED by the number of errors/sec
2025-05-14 08:50:57 PgSQL_HostGroups_Manager.cpp:266:connect_error(): [ERROR] Shunning server 127.0.0.1:15432 with 5 errors/sec. Shunning for 10 seconds
2025-05-14 08:50:57 PgSQL_Monitor.cpp:642:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='Connection refused Is the server running on that host and accepting TCP/IP connections?'
2025-05-14 08:50:57 PgSQL_Monitor.cpp:642:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='Connection refused Is the server running on that host and accepting TCP/IP connections?'
2025-05-14 08:50:58 PgSQL_Monitor.cpp:642:handle_async_connect_cont(): [ERROR] Monitor connect failed addr='127.0.0.1:15432' error='Connection refused Is the server running on that host and accepting TCP/IP connections?'
Let’s check the server status and hostgroup distribution, and compare it with what we previously learned about servers placement regarding read-only monitoring:
radmin=# SELECT * FROM runtime_pgsql_servers;
hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment
--------------+-----------+-------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------
0 | 127.0.0.1 | 15432 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | pg-primary
0 | 127.0.0.1 | 15433 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
1 | 127.0.0.1 | 15433 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | pg-replica
(3 rows)
radmin=# 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
-----------+-----------+----------+---------+----------+----------+--------+---------+-------------+---------+-----------------+-----------------+------------
0 | 127.0.0.1 | 15432 | SHUNNED | 0 | 0 | 17 | 10 | 16 | 72424 | 6056378 | 331843637 | 137
0 | 127.0.0.1 | 15433 | ONLINE | 0 | 8 | 8 | 0 | 8 | 21624 | 1483251 | 136005016 | 202
1 | 127.0.0.1 | 15433 | ONLINE | 0 | 16 | 16 | 0 | 16 | 371640 | 16200232 | 829889526 | 202
(3 rows)
The previous primary (pg-primary) have been SHUNNED due to the continuous connections errors that experienced, and has been left in this state, since monitoring actions are still unable to connect. Meanwhile, the previous replica (pg-replica) has been moved to both hostgroups (0 and 1), since now it’s no longer acting as a replica, and the transition in its read-only value (from 1 to 0) has been detected by ProxySQL. Like we previously demonstrated, when this transition is detected by ProxySQL, and pgsql-monitor_writer_is_also_reader is true, the server is placed in both reader and writerhostgroups.
We have achieved a successful fail-over 🎉! The servers status have been modified and traffic is being properly routed in for both hostgroups 0 and 1 towards the new primary (old replica). Our simulated unplanned fail-over was detected and contained, resulting in less than 1 second of traffic interruption 🎊 🎊 🎉🎉!
Conclusion
We have demonstrated the power and flexibility of ProxySQL in monitoring and managing PostgreSQL deployments. We’ve explored how to configure ProxySQL for basic monitoring, handle ping errors, and leverage read-only monitoring for replication setups. We even successfully simulated an unplanned failover, showcasing ProxySQL‘s ability to automatically detect the failure and redirect traffic to the new primary with minimal interruption.
Hopefully, you are now more familiar with many ProxySQL concepts and utilities to apply to your solutions, and as always, happy ProxySQLing!
