PostgreSQL

Before you start

You need three things on the PostgreSQL side: a server sanda can reach, a login that can only read, and sanda's address allowed through your firewall.

• A reachable server. PostgreSQL 11 or newer. sanda connects from the internet, so the host name must resolve on the public internet. A private address, or a name that only works inside your network, is refused before sanda tries. For a database on a private network, see the SSH tunnel tip below.
• The connection details. The host name, the port (5432 unless you changed it) and the name of the database.
• A read-only role. sanda only ever reads. Give it a role of its own, so its access is easy to see and easy to revoke. Run this once, as a role that can create roles:

create role becca_reader login password 'use-a-long-random-password';
grant connect on database acme to becca_reader;
grant usage on schema public to becca_reader;
grant select on all tables in schema public to becca_reader;
alter default privileges in schema public
  grant select on tables to becca_reader;

Replace acme with your database name. Repeat the last three statements for every other schema you want in sanda. The last statement covers tables created later, but only those created by the role that runs it. Run it as the role that creates your tables, or add for role and that role's name after alter default privileges.

• sanda's address allowed. Add sanda's fixed address to the firewall or security group that protects the database, and to pg_hba.conf if you restrict logins by address. See Allowlist sanda's address.

Connect PostgreSQL

The rest of the flow, naming the connection, setting a frequency and choosing streams, is the same for every source. See Add a connection.

What syncs

sanda reads the tables and views in the schemas you allow. Schemas sits under Advanced settings. Leave it empty to read every schema the role can see, or list the ones you want. The names are case sensitive. Each table you choose becomes a stream, and lands in your warehouse as its own table.

Check Table and Column Access Privileges is on by default. During schema discovery sanda tests each table individually and leaves out any the role cannot read. A table missing from the stream list usually means the role lacks select on it.

How changes are found

Update Method, under Advanced settings, decides how sanda can tell what has changed since the last run. It opens on Scan Changes with User Defined Cursor, the one that needs nothing set up on your database.

A table left on a full refresh mode is read whole on every run and replaced, so it does reflect deletes at the source, at the cost of reading everything each time. See sync modes.

Tips

• Start with the schemas that matter. You can widen the selection later without redoing anything.
• Read the big tables incrementally. The first run reads every table you choose in full. For a large table, switch to an incremental mode and choose a cursor column that only moves forward, such as updated_at. See choose what to sync.
• Prefer a read replica. If you have one, point sanda at it so syncs never compete with your application. A replica that lags hands sanda older data. Change data capture from a replica needs PostgreSQL 16.1 or later and extra configuration on the server, so it normally runs against the primary.
• Reach a private database through a tunnel. SSH Tunnel Method sits under Advanced settings, with SSH Key Authentication and Password Authentication options. The jump server has to be reachable from the internet, and it needs sanda's address allowed, exactly as a database would. See Allowlist sanda's address.
• Azure Entra sign-in. If your server signs in with a Microsoft Entra service principal, turn on Azure Entra Service Principal Authentication under Advanced settings. Password then holds the service principal's client secret, and you also fill in Azure Entra Tenant Id and Azure Entra Client Id.

Set up change data capture

Only do this if you need deletes captured and can change server settings. It needs wal_level = logical in the server configuration, which takes a restart. On a managed service that is usually a setting in the provider's console instead. Then, as a privileged role:

alter role becca_reader replication;
select pg_create_logical_replication_slot('becca_slot', 'pgoutput');
create publication becca_pub for table public.orders, public.customers;

Choose Read Changes using Change Data Capture (CDC) under Update Method in Advanced settings, and enter becca_slot in Replication Slot and becca_pub in Publication. A table without a primary key needs alter table your_table replica identity full, or PostgreSQL refuses updates and deletes on it once it is in a publication.

Configuration fields

What the connection form asks for. Required fields are marked; the rest are optional.

SSL Mode

The encryption method which is used when communicating with the database.

Choose one of the following. Each asks for its own fields.

disable

No further fields.

allow

No further fields.

prefer

No further fields.

require

No further fields.

verify-ca

verify-full

SSH Tunnel Method

Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use.

Choose one of the following. Each asks for its own fields.

No Tunnel

No further fields.

SSH Key Authentication

Password Authentication

Update Method

Configures how data is extracted from the database.

Choose one of the following. Each asks for its own fields.

Read Changes using Change Data Capture (CDC)

Detect Changes with Xmin System Column

No further fields.

Scan Changes with User Defined Cursor

No further fields.

Related