OneAnalytics docs

Connect PostgreSQL

PostgreSQL 12+ is a first-class source. We connect directly from our cloud over TLS — no gateway agent required unless the DB is inside a private network.

1. Create a read-only role

CREATE ROLE oa_reader LOGIN PASSWORD 'ChangeMe-Strong-1!';
GRANT CONNECT ON DATABASE mydb TO oa_reader;
\c mydb
GRANT USAGE ON SCHEMA public TO oa_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO oa_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO oa_reader;

ALTER ROLE oa_reader SET statement_timeout = '60s';
ALTER ROLE oa_reader SET idle_in_transaction_session_timeout = '30s';

2. Allowlist our egress IPs

OneAnalytics' cloud egresses from:

  • 10.20.0.129/32 (primary)
  • 10.20.0.130/32 (failover)

Add both to pg_hba.conf:

hostssl mydb  oa_reader  10.20.0.129/32  scram-sha-256
hostssl mydb  oa_reader  10.20.0.130/32  scram-sha-256

Reload: SELECT pg_reload_conf();

For private networks (VPC, on-prem), use the gateway agent instead — it punches out to us over a single mTLS 443 tunnel.

3. Add the connection

Sources → Add → PostgreSQL:

  • Host: db.example.com
  • Port: 5432
  • Database: mydb
  • Username: oa_reader
  • Password: (from step 1)
  • SSL mode: require (recommended) or verify-full if you upload the CA

Click Test — you should see connected, 42 tables visible.

4. Pick a mode

  • Import — rows stream into our columnar cache via COPY ... TO STDOUT BINARY. Fastest queries, costs you storage quota.
  • Direct — every query hits your DB. Always fresh, but query latency and concurrency depend on your instance.

Switch modes later from Dataset → Settings → Mode without re-configuring the connection.

CDC (Change Data Capture)

For large tables (> 10 M rows), enable logical replication and we'll stream changes instead of full refreshes. wal_level = logical, a publication, and a dedicated replication slot per dataset — full walkthrough in the UI's Enable CDC flow.

Troubleshooting

  • "SSL connection required" — Add sslmode=require or use SSL mode require in the UI.
  • "permission denied for schema foo" — We only see what oa_reader has SELECT on. Grant explicitly.