docs / destinations / postgres

Postgres

The conformance oracle. `ON CONFLICT` merge, transactional.

Every destination receives the same layout; see the tables Ingest creates.

Setup

Ingest connects as a normal Postgres user over TLS and needs to own its own schema: create tables in it, write and read them, alter them when your source adds a column, and drop the scratch schema the connection test makes. It never needs superuser, and it never touches schemas you do not grant.

Create a role for it

Run this as a user that can create roles, substituting your own password and database name:

CREATE ROLE ingest LOGIN PASSWORD 'a-long-random-password';
GRANT CONNECT ON DATABASE your_db TO ingest;
GRANT CREATE ON DATABASE your_db TO ingest;

CREATE ON DATABASE is the one that matters and the one usually missed: Ingest creates a schema per dataset, and a role without it connects perfectly and then fails on the first write. If you would rather create the schema yourself and keep database-level creation off, do that and grant instead:

CREATE SCHEMA your_dataset AUTHORIZATION ingest;

Owning the schema is simpler than enumerating table grants, because Ingest creates tables as it goes and per-table grants cannot be given in advance. If your policy forbids ownership, GRANT USAGE, CREATE ON SCHEMA your_dataset TO ingest plus ALTER DEFAULT PRIVILEGES is the equivalent.

Reachable from the internet

Ingest runs on its own fleet and egresses from a public address; it has no route into your VPC. The endpoint has to be reachable from the internet, and your security group or pg_hba.conf has to allow Ingest's egress address. A destination on a private address fails the connection test with that as the stated reason rather than a generic timeout.

What to paste

Connection string: postgresql://ingest:password@host:5432/your_db?sslmode=require. It is stored as a secret, in your own namespace, and never returned to the browser.

Then test it

Test connection creates a scratch schema and a table, writes a few rows through the same load path a real run uses, reads them back to check the count is exactly right, round-trips pipeline state, and drops the lot. That proves the CREATE grant and the write, rather than just the password.

Staging

needs_staging: none. Staging always happens in the destination's own cloud and region; data does not take a detour through ours.

Conformance

Every adapter passes the same suite before it ships: create a table with every canonical type, append 100k rows, atomic replace, merge (latest dedup_sort wins; child tables delete-then-insert by root id), schema evolution, NULL-key rejection, package replay without duplicates, state and schema round-trip, row-count read-back, identifier edge cases, double-underscored table names (__quarantine, child tables), and rollback on a mid-load failure.