# Extensions in HA Clusters

> Install and manage PostgreSQL extensions in Patroni-managed HA clusters

---

LLMS index: [llms.txt](/llms.txt)

---

Installing PostgreSQL extensions in a Patroni-managed HA cluster is
fundamentally different from a single-node instance. This page explains why,
and walks through the `pg ha extension` command subtree that handles it.

## Why not `pg extension install`?

The single-node flow (`pg extension install <instance> <ext>`) works by:

1. Building a `-ext` derived image with Pigsty packages
2. Stopping and recreating the container from the new image
3. Editing `postgresql.conf` to set `shared_preload_libraries`
4. Running `CREATE EXTENSION` inside the container

In a Patroni cluster, **steps 2-4 all break**:

- **Patroni is PID 1.** Recreating a container takes that node offline. If it's
  the leader, Patroni triggers a failover — the cluster reshuffles while
  you're mid-install.
- **Patroni regenerates `postgresql.conf` every loop** from the DCS. Any
  direct edit to the file is overwritten within seconds. `shared_preload_libraries`
  must be set via `patronictl edit-config` (which writes to the DCS).
- **`CREATE EXTENSION` must run on the leader**, which may be on a remote host
  — not inside any local container.

`pg ha extension` orchestrates all of this correctly.

## How It Works

The install flow follows a specific order to avoid the pitfalls above:

```
1. Validate extension names (IsExtensionKnown)
2. Build -ext image with Pigsty packages
3. patronictl pause <scope> --wait            ← disable auto-failover
4. Recreate each member container (one at a time, wait for rejoin)
5. patronictl resume <scope> --wait           ← ⚠ must come BEFORE edit-config
6. patronictl edit-config (set shared_preload_libraries)
   → Patroni triggers a rolling restart (replicas first, leader last)
7. Wait for rolling restart to complete
8. CREATE EXTENSION on leader
9. Save extensions list to pg.yaml
```

The critical ordering is **resume before edit-config**: a paused Patroni cluster
does not apply `edit-config` changes. If you edit-config while paused, the
`shared_preload_libraries` update is silently lost.

### Builtin-only fast path

If all requested extensions are builtin (contrib extensions like `hstore`,
`uuid-ossp` that ship with PostgreSQL), steps 2-4 are skipped entirely — no
image build, no container recreate, no pause/resume. The flow goes straight to
`edit-config` + `CREATE EXTENSION`.

## Commands

### Install

```bash
pg ha extension install <scope> <extension>[,<extension>...] [flags]
```

Builds the `-ext` image, pauses the cluster, and recreates local member containers.

- **Single-host cluster** (all members local): automatically runs apply (resume + edit-config + CREATE EXTENSION)
- **Cross-host cluster**: only performs pause + recreate, cluster stays paused. Run install on each host separately, then run `pg ha extension apply` on any host to complete the installation.

Extensions are passed as a comma-separated list:

```bash
# Install a single extension
pg ha extension install app pg_stat_statements

# Install multiple extensions to a specific database
pg ha extension install app pg_cron,pg_stat_statements --database mydb

# Skip the rolling restart confirmation prompt
pg ha extension install app pgvector --auto-restart
```

**Flags:**

| Flag | Default | Description |
|------|---------|-------------|
| `--database` | `postgres` | Target database for `CREATE EXTENSION` |
| `--auto-restart` | `false` | Skip confirmation for the rolling restart triggered by `edit-config` |

> **⚠ `--database` affects all extensions.** When you specify `--database`, the `apply` phase runs `CREATE EXTENSION IF NOT EXISTS` for **all installed extensions** (not just the newly added ones) in the target database. For example, if the cluster has `[pg_cron, pg_stat_statements, pgvector]` installed, running `pg ha extension install app hstore --database mydb` will create all four extensions in `mydb`. The extension `.so` files are already in the image and won't be reinstalled — only the SQL objects (functions, types, etc.) are registered in the target database.
>
> If you only want to enable an existing extension in a specific database, there's no need to reinstall. Simply connect to that database with psql and run `CREATE EXTENSION IF NOT EXISTS` directly.

### Remove

```bash
pg ha extension remove <scope> <extension>[,<extension>...] [flags]
```

Runs `DROP EXTENSION` on the leader, then updates `shared_preload_libraries`
via `patronictl edit-config` (triggers a rolling restart). Does **not** rebuild
the image or recreate containers — the `-ext` image only grows; disk reclamation
is rare and manual.

Extensions are passed as a comma-separated list:

```bash
pg ha extension remove app pg_cron
pg ha extension remove app pg_stat_statements,pg_cron --auto-restart
```

### List

```bash
pg ha extension list <scope>
```

Shows three views of the cluster's extensions:

- **Config (pg.yaml):** the `extensions` list stored in `pg.yaml`
- **DCS (preload):** the `shared_preload_libraries` value from `patronictl show-config`
- **Leader (installed):** actual extensions from `pg_extension` on the leader

```bash
$ pg ha extension list app
Cluster "app" extensions:

  Config (pg.yaml):  [pg_stat_statements pg_cron]
  DCS (preload):     shared_preload_libraries: pg_stat_statements,pg_cron
  Leader (installed): [pg_cron pg_stat_statements]
```

### Apply

```bash
pg ha extension apply <scope> [flags]
```

Manually triggers the second half of the install flow: resume the cluster,
run `patronictl edit-config`, and execute `CREATE EXTENSION` on the leader.

Used in **cross-host** clusters — after running `install` on each host (each
builds the image and recreates its local members), run `apply` once on any host
to complete the DCS update and extension creation.

