# PostgreSQL

> Bring tables and views from a PostgreSQL database into your warehouse, on a schedule, with optional incremental reads.

## 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:

```sql title="A read-only role for sanda"
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](https://docs.sanda-os.com.au/connections/allowlist).

## Connect PostgreSQL

:::steps
1. **Choose PostgreSQL.** Go to **Data · Connections**, press **New connection** and pick **PostgreSQL**.
2. **Say where the database is.** Enter the host name alone in **Host**, with no port and no `postgres://` prefix, and the name of the database in **Database Name**. **Port** sits under **Advanced settings** and starts at 5432.
3. **Enter the login.** Put the role's name in **Username** and its password in **Password**.
4. **Check the encryption.** Under **Advanced settings**, **SSL Mode** opens on **require**, which always encrypts the connection. Choose **verify-ca** or **verify-full** if sanda should also check the server's certificate. Both then ask for the **CA certificate**.
5. **Continue.** Press **Continue**, name the connection, and press **Read schema**. sanda signs in and lists the tables the role can read. If it fails, the message says why. See [Troubleshoot connections](https://docs.sanda-os.com.au/connections/troubleshooting).
:::

The rest of the flow, naming the connection, setting a frequency and choosing streams, is the same for every source. See [Add a connection](https://docs.sanda-os.com.au/connections/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.

| Update Method | How it finds changes | Sees deletes | What your database needs |
|---|---|---|---|
| **Scan Changes with User Defined Cursor** | Reads the rows whose cursor column, such as `updated_at`, has moved since the last run | No | `select` only. You pick the cursor for each table in the stream list |
| **Detect Changes with Xmin System Column** | Uses PostgreSQL's built-in `xmin` system column, so no cursor column is needed | No | `select` only. Suited to databases without heavy write loads. It does not read views |
| **Read Changes using Change Data Capture (CDC)** | Reads PostgreSQL's write-ahead log through logical replication | Yes | `wal_level = logical`, a replication slot, a publication and a role with the `replication` attribute |

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](https://docs.sanda-os.com.au/connections/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](https://docs.sanda-os.com.au/connections/choose-streams).
- **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](https://docs.sanda-os.com.au/connections/allowlist).
- **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:

```sql title="Prepare PostgreSQL for change data capture"
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.

:::caution
A replication slot that nothing reads makes the server keep its write-ahead log, and the disk can fill. If you delete the connection or stop syncing for good, drop the slot with `select pg_drop_replication_slot('becca_slot');`.
:::

## Configuration fields

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

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Host** | string | Yes | Hostname of the database. |
| **Port** | integer | Yes | Port of the database. Defaults to 5432. Default `5432`. |
| **Database Name** | string | Yes | The name of the database to connect to. |
| **Username** | string | Yes | The username which is used to access the database. |
| **Password** | string, secret | No | The password associated with the username. |
| **Azure Entra Service Principal Authentication** | boolean | No | Interpret password as a client secret for a Microsoft Entra service principal. Default `false`. |
| **Azure Entra Tenant Id** | string | No | If using Entra service principal, the ID of the tenant. |
| **Azure Entra Client Id** | string | No | If using Entra service principal, the application ID of the service principal. |
| **SSL Mode** | one of 6 options | No | The encryption method which is used when communicating with the database. |
| **Schemas** | array | No | The list of schemas to sync from. Case sensitive. Empty means all schemas. |
| **JDBC URL Parameters (Advanced)** | string | No | Additional properties to pass to the JDBC URL string when connecting to the database formatted as 'key=value' pairs separated by the symbol '&'. (example: key1=value1&key2=value2&key3=value3). |
| **SSH Tunnel Method** | one of 3 options | Yes | Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use. |
| **Update Method** | one of 3 options | Yes | Configures how data is extracted from the database. |
| **Check Table and Column Access Privileges** | boolean | No | When this feature is enabled, during schema discovery the connector will query each table or view individually to check access privileges and inaccessible tables, views, or columns therein will be removed. In large schemas, this might cause schema discovery to take too long, in which case it might be advisable to disable this feature. Default `true`. |
| **Checkpoint Target Time Interval** | integer | No | How often (in seconds) a stream should checkpoint, when possible. Default `300`. |
| **Max Concurrent Queries to Database** | integer | No | Maximum number of concurrent queries to the database. Leave empty to let becca optimize performance. |

### 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**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **CA certificate** | string, secret | Yes |  |
| **Client certificate File** | string, secret | No | Client certificate (this is not a required field, but if you want to use it, you will need to add the Client key as well). |
| **Client Key** | string, secret | No | Client key (this is not a required field, but if you want to use it, you will need to add the Client certificate as well). |
| **Client key password** | string, secret | No | Password for keystorage. This field is optional. If you do not add it, the password will be generated automatically. |

**verify-full**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **CA certificate** | string, secret | Yes |  |
| **Client certificate File** | string, secret | No | Client certificate (this is not a required field, but if you want to use it, you will need to add the Client key as well). |
| **Client Key** | string, secret | No | Client key (this is not a required field, but if you want to use it, you will need to add the Client certificate as well). |
| **Client key password** | string, secret | No | Password for keystorage. This field is optional. If you do not add it, the password will be generated automatically. |


### 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**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **SSH Tunnel Jump Server Host** | string | Yes | Hostname of the jump server host that allows inbound ssh tunnel. |
| **SSH Connection Port** | integer | Yes | Port on the proxy/jump server that accepts inbound ssh connections. Default `22`. |
| **SSH Login Username** | string | Yes | OS-level username for logging into the jump server host. |
| **SSH Private Key** | string, secret | Yes | OS-level user account ssh key credentials in RSA PEM format ( created with ssh-keygen -t rsa -m PEM -f myuser_rsa ). |

**Password Authentication**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **SSH Tunnel Jump Server Host** | string | Yes | Hostname of the jump server host that allows inbound ssh tunnel. |
| **SSH Connection Port** | integer | Yes | Port on the proxy/jump server that accepts inbound ssh connections. Default `22`. |
| **SSH Login Username** | string | Yes | OS-level username for logging into the jump server host. |
| **Password** | string, secret | Yes | OS-level password for logging into the jump server host. |


### 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)**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Replication Slot** | string | Yes | A plugin logical replication slot. |
| **Publication** | string | Yes | A Postgres publication used for consuming changes. |
| **Initial Waiting Time in Seconds (Advanced)** | integer | No | The amount of time the connector will wait when it launches to determine if there is new data to sync or not. Defaults to 1200 seconds. Valid range: 120 seconds to 2400 seconds. Default `1200`. |
| **LSN commit behavior** | string | No | Determines when becca should flush the LSN of processed WAL logs in the source database. `After loading Data in the destination` is default. If `While reading Data` is selected, in case of a downstream failure (while loading data into the destination), next sync would result in a full sync. One of `While reading Data`, `After loading Data in the destination`. Default `After loading Data in the destination`. |
| **Debezium heartbeat query (Advanced)** | string | No | Specifies a query that the connector executes on the source database when the connector sends a heartbeat message. |
| **Initial Load Timeout in Hours (Advanced)** | integer | No | The amount of time an initial load is allowed to continue for before catching up on CDC events. Default `8`. |
| **Debezium Engine Shutdown Timeout in Seconds (Advanced)** | integer | No | The amount of time to allow the Debezium Engine to shut down, in seconds. Default `60`. |

**Detect Changes with Xmin System Column**

No further fields.

**Scan Changes with User Defined Cursor**

No further fields.


## Related

:::links
- [Sync modes](https://docs.sanda-os.com.au/connections/sync-modes): How sanda reads each stream on every run.
- [Sync schedules](https://docs.sanda-os.com.au/connections/schedules): How often a connection runs.
- [Allowlist sanda’s address](https://docs.sanda-os.com.au/connections/allowlist): For a system behind a firewall.
- [Troubleshoot connections](https://docs.sanda-os.com.au/connections/troubleshooting): What an error means and how to fix it.
:::