DBTrail
Queries from PostgreSQL

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 interface

ProxySQL 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.

You should see

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 interface

ProxySQL 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.

You should see

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 admin

Hostgroup 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 admin

The 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 admin
INSERT 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 application

Host and port change to ProxySQL's (6133 by default). Username, password and database stay the same.

You should see

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 \bind and 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 = 100

userlist.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.

On this page