PostgreSQL
The PostgreSQL connector reads tables and stored procedure results, and writes rows to tables, in a self-hosted or managed PostgreSQL database.
When to use it
- You read from or write to PostgreSQL.
- You want the database to filter rows, with a SQL-mode query on the Source node.
- You need a staging table created and dropped around a load, with a DB Script node.
What you need
- The host name and port the runtime can reach.
- A database user and password. The user needs read access for Sources and write access for loads.
- For a verified TLS connection to a private or self-signed authority, its CA certificate in PEM form.
Connect
The connection form lists its steps on the left. Open any step from that list. The Environments step holds the values that differ per environment.
Test connection is in the form footer. Beside it, Test against picks Base, the connection as saved, or an environment, whose values are applied first.
| Field | Step | What to enter |
|---|---|---|
| Username | Authentication | The database user. |
| Password | Authentication | The user's password. Required to connect. |
| Host | Connection | Host name or IP address. |
| Port | Connection | Default 5432. |
| Database | Connection | The database name. |
| Schema (Optional) | Connection | The default schema. Blank uses public. |
| SSL Mode | Connection | Disable, Require (encrypt, no verification), Verify CA or Verify full. Default Disable. |
| CA Certificate | Connection | A PEM certificate for Verify CA or Verify full with a private or self-signed authority. |
| Connect Timeout (ms) | Options | Default 30000. |
| Pool Max | Options | Maximum open connections. Default 10. |
| Note | Options | Free text for your team. |
Require encrypts the connection without checking the certificate. Verify CA checks that a trusted authority signed it. Verify full also checks that the host name matches.
Supported operations
| Action | Node | Operations |
|---|---|---|
| Table Query (Source) | Source | Query, in No-code or SQL mode |
| Table Load (Target) | Load | Insert, Update, Upsert, Delete |
| Stored Procedure (Source) | Source | Execute |
| Stored Procedure (Target) | Load | Execute |
| DDL Script | DB Script | Create, Alter, Drop, Truncate, Rename |
Limits
- A table with a single-column primary key is read by seeking that key. Other objects are read in a fixed order with an offset, which is slower on a large table.
- Upsert, Update and Delete loads stage rows in a temporary table and apply them in one transaction.
- Load batches are sized by bind count and by bytes.
Troubleshooting
- Test connection fails with correct credentials. The database must accept connections from the runtime that runs the data pipeline, not from your computer. For a database on a private network, run a Private Agent inside that network or allow-list the runtime's address.
- The test passes but Configure Action lists no objects. The user can sign in but cannot see the catalog. Grant it read access on the schema, then press Get Metadata again.
- A column is missing from the field list. The list is read when metadata is loaded. Press Get Metadata again to read the current columns.
- A load fails on one batch. The run log names the statement and the batch. Check the rows in that batch against the column types and lengths.
- SSL errors with Verify CA or Verify full. The server certificate is signed by an authority the runtime does not trust. Paste that authority's PEM into CA Certificate.
Related
- Source node, Load node and DB Script node.
- Connections for environments and sharing.
- MySQL, SQL Server, Oracle and Snowflake.