Postgres

A Postgres source loads the tables of one database that match its include patterns, one table each, read with binary COPY into Arrow. To set one up and keep it up to date, see Load Postgres tables.

[connections.app]
kind = "postgres"
host = "db.example.com"
port = 5432
user = "loader"
password = "${ENV:APP_DB_PASSWORD}"
database = "shop"

[sources.app]
kind = "postgres"
connection = "app"
description = "The shop's application database."
include = ["public.*"]
exclude = ["public.audit_*"]
write_strategy = "merge"

[sources.app.tables."public.orders"]
description = "Orders, one row per order."
cursor_column = "updated_at"

Connection

A postgres connection takes host, port, user, password and database, the TLS settings, and an ssh table for an SSH tunnel. Every key and its default is in sqlload.toml. Postgres targets use the same connection.

Choosing tables

include and exclude are schema.table patterns (* matches any text). A table must match an include pattern and no exclude pattern. A [sources.app.tables."schema.table"] entry sets options for one table; on its own, it doesn’t add the table.

new_tables says what happens when a table that matches include appears after the source’s first load:

new_tables	A new table
sync (default)	Loads like the others.
skip	Is left out until include names it exactly, such as include = ["public.*", "public.refunds"].
fail	Stops the plan.

The first load is the one before any of the source’s tables exists in the destination, whichever tables are selected. Tables present then count as approved. A table that didn’t load then, such as one a problem blocked, counts as new afterwards, so name it exactly in include.

Choosing columns

Setting	Effect
exclude_columns	Column patterns to leave out.
allow_columns	Column patterns kept even when exclude_columns matches them.
skip_tables_with_columns	Leave out any table that has a column matching one of these patterns.
mask (per table)	Keep a column but replace its values with hash (SHA-256 hex) or hmac (HMAC-SHA256 hex, keyed by the source’s mask_key).

$ sqlload plan
Plan ready  1 selected, 1 object to create, 1 to load, 1 skipped

Objects (1)                                         sqlload apply
└── raw.app__customers                              create table
    ├── customer_id int    -> BIGINT
    ├── email       text   -> VARCHAR
    ├── country     text   -> VARCHAR
    ├── + _sqlload_load_id, _loaded_at
    ├── excluded  created_at (exclude_columns *_at)
    └── masked  email (hmac)

Loads (1)                                           sqlload load
└── app__customers                                  replace
    ├── source  postgres  app  public.customers
    ├── route   read as Arrow -> DuckDB takes Arrow
    └── batch   about 1 GB at a time into a new table, swapped in once at the end

Skipped (1)
└── public.orders
    └── reason  has column total, which matches skip_tables_with_columns

✓ Plan complete  1 selected, 1 object to create, 1 to load, 1 skipped

That plan comes from this source:

[sources.app]
kind = "postgres"
connection = "app"
destination = "local"
include = ["public.*"]
exclude_columns = ["*_at"]
allow_columns = ["updated_at"]
skip_tables_with_columns = ["total"]
mask_key = "${ENV:MASK_KEY}"

[sources.app.tables."public.customers"]
description = "Customers."
mask = { email = "hmac" }

hash is the same everywhere, but anyone can recompute it, so guessable values such as emails can be recovered. Use hmac for personal data. A masked column can’t be the cursor_column or part of the unique_key.

How Postgres is read

SQLLoad reads each table with binary COPY into Arrow. A table whose cursor_column leads an index is read in cursor order and commits about every 1 GB, between distinct cursor values. batch_size = "none" opts out, for tables where the ordered read is slower than a scan. Windows of a batch_size are all read in one REPEATABLE READ snapshot, so a row moving between windows is read once. That snapshot stays open for the whole table load: it counts towards idle_in_transaction_session_timeout, and vacuum can’t remove rows it can still see. Use a larger batch_size or a narrower range if either matters.

numeric without a precision has no fixed scale, so plan asks you to pin a type for it, such as columns = { amount = "decimal(38,10)" }. Postgres arrays load as JSON. See Types.

TLS

A Postgres connection takes libpq’s TLS settings, from 0.25.0. They apply to postgres and postgres_cdc sources alike, replication included, and to Postgres targets. To set them up and check them, see Connect over TLS.

sslmode	Encrypts	Checks the certificate
disable	Never	No
prefer (default)	When the server offers it	No
require	Always	No, unless sslrootcert is set: then as verify-ca
verify-ca	Always	Signed by a trusted CA
verify-full	Always	Signed by a trusted CA, for the host connected to

require and the verify-* modes refuse a server without TLS rather than fall back to plain text. sslrootcert names CA certificates (PEM) to trust. sslcert and sslkey give a client certificate and its private key; set both or neither.

Where SQLLoad differs from libpq:

• verify-ca and verify-full without sslrootcert trust the public CA roots built into SQLLoad, not the operating system’s store or ~/.postgresql/root.crt. A server whose certificate comes from a private CA, such as Amazon RDS and Aurora or a company CA, needs sslrootcert: for RDS, the region’s certificate bundle from AWS.
• verify-full matches the host only against the certificate’s subject alternative names, never its common name.
• A self-signed server certificate marked as a CA, as openssl req -x509 makes by default, can’t be its own sslrootcert. Sign the server certificate with a separate CA and give that CA.
• Certificate paths are relative to the directory SQLLoad runs in, not to sqlload.toml.

As in libpq, prefer falls back to plain text when the server refuses TLS or the handshake fails, and a Unix socket host never uses TLS. SCRAM sign-in over TLS binds to the server’s certificate, as libpq does by default. That stops an attacker who only relays the password exchange, but not one who changes how the client signs in. Only verify-full, or verify-ca on a private network, keeps a man in the middle out.

SSH tunnels

From 0.27.0. A connection with an ssh table reaches the database through a bastion host. host and port are then the database as the bastion sees it. To set one up, see Connect through an SSH tunnel.

• SQLLoad opens the tunnel itself, with no ssh program needed. It keeps one session to the bastion per run, shared by every table, and closes it when the run ends. It opens no local TCP port: direct reads use the tunnel in process, and a postgres_cdc source’s replication session reaches it through a Unix socket in a folder only your user can enter.
• It signs in with private_key_file (with private_key_passphrase if the key has one), or with each key ssh-agent holds when no file is set. From 0.32.0, password signs in when no key file is set; before, passwords aren’t supported.
• The bastion’s host key must be in known_hosts (~/.ssh/known_hosts by default). An unknown or changed key fails with its SHA256 fingerprint and the fix; a key is never accepted on first use.
• TLS still runs end to end with the database, so verify-full checks the database’s own name.
• Keepalives every 15 seconds hold idle tunnels open through long reads and load --follow. When the tunnel drops, the table fails with an error that names the tunnel. A postgres_cdc source resumes from its slot on the next run.
• postgres and postgres_cdc sources only for now: a Python source that names the connection gets its settings but no tunnel. Copy URIs, jump chains and ~/.ssh/config aren’t supported.