Documentation

    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

    1. In Strata, select an Input block to read, or an Output block to write.
    2. Set Source to Connectors. On an Output block the same control is Destination.
    3. In the connector dropdown below it, choose Postgres.
    4. Open the Connection dropdown and choose Create new connection.
    5. 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.
    6. Set Name (optional). Without a name, the connection is labeled host/database.
    7. 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

    SettingWhat it does
    SchemaDefault public.
    Table or viewThe table or view to read.
    ColumnsAll columns or Selected columns.
    Date column, From and ToOptional. 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 TRUNCATE and 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

    MessageWhat to do
    PostgreSQL: select at least one columnChoose All columns, or select at least one column.
    PostgreSQL: column <name> is unavailable; refresh available columnsThe table changed. Refresh the list and select the columns again.
    PostgreSQL: choose a date column before setting datesPick a Date column, or clear From and To.
    PostgreSQL: From must not be after ToFix the date range.
    PostgreSQL: selected table is unavailable; refresh and check permissionsCheck the table exists and the user has SELECT.
    PostgreSQL: selected table has no readable columns; refresh and check SELECT permissionsGrant SELECT on the table.
    PostgreSQL: arrays with non-default bounds cannot be represented without losing informationLeave the column out, or convert it in the database.
    PostgreSQL: destination is not a writable tablePick a table, not a view.
    PostgreSQL: replace is unavailable while row-level security applies to this connectionUse 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 StrataConvert or drop the column before Output.
    PostgreSQL: <name> cannot be written as <type> without changing its value; convert it explicitly in StrataConvert the column before Output.
    port must be a numberFix the port, then save again.
    host is requiredFill in the host.
    PostgreSQL: empty replacement is blocked; existing data was preservedThe 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 creditsThe 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.