DBTrail
Queries from PostgreSQL

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) and floor(a / b) over numerics (50.00, not 50.0), ntile, and names a query gives its own numbers (SUM(x) AS units, a column of a WITH) compared or sorted. Of 14 TPC-C reporting queries, 11 stay on the copy; the other 3 sort text or use to_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 on

Either 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;
You should see

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.

ReadsonlyA statement that is not a read is refused with an error. Nothing here can change your data.
10,000rowsThe default cap. A bigger result is refused, not cut: add a LIMIT. A very long cell is refused the same way.
5 minper statementThe port stops a statement that runs longer. A shorter statement_timeout is applied too.
2at onceStatements that run on the copy at once, with 2 threads each. The rest wait their turn. 16 clients can connect.

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.

On this page