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

# Upgrading PostgreSQL

> How compose.yaml moves an installation from PostgreSQL 17 to 18, how to check it and how to roll back.

From version 44.0, `compose.yaml` runs PostgreSQL 18. An installation made with an earlier `compose.yaml` runs PostgreSQL 17, and its first `docker compose up -d` on 44.0 upgrades it by itself. Notifuse still runs on PostgreSQL 17 (and refuses an older server), so nothing forces you to move: see [Staying on PostgreSQL 17](#staying-on-postgresql-17).

An installation with an external database (`DB_HOST` pointing at a managed PostgreSQL) is not concerned: Notifuse only connects to it.

## What `docker compose up -d` does

1. Compose stops Notifuse and PostgreSQL 17.
2. The `pg-upgrade` service copies the PostgreSQL 17 data to the `postgres-upgrade-backup` volume, in place of any earlier copy. If that volume's disk has less free space than the data plus 10 %, it stops here without changing anything, and so does the stack: `docker compose logs pg-upgrade` says how much space it needs. Free some, then run `docker compose up -d` again.
3. It upgrades the data to PostgreSQL 18 in place, with [pgautoupgrade](https://github.com/pgautoupgrade/docker-pgautoupgrade) (`pg_upgrade --link`).
4. PostgreSQL 18 starts on the upgraded data, then Notifuse.

Notifuse is unavailable meanwhile. The time is driven by the number of workspaces more than by the volume of data, since each workspace has its own database: count seconds for a handful of workspaces, minutes for hundreds. Follow it with `docker compose logs -f pg-upgrade`.

Every later `docker compose up -d` finds the data on 18 and does nothing.

If the upgrade stops half-way (an error, or the server going down), the stack does not start, and the next `docker compose up -d` puts the copy back and starts the upgrade again. `docker compose logs pg-upgrade` gives the error of the failed attempt.

> [!WARNING]
>
> The copy is a way back on the same disk, not a backup. [Back up](https://www.notifuse.com/docs/self-hosting/backups.md) before upgrading, and keep `SECRET_KEY` with the backup.

## Before upgrading

- **Back up** every database, as [Backups](https://www.notifuse.com/docs/self-hosting/backups.md) shows.

- **Check the free space**: the copy needs as much as the data takes. With the stack running on 17:

  ```bash
  docker compose exec postgres du -sh /var/lib/postgresql/data
  df -h /var/lib/docker
  ```

- **Set `DB_EXPECT_EXISTING=true`** in `.env`. If anything went wrong and Notifuse found an empty database, it would stop instead of starting a new installation on it.

## Checking the result

```bash
docker compose logs pg-upgrade | grep -i copy      # "Copying the PostgreSQL 17 data ..." then "Copy done"
docker compose exec postgres psql -U postgres -d postgres -Atc "SELECT version()"    # PostgreSQL 18.x
docker compose exec postgres psql -U postgres -d postgres -Atc "SELECT datname FROM pg_database ORDER BY 1"
```

The last command lists `notifuse_system` and one `notifuse_ws_…` database per workspace (replace `postgres` with your `DB_USER` if you changed it). Then sign in and open your workspaces.

## Deleting the copy

Once the upgraded installation works, delete the copy to get its space back. Its name starts with the Compose project, the name of the directory that holds `compose.yaml`:

```bash
docker volume ls | grep postgres-upgrade-backup
docker volume rm notifuse_postgres-upgrade-backup
```

## Rolling back

While the copy exists, you can go back to PostgreSQL 17 and the data as it was before the upgrade. Anything written since the upgrade is lost.

```bash
docker compose down
# Put back the compose.yaml of your previous release, for example 43.0:
curl -fsSL https://raw.githubusercontent.com/Notifuse/notifuse/v43.0/compose.yaml -o compose.yaml
# Replace the PostgreSQL 18 data with the copy (project "notifuse": adjust both volume names)
docker run --rm -v notifuse_postgres-upgrade-backup:/from -v notifuse_postgres-data:/to alpine \
  sh -c 'find /to -mindepth 1 -delete && cp -a /from/pg17/. /to/'
docker compose up -d
```

## Staying on PostgreSQL 17

Keep the image and the mount path together, and remove the upgrade, in your `compose.yaml`:

- `image: postgres:17-alpine` on the `postgres` service;
- its volume mounted at `/var/lib/postgresql/data` (`- postgres-data:/var/lib/postgresql/data`);
- the `pg-upgrade` service, and the `depends_on` of `postgres` on it, deleted.

The tag and the path are a matched pair: PostgreSQL 18 keeps its data in `/var/lib/postgresql/18/docker`, 17 in `/var/lib/postgresql/data`.

## With your own Compose file

If you run PostgreSQL from your own file, upgrade the same way: stop the stack, back up, copy the volume, then run pgautoupgrade once on it, mounted at `/var/lib/postgresql`:

```bash
docker run --rm -v <your volume>:/var/lib/postgresql \
  -e POSTGRES_USER=<your user> -e POSTGRES_PASSWORD=<your password> \
  -e PGAUTO_ONESHOT=yes -e PGAUTO_REINDEX=no pgautoupgrade/pgautoupgrade:18-alpine
```

Then use `postgres:18-alpine` with the volume mounted at `/var/lib/postgresql`. `PGAUTO_REINDEX=no` is safe only when both images are Alpine; from a Debian-based `postgres:17`, use `pgautoupgrade:18-trixie` without it, so that the indexes are rebuilt.

> [!WARNING]
>
> Never set `PGDATA` to get past a PostgreSQL 18 startup error. With an explicit `PGDATA`, the image no longer checks for data from an older version, and initialises an empty database next to yours instead of refusing to start. If PostgreSQL refuses to start, that refusal is protecting your data.

## Good to know

- `docker compose down -v` and `docker volume prune` delete the database and the copy.
- An upgraded database keeps data checksums off, as PostgreSQL 17 created it; a new PostgreSQL 18 installation has them on.
- Upgrade on the same CPU architecture the database was created on: don't combine a move between x86 and ARM servers with the upgrade.
- pgautoupgrade does not support PostgreSQL servers with SSL enabled or tablespaces on separate volumes, neither of which `compose.yaml` uses. With those, move the data with a [backup and a restore](https://www.notifuse.com/docs/self-hosting/backups.md) instead.
