Postgres
Read PostgreSQL tables and views into a Strata flow, and write results to tables. Works with PostgreSQL 14 to 18.
Before you start
- A host and port that accept connections from outside your network, a database name, a user and a password.
- Connections come from Oplema's servers, not your browser. This also applies when the rest of your flow runs locally. A database behind a firewall or allowlist must accept them. Oplema doesn't promise a fixed IP address and there is no SSH tunnel or private link; contact us before you plan around an allowlist.
- A dedicated user for Oplema, with the permissions below. See PostgreSQL's GRANT. Your server's pg_hba.conf must also allow the login.
-- Read
GRANT USAGE ON SCHEMA public TO oplema;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO oplema;
-- Write: Append rows and Replace
GRANT INSERT ON public.orders TO oplema;
-- Write: Replace also runs TRUNCATE
GRANT TRUNCATE ON public.orders TO oplema;
-- Write: Create new table
GRANT CREATE ON SCHEMA public TO oplema;Connect
- In Strata, select an Input block to read, or an Output block to write.
- Set Source to Connectors. On an Output block the same control is Destination.
- In the connector dropdown below it, choose Postgres.
- Open the Connection dropdown and choose Create new connection.
- Fill in Host, Port (default 5432), Database, User and Password. The password is shown as you type. Select Use SSL to encrypt the connection. It is off by default, and without it traffic is not encrypted. There is nowhere to upload a private CA, so a server certificate from a private CA may fail.
- Set Name (optional). Without a name, the connection is labeled
host/database. - Select Save connection.
The new connection is selected. Saving only checks the format of what you typed. It doesn't sign in, so wrong details first show up as an error when the pickers below load.
A saved connection can't be edited. To change a password, token or any other field, open the Connection dropdown, choose Delete (the ×) on its row, and create the connection again. Blocks that used the deleted connection must then select the new one.
Read
| Setting | What it does |
|---|---|
| Schema | Default public. |
| Table or view | The table or view to read. |
| Columns | All columns or Selected columns. |
| Date column, From and To | Optional. These are inclusive whole days. Zoned timestamps use UTC. |
Select Refresh data to read again. A read uses one read-only snapshot with no row limit.
Write
Pick Schema, then Table.
- Create new table: enter a Table name, default
output. - An existing table: set Write to Replace or Append rows. Replace runs
TRUNCATEand inserts the new rows in one transaction. It keeps the table's columns and identity sequence, and it fails if row-level security applies to the login or other tables reference this one with foreign keys.
Output writes when you run the flow. Choosing tables in the panel never writes. If you pick Create new table and a table with that name already exists, the run fails. Statements time out after 30 seconds, and a whole write after 180.
Limits and billing
- Reads: the first 50 MB of each download is free. After that, each download costs $0.01 per GB from your usage. Failed reads aren't charged, and a read stops when your usage runs out.
- Writes: the first 50 MB of each write is free. After that, each write costs $0.01 per GB from your usage, and a write stops when your usage runs out.
See Billing for who pays and how usage works.
Troubleshooting
| Message | What to do |
|---|---|
PostgreSQL: select at least one column | Choose All columns, or select at least one column. |
PostgreSQL: column <name> is unavailable; refresh available columns | The table changed. Refresh the list and select the columns again. |
PostgreSQL: choose a date column before setting dates | Pick a Date column, or clear From and To. |
PostgreSQL: From must not be after To | Fix the date range. |
PostgreSQL: selected table is unavailable; refresh and check permissions | Check the table exists and the user has SELECT. |
PostgreSQL: selected table has no readable columns; refresh and check SELECT permissions | Grant SELECT on the table. |
PostgreSQL: arrays with non-default bounds cannot be represented without losing information | Leave the column out, or convert it in the database. |
PostgreSQL: destination is not a writable table | Pick a table, not a view. |
PostgreSQL: replace is unavailable while row-level security applies to this connection | Use Append rows, or a login that bypasses row-level security. |
PostgreSQL: missing required column <name> | Add the column in Strata before Output. |
PostgreSQL: incompatible or generated destination column <name>; convert it explicitly in Strata | Convert or drop the column before Output. |
PostgreSQL: <name> cannot be written as <type> without changing its value; convert it explicitly in Strata | Convert the column before Output. |
port must be a number | Fix the port, then save again. |
host is required | Fill in the host. |
PostgreSQL: empty replacement is blocked; existing data was preserved | The incoming table has no rows. Check the flow upstream. |
PostgreSQL: write completion could not be confirmed. Check the destination before running again. | The connection dropped during the write. Look at the table first, because a retry could write the rows twice. |
This transfer exceeds the free 50 MB and the available usage credits | The read or write passed 50 MB and your usage ran out. Add usage, or move less data. |
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.