```bash
pg ha extension apply app
pg ha extension apply app --database mydb --auto-restart
```

> **⚠ `--database` affects all installed extensions.** `apply` runs `CREATE EXTENSION IF NOT EXISTS` for **all extensions** in the cluster in the specified database, not just the newly added ones.

## Cross-Host Workflow

In a cross-host cluster, each host manages only its own members. The extension
installation workflow splits into two phases with **global pause/resume** —
the cluster is paused once and resumed once, not per-host:

**Phase 1 — per host (sequentially):** Run `pg ha extension install` on each host.
Each host:
- Builds the `-ext` image locally (merging DCS's existing preload list to include all packages)
- Pauses the cluster (idempotent — second host won't fail on "already paused")
- Recreates its own local members from the new image (**replicas first, leader last**)
- Saves config — **cluster stays paused**, no resume

> **Recommended order: start from replica hosts.** If the host with the leader runs
> `install` first, recreating the leader triggers a failover (leader moves to another
> host). Starting from replica hosts keeps the leader in place until the very end,
> minimizing failover-related data sync overhead.

```bash
# host A (replicas only — run first) — cluster enters paused state
pg ha extension install app pg_stat_statements,pg_cron

# host B (has the leader — run last) — cluster stays paused
pg ha extension install app pg_stat_statements,pg_cron
```

**Phase 2 — once, on any host:** Run `pg ha extension apply` to:
- Resume the cluster (idempotent — tolerates "not paused")
- Update `shared_preload_libraries` via `patronictl edit-config`
- Wait for the rolling restart
- Run `CREATE EXTENSION` on the leader

```bash
pg ha extension apply app --auto-restart
```

> **Note:** After `install`, the cluster is in paused state (auto-failover disabled).
> If you forget to run `apply`, run `pg ha extension apply` or manually
> `patronictl resume <scope>` to re-enable failover.

For single-host clusters (all members local), `install` automatically runs
the `apply` step — no separate command needed.

> **About leader drift:** Leader drift cannot be completely avoided at this time.
> When the container on the host where the leader resides is recreated, the Patroni
> process stops. After the leader key in the DCS expires (TTL), even if the cluster
> is paused, other replicas will still detect the expired key and trigger a new
> election. The `replicas-first` recreation order ensures that the host with the
> leader is the last one to run `install`, but it cannot prevent the leader drift
> itself. This is an inherent limitation of Patroni + container recreation.

## The `extensions` Config Field

Extensions are tracked at the cluster level in `pg.yaml`, under the Patroni
cluster config:

```yaml
addons:
  patroni:
    app:
      name: app
      extensions:
        - pg_stat_statements
        - pg_cron
      passwords:
        superuser: ...
      members:
        node1: { ... }
        node2: { ... }
```

This list is the source of truth for what `pg ha extension list` reports and
what `apply` installs. Both `install` and `remove` update it automatically.

## `shared_preload_libraries` Ordering

Some extensions must appear at position 0 in `shared_preload_libraries`
(PostgreSQL fatals if they're not first). pgcli tracks this as a catalog
attribute (`PreloadFirst`) — currently set on:

- **`citus`** — distributed PostgreSQL, must be loaded before anything else
- **`timescaledb`** — time-series engine, same constraint

Additional extensions may require companion DCS parameters:

- **`pg_cron`** needs `cron.database_name`

`pg ha extension` handles all of this automatically:

- Extensions with `PreloadFirst` are always placed at the start of the CSV,
  regardless of input order
- When `pg_cron` is present, `cron.database_name` is set to the `--database`
  value (default `postgres`)
- When `pg_cron` is removed, `cron.database_name` is cleared

## Examples

### Install pg_stat_statements and pg_cron

```bash
pg ha extension install app pg_stat_statements,pg_cron --database mydb --auto-restart
```

This builds an `-ext` image, recreates all members (with pause/resume), sets
`shared_preload_libraries=pg_stat_statements,pg_cron` and
`cron.database_name=mydb` in the DCS, then creates both extensions in `mydb`.

### Install Citus (must be first in preload)

```bash
pg ha extension install app pg_stat_statements,citus
```

Even though `citus` is listed second, the preload CSV is generated as
`citus,pg_stat_statements` — extensions with `PreloadFirst` are always
placed at position 0.

### Remove an extension

```bash
pg ha extension remove app pg_cron
```

Drops `pg_cron` from the leader, removes it from `shared_preload_libraries`,
and clears `cron.database_name`. Triggers a rolling restart.

### Check what's installed

```bash
pg ha extension list app
```

## Limitations

- **Image rebuild is additive.** Removing an extension does not rebuild the
  `-ext` image or shrink it. To reclaim disk, manually prune old images with
  `podman image prune`.
- **No per-member extension list.** Extensions are cluster-wide — all members
  share the same `-ext` image and the same `shared_preload_libraries`.
- **`CREATE EXTENSION` targets one database.** PostgreSQL extensions are
  per-database. To install in multiple databases, re-run with `--database`
  pointing at each one.
- **Cross-host requires manual coordination.** Each host must run `install`
  before `apply` is run once. pgcli does not SSH into remote hosts.

## See Also

- [Patroni HA](./ha/) — cluster setup, commands, and architecture
- [Extensions](/docs/extensions/) — single-node extension management and the Pigsty catalog
- [Patroni Dynamic Configuration](./ha-dynamic/) — DCS parameters reference

---

Backlinks:

- [HA Cluster](/docs/ha-cluster/)
- [Patroni HA](/docs/ha-cluster/ha/)