> ## Documentation Index
> Fetch the complete documentation index at: https://docs.bijection.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Replicate PostgreSQL tables into synced tables with change data capture

<Warning>PostgreSQL change data capture is in beta.</Warning>

`definePostgresIntegration` replicates selected tables of a PostgreSQL database
into synced tables. Bijection takes one consistent snapshot of the tables, then
follows the database's logical replication stream and publishes each committed
source transaction atomically across all the selected tables. A query never
sees half of a source transaction, even when it touched several tables.

```ts bijection/warehouse.ts theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
import { definePostgresIntegration } from "bijection/server";
import { v } from "bijection/values";

export const warehouse = definePostgresIntegration({
  collections: {
    orders: {
      id: v.int64(),
      description: v.union(v.string(), v.null()),
      total: v.string(),
    },
    order_lines: {
      id: v.int64(),
      order_id: v.int64(),
      amount: v.int64(),
    },
  },
});
```

```ts bijection/schema.ts theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
import { defineSchema, defineTable } from "bijection/server";
import { warehouse } from "./warehouse";

export default defineSchema({
  orders: defineTable(warehouse.orders.schema)
    .source(warehouse.orders)
    .index("by_provider_id", ["id"]),
  order_lines: defineTable(warehouse.order_lines.schema)
    .source(warehouse.order_lines)
    .index("by_order_id", ["order_id"]),
});
```

Your queries then read `orders` and `order_lines` like any other
[synced table](/integrations/synced-tables).

## Declaring the tables

`collections` maps each collection name to the validators of its columns. There
is no `protocol` or `sync` to write: the PostgreSQL protocol is built in.

`every` optionally sets how often the replication stream is polled. It defaults
to `{ seconds: 1 }`.

Declare every column of each table, by its PostgreSQL name, with the validator
for its type. A `NOT NULL` column uses the validator itself; a nullable column
uses `v.union(validator, v.null())`.

| PostgreSQL type | Validator |
| - | - |
| `boolean` | `v.boolean()` |
| `smallint`, `integer`, `bigint` | `v.int64()` |
| `real`, `double precision` | `v.float64()` |
| `bytea` | `v.bytes()` |
| `text`, `varchar`, `uuid`, `numeric`, `json`, `jsonb`, `date`, `timestamp`, `timestamptz` | `v.string()` |
| `time`, `timetz`, `interval`, `inet`, `cidr`, `macaddr`, `macaddr8`, `bit`, `varbit` | `v.string()` |

Exact values stay exact. `numeric` arrives as a decimal string that keeps its
scale, `json` and `jsonb` as their exact JSON text, and `timestamptz` as a UTC
timestamp string.

## Preparing the source database

The source must meet these requirements. Bijection checks them before it
replicates and refuses a source that doesn't:

* PostgreSQL 16.14, a primary server with `wal_level = logical`.
* `max_slot_wal_keep_size` set to a finite value of at most `1GB`.
* One publication that lists exactly the selected tables and publishes
  inserts, updates, deletes and truncates. It can't be `FOR ALL TABLES`, use
  column lists or row filters, or publish through a partition root.
* At most eight tables. Each is an ordinary table, not partitioned, without
  row-level security, with the default replica identity, at most 32 columns and
  no generated columns.
* Each table has a primary key whose columns are `NOT NULL`. Primary keys of
  type `numeric`, `json`, `jsonb`, `real`, `double precision`, `interval` or
  `timetz` aren't supported.

Create the publication, and a login with `REPLICATION`, `CONNECT` on the
database, `USAGE` on the schema, `SELECT` on the selected tables and `EXECUTE`
on `pg_catalog.pg_control_system()`:

```sql theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
CREATE PUBLICATION bijection_pub FOR TABLE orders, order_lines;
```

The login does not need superuser or write access. Note that replication
privileges allow more than reading the published tables.

## Connecting

The connection's private configuration is a JSON document with the database
address, the login and the publication, and maps each collection to its table:

```json warehouse-connection.json theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
{
  "host": "db.example.com",
  "port": 5432,
  "database": "shop",
  "user": "bijection_replication",
  "password": "…",
  "publication": "bijection_pub",
  "relations": {
    "orders": { "schema": "public", "table": "orders" },
    "order_lines": { "schema": "public", "table": "order_lines" }
  }
}
```

* `relations` must name exactly the declared collections.
* `ca_pem` optionally sets the certificate authority the server's certificate
  is verified against. The server name is always verified.
* `client_identity`, with `certificate_pem` and `private_key_pem`, adds a client
  certificate. With a client certificate, `password` may be omitted.

Store it as a credential, then configure, verify and install the connection.
Leave out `--base-url`:

```sh theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
bijection integration credential-create WAREHOUSE --from-file warehouse-connection.json
bijection integration configure warehouse --module warehouse.js --export warehouse --credential-ref WAREHOUSE
bijection integration identify warehouse verify-1
bijection integration run verify-1
bijection integration install warehouse orders
```

The selected tables form one group. Bind all of the group's collections in your
schema before installing; installing one table installs the whole group.

## Operating the source

`bijection integration source-status <source>` and the console's Sources
page show the replication phase, the last admitted position and the recorded
reason for a stop.

`bijection integration postgres-control <connection>` changes the source's
state. Pass the `revision` that `source-status` reports, so a control can't act
on a state you haven't seen:

```sh theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
bijection integration postgres-control warehouse --revision 7 --control stop
bijection integration postgres-control warehouse --revision 8 --control rebaseline
```

* `stop` stops replication for the connection.
* `rebaseline` takes a fresh snapshot. The current rows stay visible until the
  complete replacement is published.

<Warning>
  Schema changes are not replicated. Before you alter a selected table or the
  publication, stop the source; after the change, update your collection if
  needed and rebaseline. A changed table is reported as `schema_changed` or
  `source_identity_changed` and blocks the source until you do.
</Warning>

To rotate the database password or certificate: stop the source, rotate the
credential with `credential-rotate`, configure and verify the connection again,
then rebaseline.

## Limits

* A snapshot or rebaseline publishes at most 131,072 rows and 64 MB across the
  group, and a single row is at most 512 KB.
* A single source transaction carries at most 512 KB of replication payload.

These are upper bounds; other deployment limits can refuse work earlier. When a
change is too large to publish, the source stops without losing what it has
already published or the position it reached, and a rebaseline continues from
the source as it is now.

If a table's [materialized view](/views/overview) can't be updated for a
change, the change isn't published and the previous data stays visible. Fix
the view, then resume the source.