Operations

This page covers running QueryPilot after deployment. The Router is the SQL proxy (binary querypilot); Analytics is the DuckDB analytics server (binary querypilot-analytics). Everything here assumes the metrics endpoint is enabled ([metrics] enabled = true), which serves Prometheus metrics on port 9090 and the Router's admin API on 9091 by default.

Config validation

Both binaries take --check: load the config, validate it, print the result, and exit without serving traffic.

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

The Router prints Configuration is valid on success and a parse or validation error otherwise. Its schema is strict: unknown keys fail. Run --check in CI or a deploy hook before rolling a config change; a config that passes --check will not fail startup on schema grounds.

For Analytics, --check also confirms that the file you intend is the file being read. A missing config file is not an error there; the server boots on defaults, so a mistyped path looks like a healthy server with none of your settings (see Troubleshooting).

Online config reload

Router

When the metrics endpoint is enabled, the Router serves a reload API on the admin port:

curl -X POST http://localhost:9091/api/config/reload
curl http://localhost:9091/api/config/status

Reloadable without a restart:

• routing.rules and routing.pattern_rules
• routing.read_write_split, routing.read_pool, routing.write_pool, routing.default_pool, routing.mysql_default_pool
• routing.cache.enabled, routing.cache.max_size
• [[translation.rules]] (translations are cleared and re-registered from config; translations registered at runtime via the API are ephemeral and do not survive a reload)
• logging.level

Not reloadable: listeners, pools, tls, and the rest of general. Those require a restart, and reloaded rules can only reference pools that existed at startup.

Reload validates before applying: the TOML must parse, regexes must compile, and pool references must exist. On failure it returns 400 identifying the offending rule and field, and the old config stays active. On success the change set is applied atomically and the routing-decision cache is cleared. The response reports what changed (rule counts, whether the read/write split, cache config, or log level moved).

Analytics

The same endpoints on the HTTP API port (default 8080) reload only logging.level. Everything else (server.*, duckdb.*, replicator.*) requires a restart. Adding and removing replicated tables is a separate runtime API, not a config reload:

curl -X POST http://localhost:8080/api/tables -H "Content-Type: application/json" -d '{"table": "new_table"}'
curl -X DELETE http://localhost:8080/api/tables/old_table
curl http://localhost:8080/api/tables

See the Analytics API reference for the full surface.

Graceful shutdown and connection draining

The Router shuts down on SIGTERM or Ctrl+C (docker stop and Kubernetes pod termination both send SIGTERM):

1. Listeners stop accepting new connections.
2. The Router waits for active client connections to drain, up to a fixed 5-second window.
3. Connections still open when the window expires are force-closed, with a warning log that includes the remaining count.
4. Plugins shut down last, after traffic has stopped.

Plan rolling restarts around the 5-second drain: with transaction pooling, in-flight transactions shorter than the window complete; long-running queries are cut off. Drain the pod from your load balancer before sending SIGTERM if you need zero interruption. The shutdown_timeout key under [general] parses but the drain window is fixed at 5 seconds in this release.

Analytics also shuts down on SIGTERM. It saves its replication checkpoint in DuckDB and deliberately keeps its PostgreSQL replication slot so the next start resumes streaming from the checkpoint without re-snapshotting. The slot lifecycle consequences are covered below.

Logging

Both servers configure logging from the [logging] section: level, and format = "pretty" (human-readable, for terminals) or "json" (one JSON object per line, for log pipelines). The provided deployment configs use json.

Override the level at launch without editing the config:

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

Precedence is CLI flag (--log-level) over environment variable over config file. The Router's config level is a closed list (trace/debug/info/warn/error); the env/CLI override and Analytics's config level accept full tracing filter directives such as per-module levels.

RUST_LOG is not read by either binary; exporting it does nothing. Both servers reload logging.level via POST /api/config/reload as described above. See environment variables.

Monitoring

Both components serve Prometheus metrics on GET /metrics at their [metrics] bind address. The full catalog is in the metrics reference; ready-made Grafana dashboards and alert rules are in Extras. Start by alerting on these:

Useful companions: sqp_pool_queue_depth and sqp_pool_wait_duration_seconds (leading indicators of exhaustion), sqp_queries_total and sqp_query_duration_seconds (traffic and latency baselines), sqp_routing_decisions_total by pool (verifies traffic splits the way you configured), and sqp_auth_failures_total.

Pool status management

Take a pool out of rotation without a restart, for example ahead of backend maintenance:

curl http://localhost:9091/api/pools                       # list pools and statuses
curl -X PUT http://localhost:9091/api/pools/replica/status \
  -H "Content-Type: application/json" -d '{"status": "offline_soft"}'

Statuses are online, offline_soft, and shunned. Both non-online statuses cause acquire attempts against the pool to fail immediately as if no healthy backends exist, so make sure routing has somewhere else to send that traffic before you flip a pool offline. Set {"status": "online"} to restore it. Status overrides live in memory and reset to online on restart. See the admin API reference.

Analytics operations

Snapshot progress

On a fresh start (or when tables are added), Analytics snapshots existing data before streaming changes. Track progress via the HTTP API:

curl http://localhost:8080/api/status              # replication phase + per-table progress
curl http://localhost:8080/api/snapshot/orders     # rows done for one table (404 once complete)

Progress is also logged every 10 seconds by default and exported as sqp_snapshot_rows_total, sqp_snapshot_phase, and related metrics (labeled by table). Initial snapshots of large tables legitimately take tens of minutes; the shipped container healthcheck allows a 600-second start period for this reason.

Checkpoint and resume

Analytics checkpoints its replication position in the DuckDB database. On restart after a graceful shutdown or a crash, it resumes streaming from the checkpoint instead of re-snapshotting; snapshots interrupted mid-run resume from their own checkpoints on the paginated path. A full re-snapshot happens only when there is no usable checkpoint, most commonly because the DuckDB file was deleted.

PostgreSQL replication slot lifecycle

Analytics creates one permanent, stable-named replication slot on the source PostgreSQL (default sqp_slot, configurable via pg_slot_name). Slot behavior by scenario:

The operational consequence: a replication slot retains WAL on the source until it is consumed or dropped. While Analytics runs, it consumes WAL continuously. While it is stopped, WAL accumulates behind the slot; that is the price of fast resume, and it is bounded by your downtime. But if you decommission an Analytics instance, or change pg_slot_name, without dropping the old slot, the orphaned slot pins WAL forever and will eventually fill the source's disk.

List slots and their retained WAL:

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

Drop an orphaned slot (only when no Analytics instance uses it anymore):

SELECT pg_drop_replication_slot('sqp_slot');

Do this whenever you tear down a replica setup permanently. When running multiple Analytics instances against the same PostgreSQL, give each a unique pg_slot_name.