From your PostgreSQL client
Run reports on the copy of a PostgreSQL server from psql, pgx, psycopg or JDBC, on port 5433, without touching your PostgreSQL.
Point psql, a driver or a BI tool at port 5433, and your queries run on
DBTrail's copy of a PostgreSQL server instead of on the server itself. Your
PostgreSQL does no work for them. This port is new in 0.133.0, and PostgreSQL
support is in beta.
How current an answer is, and who answers it:
- About 1 to 3 seconds behind your PostgreSQL, in fresh mode. Fresh mode is the default, new in 0.134.0: each statement lays the changes captured since the last snapshot over the copy. Measured: 1.2 seconds behind in the middle, 3.2 at most, with 2 and with 10 million rows. It costs more: 1.7 to 3.6 times the time of the same statement in fast mode.
- As old as the last snapshot update, in fast mode. The cheaper answer,
chosen per server on its Snapshots page or with
dbtrail port mode <server> fast. A connection already open when a server goes from fresh to fast gets a notice with its next answer: from then on it reads older data than it just did. - The copy answers, unless the server has a forwarding user. With one, a
statement the copy is not known to answer the way PostgreSQL does goes to
your PostgreSQL, read-only, and so does a transaction block (
BEGIN...COMMIT). Without one, the copy answers alone, and a statement it cannot answer is an error. Without a forwarding user, a lookup of one row by its key reads the whole table on the copy, which has no indexes: on a big table that is slower than on your PostgreSQL. - Reports stay on the copy with a forwarding user. Since 0.136.0 the copy
answers, as PostgreSQL does,
round(a / b, k),round(avg(x), k)andfloor(a / b)over numerics (50.00, not50.0),ntile, and names a query gives its own numbers (SUM(x) AS units, a column of aWITH) compared or sorted. Of 14 TPC-C reporting queries, 11 stay on the copy; the other 3 sort text or useto_timestamp, and go to your PostgreSQL.
1. Turn the port on
In the web interface: Settings → MySQL clients → Turn on, in the
Connect a SQL client panel. The page keeps its name with PostgreSQL
servers, and the panel shows the psql line to copy. Or in a terminal:
dbtrail port onEither way you get a password, shown only then. It is the same switch and the same password as the MySQL port's.
2. Connect
The database name picks the server: its id or its name, as the web interface shows them. The username can be anything.
psql "host=127.0.0.1 port=5433 dbname=shop user=app sslmode=require"SELECT status, count(*) AS n FROM orders GROUP BY status ORDER BY n DESC;The answer, from the copy. Your PostgreSQL ran nothing.
From an application, the usual connection strings work:
pgx (Go) postgres://app:PASSWORD@127.0.0.1:5433/shop?sslmode=require
psycopg 3 host=127.0.0.1 port=5433 dbname=shop user=app password=PASSWORD sslmode=require
JDBC jdbc:postgresql://127.0.0.1:5433/shop?sslmode=require (user app)Limits
Small on purpose, so the port fits beside capture.
Results waiting for a client to read them are held in memory, up to 256 MB
for all clients together. Past that, the next statement is refused with error
53000; run it again once the others have read their answers. A client that
reads nothing of a result for 60 seconds is disconnected.
One connection, with ProxySQL
Your application keeps one connection and its own MySQL login. ProxySQL sends writes and transactions to your MySQL, and reads to DBTrail, which answers the heavy ones from the copy.
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.