QueryPilot

Symptom-first fixes for the failure modes operators actually hit. The Router is the SQL proxy (binary querypilot); Analytics is the analytics server (binary querypilot-analytics). Port conventions used throughout: Router wire 5433, metrics 9090, admin API 9091 (always metrics port + 1); Analytics wire 15432, HTTP API 8080 (18080 in Compose), metrics 9090 (19090 in Compose). Most "cannot reach it" reports trace back to probing the wrong port.

Startup failures

The open-proxy refusal

With no pool credentials configured, the Router accepts any client credentials, so it refuses to put a listener on a non-loopback address. The error is explicit:

refusing to start: no client authentication is configured but listener(s)
[...] bind a non-loopback address, which would expose an open proxy.
Set username/password on a pool to require auth, bind listeners to loopback,
or set `[general] allow_anonymous = true` to deliberately run without auth.

Fix one of three ways: set username/password on a pool (these double as the client credentials), bind listeners to 127.0.0.1, or set allow_anonymous = true to deliberately run without auth after reading the security page.

A config setting has no effect

Which schema are you in? The components treat mistakes differently:

• Router (querypilot.toml): strict. Unknown keys fail at startup. If the Router started, your key was at least spelled correctly; check you edited the file the Router actually loaded (--config flag, then QUERYPILOT_CONFIG, then ./querypilot.toml) and see GET /api/config/status for the live config path.
• Analytics (querypilot-analytics.toml): lenient except [replicator]. Typos in [server], [duckdb], [logging], [metrics], or at the top level are silently dropped; the misspelled key becomes its default with no diagnostic. Only [replicator] rejects unknown keys. Re-check spelling character by character and run --check.

Related trap: feeding a Router config to querypilot-analytics does not fail. The unknown sections ([general], [[listeners]], [pools.*]) are silently ignored and the server boots on pure defaults. Analytics's schema is exactly [server], [duckdb], [logging], [metrics], [replicator]; if the file you are editing contains [[listeners]], it is not an Analytics config.

Connection and authentication failures

Routing surprises

The dry-run endpoint answers most routing questions without sending traffic:

curl "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20orders%20WHERE%20id%20=%201"

The response includes decision (translation, pattern, regex, default, or pinned), target_pool, fingerprint_hash, and normalized_sql. Under live traffic, sqp_routing_decisions_total split by decision_type and pool shows where queries actually go.

Pool exhaustion

Symptom: queries stall then fail after roughly acquire_timeout; sqp_pool_timeouts_total and sqp_pool_acquire_total{outcome="timeout"} increment.

Diagnose with the pool gauges: sqp_pool_utilization_ratio pinned near 1.0 with growing sqp_pool_queue_depth and sqp_pool_wait_duration_seconds means every connection is busy and sessions are queueing. Fixes, in order of preference:

1. Confirm the backend is not the real bottleneck (slow queries hold connections; check sqp_query_duration_seconds).
2. Raise max_connections on the pool if the backend has headroom.
3. With transaction pooling (pool_mode = "transaction", the default), size against concurrent transactions, not client count; look for applications holding transactions open (sqp_transaction_idle_seconds, sqp_transactions_active).
4. If connections churn (sqp_pool_acquire_total{outcome="created"} climbing alongside a short idle_timeout), raise min_connections or idle_timeout.

Also check pool status: a pool set to offline_soft or shunned via the admin API rejects all acquires (GET /api/pools shows current statuses).

Analytics

Configuration silently ignored

Symptom: querypilot-analytics starts cleanly but binds default addresses, has no replication, and an in-memory database.

A missing config file is not an error: if the path given by --config, QUERYPILOT_ANALYTICS_CONFIG, or the default ./querypilot-analytics.toml does not exist, the server boots silently on full defaults. A mistyped path therefore looks like a healthy server with none of your settings. Confirm the file you intend is the file being read:

querypilot-analytics --check -c /etc/sqp/querypilot-analytics.toml

Replication not progressing

Snapshot seems stuck or slow

Initial snapshots of large tables legitimately take tens of minutes; the shipped container healthcheck allows a 600-second start period for exactly this reason. Before assuming a hang:

curl http://localhost:8080/api/status              # phase + per-table progress
curl http://localhost:8080/api/snapshot/<table>    # rows done for one table

Progress is logged every 10 seconds by default and exported as sqp_snapshot_rows_total / sqp_snapshot_phase. If rows-done genuinely stops moving in an active phase, suspect source-side contention or scratch/target disk before the snapshot code. Levers when it is slow rather than stuck: max_parallel_snapshot, ctid_workers_per_table, and the parquet snapshot strategies (snapshot_strategy = "parquet" or "parquet_streaming"), which trade scratch disk (transiently about 2x source table size) for throughput on slow-fsync storage. Interrupted snapshots resume from checkpoints on restart on the paginated path.

WAL growth on the source

Symptom: the PostgreSQL source's disk fills with WAL.

A replication slot retains WAL until consumed or dropped, and Analytics deliberately keeps its slot across shutdowns for fast resume. Two cases:

• The instance is just stopped. WAL accumulates during downtime and is consumed on restart. Bounded by downtime; expected.
• The slot is orphaned. The instance was decommissioned, or pg_slot_name changed, and the old slot pins WAL forever. Find and drop it:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

SELECT pg_drop_replication_slot('<orphaned_slot>');

See Operations for the full slot lifecycle.

Reading logs

Turn up verbosity with the environment override, not RUST_LOG, which neither binary reads:

QUERYPILOT_LOG_LEVEL=debug querypilot
QUERYPILOT_ANALYTICS_LOG_LEVEL="sqp_duckdb_core=debug,info" querypilot-analytics

Precedence is --log-level flag, then the environment variable, then the config's logging.level. Set format = "json" under [logging] when shipping logs to a pipeline; pretty is for terminals. Both servers reload logging.level via POST /api/config/reload if you need debug logs without a restart. See environment variables.