One connection, with ProxySQL
Your application keeps one connection and its own PostgreSQL login. ProxySQL sends writes and transactions to your PostgreSQL, and reads to DBTrail, which answers the heavy ones from the copy.
Nothing changes in your application and nothing changes in your PostgreSQL. ProxySQL is what makes the single connection possible: it sends each statement where it belongs, because DBTrail's PostgreSQL port answers reads only.
Before you start
Have this at hand
- DBTrail with a PostgreSQL server and a forwarding user
- The server added, its first copy ready, and a forwarding user set with
dbtrail server forward-user: see From your PostgreSQL client. - DBTrail 0.137.0 or newer
- The PostgreSQL port takes the monitor login from 0.137.0 on.
- ProxySQL 3.1 or newer
- Any install. This guide uses its PostgreSQL admin interface on port 6132, with psql.
- Your application's PostgreSQL username and password
- The ones it uses today. This guide calls them shop_app and app-password.
- A monitor role
- The username and password ProxySQL checks your PostgreSQL with (pgsql-monitor_username and pgsql-monitor_password). This guide calls them monitor and monitor-password.
- The name of your server in DBTrail
- As the left sidebar shows it. This guide calls it
shop.
One forwarding user per server, with your application's grants and no more. A read DBTrail passes on to your PostgreSQL runs as the forwarding user, so its grants, not your application's, decide what the application can read through the port.
Give DBTrail your application's login
DBTrail web interfaceProxySQL logs into every backend with the username and password your application logged into ProxySQL with, so the port has to take that login.
Pick shop in the left sidebar, open MySQL clients, and under
A proxy in front, save the application's username and password as the
Application login. The same login opens shop on the PostgreSQL port
and on the MySQL port, whatever database the connection names, and no other
server.
psql "host=dbtrail-host port=5433 dbname=shop user=shop_app" with the
application's password logs in and answers SELECT count(*) of a table.
Give DBTrail ProxySQL's monitor login
DBTrail web interfaceProxySQL checks every backend with one monitor username and password, your PostgreSQL included. On the same card, save that pair as the Monitor login: one pair for the MySQL port and the PostgreSQL port. It opens no server.
A connection opened with it answers what ProxySQL's monitor sends and nothing
else: its ping (an empty query), SET, and SELECT pg_is_in_recovery(),
which is true on the port. Any other statement is refused.
psql "host=dbtrail-host port=5433 dbname=postgres user=monitor" -c "SELECT pg_is_in_recovery()"
answers t.
Without it, every check of the port is refused, and ProxySQL's log and
DBTrail's both say password authentication failed for user "monitor" every
few seconds.
Add both servers to ProxySQL
ProxySQL adminHostgroup 10 is your PostgreSQL, hostgroup 20 is DBTrail.
INSERT INTO pgsql_servers (hostgroup_id, hostname, port, comment) VALUES
(10, 'postgres-host', 5432, 'postgres'),
(20, 'dbtrail-host', 5433, 'dbtrail');
LOAD PGSQL SERVERS TO RUNTIME; SAVE PGSQL SERVERS TO DISK;If you use pgsql_replication_hostgroups, DBTrail stays in the reader
hostgroup: pg_is_in_recovery() is true on the port, as on a standby.
Do not set max_replication_lag on DBTrail's row: DBTrail does not answer
the replication-lag check.
Add your application's user
ProxySQL adminThe same login as in step 1. Its default hostgroup is your PostgreSQL, and
transaction_persistent keeps a transaction where it started.
INSERT INTO pgsql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('shop_app', 'app-password', 10, 1);
LOAD PGSQL USERS TO RUNTIME; SAVE PGSQL USERS TO DISK;Send reads to DBTrail
ProxySQL adminINSERT INTO pgsql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT .* FOR (UPDATE|SHARE)', 10, 1),
(2, 1, '^SELECT ', 20, 1);
LOAD PGSQL QUERY RULES TO RUNTIME; SAVE PGSQL QUERY RULES TO DISK;Everything the rules don't match, writes included, goes to hostgroup 10. DBTrail answers the heavy reads from the copy and passes the others to your PostgreSQL as the forwarding user.
Point your application at ProxySQL
In your applicationHost and port change to ProxySQL's (6133 by default). Username, password and database stay the same.
In ProxySQL's admin, SELECT hostgroup_id, port, status FROM runtime_pgsql_servers;
shows both servers ONLINE,
SELECT port, ping_error FROM pgsql_server_ping_log ORDER BY time_start_us DESC LIMIT 4;
shows pings with no error, and
SELECT hostgroup, count_star, digest_text FROM stats_pgsql_query_digest;
shows the reads on hostgroup 20 and the writes on 10.
What reads the copy, and what doesn't
The copy is a few seconds behind your PostgreSQL. In the test behind this
guide, a row written through ProxySQL was in the answer to a read two
seconds later, and not before. A read that must see a write that just
happened belongs in the same transaction (BEGIN ... COMMIT), which
transaction_persistent keeps on hostgroup 10, or on a rule that keeps it
there (by username, database or a /* comment */ your application
adds).
Good to know
- ProxySQL answers
SELECT pg_backend_pid()itself, with its own session number: the statement reaches neither DBTrail nor your PostgreSQL. It does the same in front of any PostgreSQL. - Prepared statements work through ProxySQL:
psql's\bindand psycopg's server-side prepared statements were answered by DBTrail with their arguments. - Changing a password in DBTrail closes the connections opened with the old one. ProxySQL opens new ones with the password it has, so change it in ProxySQL too.
What was measured: ProxySQL 3.1.12, PostgreSQL 16, DBTrail on 4 cores, a writer, reports, transactions and prepared statements through one ProxySQL connection, and the monitor with and without replication hostgroups. A check that each piece works, not a load test.
With PgBouncer
PgBouncer pools connections, but it does not choose a server for each statement as ProxySQL does. Give it two databases: one on your PostgreSQL for the application's writes, and one on DBTrail's port for its reports.
[databases]
shop = host=postgres-host port=5432 dbname=shop
shop_reports = host=dbtrail-host port=5433 dbname=shop
[pgbouncer]
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_prepared_statements = 100userlist.txt holds the application's username and password, the same pair
saved as the server's Application login in DBTrail (step 1 above).
The reports connect to shop_reports.
Measured with PgBouncer 1.22 in session and in transaction mode: reads,
reports, SET, transaction blocks and prepared statements (psql's \bind
and psycopg's server-side ones) answered through the DBTrail pool. A write
sent to it is refused, since DBTrail's port is read-only:
this port is read-only toward the source: UPDATE is not a read, and is not sent.