# PostgreSQL

> Source: https://docs.clonepartner.com/connectors/postgres/

<!-- sources: src/connectors/postgres.ts, src/connectors/readonly-sql.ts, src/connectors/readonly-pool.ts, references/envoy/connectors.md -->

# PostgreSQL

The `postgres` connector queries and writes PostgreSQL tables over a connection pool, with a separate read-only pool for dashboard queries.

## When to use it

Use `postgres` as a relational source or destination, and as a mapping database or cursor store for sync tasks. For MySQL-protocol servers use [MySQL](/connectors/mysql/); for SQL Server use [MS SQL Server](/connectors/mssql/).

## Configuration

| Key | Type | Required | Default | Description |
|---|---|---|---|---|
| `connection_string` | string | Yes | — | PostgreSQL connection string (`postgres://user:pass@host:5432/db`), stored as a secret |
| `max_connections` | number | No | 10 | Max pool connections |
| `idle_timeout` | number | No | 30 | Idle connection timeout in seconds |
| `ssl` | string | No | — | SSL mode: `disable`, `prefer`, `require`, `verify-ca`, `verify-full` |
| `readonly_connection_string` | string | No | — | Optional read-only connection string for dashboard queries (separate pool). Defaults to the primary with `default_transaction_read_only=on`. Stored as a secret. |
| `readonly_max_connections` | number | No | 2 | Max connections on the read-only pool |

## Methods

| Method | Arguments | Returns | Behavior |
|---|---|---|---|
| `find` | `table` (required), `filter`, `select`, `limit`, `offset`, `order_by` | array | Streams matching rows to downstream steps |
| `find_one` | `table` (required), `filter`, `select` | object | Returns the first matching row |
| `insert_one` | `table` (required), `data` (required) | object | Inserts one record and returns it (`RETURNING *`) |
| `upsert` | `table` (required), `data` (required), `conflict_columns` (required), `update_columns` | object | `INSERT ... ON CONFLICT` on `conflict_columns`; updates `update_columns` (or all columns) on conflict |
| `delete` | `table` (required), `filter` | object | Deletes rows matching the filter |
| `execute` | `statements` (required) | object | Executes a raw SQL statement on the primary pool |
| `select` | `sql` (required), `params` | array | Runs a read-only `SELECT` on the dedicated read pool (`SELECT` or `WITH` only) |

`filter` uses the shared JSON filter syntax (`gt`, `gte`, `lt`, `lte`, `ne`, `in`, `nin`, `like`, `ilike`, `regex`, `exists`, plus `and`/`or`). `ilike` maps to PostgreSQL `ILIKE` and `regex` to the `~` operator.

## Example

```yaml
name: export_active_users
connectors:
  source:
    type: postgres
steps:
  - name: read_users
    type: call_method
    config: |
      {
        "connector": "source",
        "method": "find",
        "args": { "table": "users", "filter": { "status": "active" } }
      }
```

The matching `connectors.yaml` entry for CLI runs:

```yaml
source:
  type: postgres
  config:
    connection_string: ${POSTGRES_CONNECTION_STRING}
    ssl: require
```

## Behavior notes

- `select` rejects anything that is not a `SELECT` or `WITH ... SELECT` statement and runs on a separate pool whose sessions default to read-only transactions, so dashboard queries cannot mutate data. Point `readonly_connection_string` at a replica to isolate them further.
- The connector implements the mapping-DB interface (a `resource_mapping` table created on first use) and the cursor store, so it can serve as `lookup_connector` or `cursor_store` for sync tasks. See [Idempotent sync and the mapping DB](/building/idempotent-sync/).
- `insert_one` and `upsert` return the affected row via `RETURNING *`.

## Related

- [Connectors overview](/connectors/) — all compiled connector types
- [MySQL](/connectors/mysql/) — the same method set for MySQL
- [Idempotent sync and the mapping DB](/building/idempotent-sync/) — how the mapping table is used