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.

Replication sessions on Windows

A postgres_cdc source’s replication client speaks plain text only, so its session reaches the database through a relay inside SQLLoad, which adds TLS and the SSH tunnel. On Linux and macOS the relay is a Unix socket in a folder only your user can enter. On Windows the replication client can only connect over TCP, so the relay listens on a random port on 127.0.0.1, for at most 30 seconds.

• Any local process can connect to that port. SQLLoad makes a random secret for each session and relays only the connection whose startup message carries it. It drops every other connection before a byte of it reaches the database, gives each one 5 seconds to send its startup message, and closes the port once its own client is in. The database never sees the secret.
• What remains: between the replication client and the relay, the sign-in exchange and then every replicated row cross the loopback interface in plain text. SCRAM never sends the password itself, but row data can be read by anyone who can capture loopback traffic. On Windows that takes an administrator, who could also read SQLLoad’s memory.
• From the relay to the database, the connection is encrypted as the TLS settings above say.

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. Direct reads use the tunnel in process. A postgres_cdc source’s replication session reaches it through a Unix socket in a folder only your user can enter, or on Windows through a loopback port guarded by a secret (see Replication sessions on Windows).
• 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.
• On Windows, ssh-agent is the pipe SSH_AUTH_SOCK names, or else the OpenSSH agent service’s (\\.\pipe\openssh-ssh-agent). Windows ssh-agent (OpenSSH agent pipe): compiles and unit-tested, not live-tested. Key-file and password sign-in run the same code as on Linux and macOS, but on Windows they’re not covered by an end-to-end test: no Windows test goes through a bastion.
• 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.