PostgreSQL

Replicate into PostgreSQL tables, including the tables and bookkeeping objects SQL Flow creates and the privileges its login needs.

A PostgreSQL target keeps a replica of the captured tables in a PostgreSQL database: the initial load copies the rows, and every captured change is applied by primary key.

Requirements

Permissions

SQL Flow doesn't check the login's privileges up front. A missing privilege appears as PostgreSQL's own permission error when the statement that needs it runs, and the pipeline stops.

The login needs:

• CONNECT and CREATE on the database. CREATE lets SQL Flow create its sqlflow bookkeeping schema, and the schemas of the tables it creates for you.
• TEMPORARY on the database, for the staging tables of Collapse row versions (faster). PostgreSQL grants it to every role by default.
• SELECT, INSERT, UPDATE, DELETE and TRUNCATE on the target tables. TRUNCATE is used by a reload and to apply a TRUNCATE captured from a PostgreSQL source.
• Ownership of the target tables, when SQL Flow creates them, adds a missing key, or changes columns for a schema change.

The simplest setup is a login that owns the target tables because SQL Flow created them. Replace <password> and <database_name>.

CREATE ROLE sqlflow_apply WITH LOGIN PASSWORD '<password>';
GRANT CONNECT, CREATE, TEMPORARY ON DATABASE <database_name> TO sqlflow_apply;

If the target tables already exist and belong to another role, also grant the table privileges. Replace <schema_name>.

GRANT USAGE ON SCHEMA <schema_name> TO sqlflow_apply;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA <schema_name> TO sqlflow_apply;

What SQL Flow creates on the target

Replicated tables

SQL Flow creates target tables only when you ask it to: with Prepare target schema on the wizard's Prepare step, or the same action when you add a target or change tables. Show SQL lists exactly the statements that will run, and Copy SQL copies them for a DBA.

Each missing table is created with a primary key on the source table's key columns, in a schema that is created if needed. An existing table without a matching key gets the primary key, or, if it already has a different primary key, a unique index named ux_<table>_<columns>. SQL Flow never adds columns to an existing table when it prepares it.

Table and column names follow the naming conventions for PostgreSQL, and column types follow Data type mapping.

If a table is missing when the pipeline starts, the load stops with Target table <name> does not exist in the target. Provision the target schema before starting the pipeline (the add-target / create-pipeline flow can create it).

Bookkeeping objects

SQL Flow keeps its own state in a schema named sqlflow, which it creates when it first needs it. It holds the committed source position, which lets a restarted pipeline, or another agent that takes over, resume from what the target holds, and the progress of the initial load. Don't change or drop these tables. Unlike on SQL Server targets, the schema name can't be changed.

Two other objects can appear outside that schema:

• __cdc_schema_review, in the login's default schema, records each applied schema-change review.
• With the postgresLargeObjectColumns option, the function __sqlflow_lo_lifecycle and triggers named __sqlflow_lo_lifecycle and __sqlflow_lo_truncate on the mapped tables manage the large objects.

Options

• Collapse row versions (faster). "Writes only each row's final state. Untick if a target trigger or audit must see every change." Even when it is ticked, SQL Flow applies every change in order for a table where collapsing isn't safe, for example a table with triggers, row-level security, or generated or identity columns.
• Atomic transaction limits. Apply stops if one source transaction exceeds either limit, which keeps the transaction intact for recovery rather than splitting it.
• Allow streamed large-object loads despite triggers. "Large values load as an INSERT plus one update each. Tick only if target triggers accept that."
• Encryption. Require (default) always uses TLS and validates the server certificate's chain and host name. Prefer uses TLS when the server offers it, otherwise connects unencrypted, and doesn't check the certificate. sslRootCertificate names a private certificate authority. See Logical replication: Encrypted data.
• postgresLargeObjectColumns. Stores the listed columns as PostgreSQL large objects (oid columns) instead of inline values. It applies only to a SQL Server DirectLog source with ordered apply. See Large values and LOBs.

Limitations

• Every target table needs a primary key or a unique constraint on the source key. Otherwise apply stops with Tracked table '<table>' (pipeline '<id>', target engine 'postgres') has no primary key or unique key resolved on the target, and no key columns were configured for it.
• A source column that PostgreSQL can't hold exactly is refused by name on the Prepare step, before any table is created: for example a SQL Server geography, geometry, sql_variant or CLR user-defined type column, or an Oracle FLOAT, JSON or TIMESTAMP WITH LOCAL TIME ZONE column. See Data type mapping.
• PostgreSQL truncates identifiers longer than 63 bytes. SQL Flow shortens long names, with a hash suffix, only for Oracle sources.
• A TRUNCATE captured from a PostgreSQL source is applied as TRUNCATE TABLE without CASCADE, so a foreign key that references the table stops apply rather than emptying other tables.

See also

• PostgreSQL source
• Prepare
• Naming conventions per target engine
• Initial load
• Schema changes