Skip to main content

​
Connect PostgreSQL with Statisfy

The PostgreSQL integration lets you import data from any PostgreSQL-compatible database into Statisfy — your application database, an analytics replica, or any Postgres-wire-compatible engine. Author SQL queries in the Statisfy settings UI, preview results, and schedule them. PostgreSQL uses the same self-serve configuration model as the other warehouse integrations. You can find the shared concepts — query types, account resolvers, transformers, schedules, configuration history — in Integration Concepts. This page focuses on what’s specific to PostgreSQL.

​
Video Tutorial

Prerequisites:
  • Admin access to Statisfy
  • A PostgreSQL instance reachable from Statisfy’s egress IPs (see Network Access)
  • A database user with SELECT access on the schemas/tables you want to sync

​
What You Can Import

​
Set Up the Database User

Run as a Postgres superuser (or a user with CREATE ROLE):
Replace analytics and public with the database and schema(s) you want Statisfy to read.
If you can, point Statisfy at a read replica rather than your primary database. Sync queries can be large; a replica isolates that load from your live application traffic.

​
Steps to Connect

  1. Log in to Statisfy.
  2. Navigate to Integrations → Admin Apps → PostgreSQL → Connect.
  3. Enter your connection details:
    • Host — DNS name or IP of the database
    • Port — Default 5432
    • Database — Database name
    • Username — statisfy_reader (or whichever read-only user you created)
    • Password — The user’s password
    • SSL Mode — require is recommended; use verify-full if you’ve installed your CA cert
  4. Click Connect.
  5. Once connected, PostgreSQL will appear as Connected on the Integrations page. To confirm Statisfy can reach PostgreSQL and authenticate, click the gear (Settings) on the card, then Test Connection on the Connection card.

​
Configure Queries

Open Integrations → PostgreSQL → Settings to:
  • Browse schemas and tables the user can see, with column types
  • Author queries using Postgres SQL with autocomplete and a preview pane
  • Map results to Statisfy accounts (via Statisfy account ID, CRM ID, organization ID, email domain, account name, or any custom field) and to fields, metrics, or custom objects
  • Apply column transformers (strip_prefix, regex_extract, lookup, etc.) to reshape values before they’re written
  • Set the cadence — daily, weekly, monthly, or a sub-day interval (HOURLY through TWELVE_HOURS)
  • Run a one-off sync on demand
Each saved query is one of three types — product_usage, custom_field, or custom_object. See Query types, Account resolvers, and Query configuration dialog for the shared model and how to write, preview, and map a query. If a schema and table allowlist is saved, Statisfy only lets you save, preview, and browse data inside it. After each sync, check Recent runs for how many rows each query wrote, failed, didn’t match, had nothing to write, or filtered out. See Recent runs.

​
PostgreSQL-Specific Notes

  • Date placeholders. Use {START_DATE} / {END_DATE_SQL} in WHERE clauses to keep queries incremental. Postgres’s DATE_TRUNC(), INTERVAL, and ::date casts compose naturally with these.
  • Schema search path. Statisfy does not rely on search_path — queries should fully qualify tables (schema.table) when they live outside public.
  • Read isolation. For long-running scans, consider SET TRANSACTION ISOLATION LEVEL REPEATABLE READ in the query if you need a consistent snapshot — but in most cases the default isolation is fine.

​
Account Matching

Rows that don’t resolve to an existing Statisfy account are skipped. For CRM ID resolvers, make sure your CRM integration has synced the matching accounts first.
For custom_field queries that target people instead of accounts, configure a person resolver (email column, optionally name) — Statisfy will upsert the person record by email.

​
Sync Behavior

Scheduled Jobs:
  • Each saved query runs on its own cadence.
  • The first run after connecting is a bootstrap covering the last 30 days; subsequent runs cover only the active window for the cadence.
  • Self-serve configuration takes precedence over any built-in fallback set up by support.
Error Handling:
  • Per-query errors are logged but do not block other queries from running.
  • Invalid rows within a query are skipped and reported in the job audit log.

​
Network Access

If your Postgres host has IP allowlisting or is in a private network, allow Statisfy’s egress IPs:
Statisfy only connects to hosts that resolve to a public IP address. If the host resolves to a private or other non-public address, Statisfy refuses the connection. For a database in a private network, expose it through a publicly resolvable host and restrict inbound traffic to the egress IPs above. If you can’t expose a publicly resolvable host, reach out to support@statisfy.com to discuss VPN / SSH-tunnel options.

​
Security

  • Credentials are stored encrypted in Google Cloud Secret Manager.
  • All connections use TLS — Statisfy refuses to connect over plaintext when ssl_mode is set to require or higher.
  • Queries authored in the Settings page are read-only — Statisfy validates each query and rejects DDL/DML before save.
  • Access is scoped to the database user you provide — grant SELECT only on the schemas you want Statisfy to read.

​
Troubleshooting

Check network access:
  • Verify the host is reachable from the Statisfy egress IPs above
  • Confirm the firewall / security group allows inbound traffic on the Postgres port (default 5432)
  • Confirm the database accepts SSL connections (check pg_hba.conf)
Check credentials:
  • Verify host, port, database, username, and password
  • Confirm the user has CONNECT privilege on the database
  • Confirm the database user has USAGE on the schema and SELECT on the tables
  • System schemas (pg_catalog, information_schema, pg_toast) are intentionally hidden
  • New grants may require closing and reopening the schema picker
  • Statisfy validates queries against the Postgres dialect — vendor-specific functions from other warehouses will be flagged
  • DDL (CREATE, DROP, ALTER) and DML (INSERT, UPDATE, DELETE) are rejected — only read-only queries are allowed
  • Check the validation error for the offending function or syntax
  • Verify the resolver column contains values that exist in Statisfy
  • For CRM ID resolvers, confirm your CRM integration has synced the matching accounts
  • Try a different resolver (switch from account name to email domain) if matching is unreliable
  • Add a regex_extract or strip_prefix transformer if the column contains the right ID with extra characters
  • Point Statisfy at a read replica instead of the primary
  • Lower the cadence to DAILY so each run scans a smaller window
  • Use {START_DATE} / {END_DATE_SQL} to keep queries incremental
  • Add indexes on the columns you filter on

​
Need Help?

For initial setup, query design, or troubleshooting, contact support@statisfy.com.