# Upgrade to Postgres 17

Start a new self-hosted Supabase deployment with Postgres 17, or upgrade an existing Postgres 15 installation.

Self-hosted Supabase ships with Postgres 15 by default. This guide covers two scenarios:

- **New deployment** - start fresh with Postgres 17 (no existing data)
- **Upgrade existing deployment** - migrate from Postgres 15 to Postgres 17 using `pg_upgrade`

## Before you begin

- Complete the [Self-Hosting with Docker](/docs/guides/self-hosting/docker) setup
- Your current database image should be `supabase/postgres:15.x`

## New deployment with Postgres 17

If you are starting a new self-hosted Supabase instance with **no existing data**, use the Postgres 17 compose override:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml up -d
```

This uses the `docker-compose.pg17.yml` override file which swaps the database image:

```yaml name=docker-compose.pg17.yml
services:
  db:
    image: supabase/postgres:17.6.1.084
```

If you use the override file, remember to include both compose files when running commands. Alternatively, you can update the image tag directly in `docker-compose.yml` instead of using an override.

The rest of the setup is the same as for Postgres 15 (see the Docker install [guide](/docs/guides/self-hosting/docker)).

If the new Postgres 17 container fails to start, make sure to check for an old `db-config` Docker volume. See [Postgres 17 fails to start with a leftover db-config volume](#postgres-17-fails-to-start-with-a-leftover-db-config-volume) for details.

## Upgrade an existing Postgres 15 deployment

Upgrading an existing deployment uses `pg_upgrade` to migrate data in place. The included upgrade scripts automates the full process.

### What the upgrade does

1. Pulls a specific Postgres 17 image and extracts upgrade binaries
2. Pulls supplemental upgrade scripts from Supabase's [Postgres](https://github.com/supabase/postgres) repository
3. Stops all self-hosted Supabase containers
4. Runs `pg_upgrade` inside a temporary Postgres 15 container
5. Runs additional tasks inside a temporary Postgres 17 container (re-enables extensions, applies patches, runs `VACUUM ANALYZE`)
6. Swaps data directories (the original is kept as a backup)
7. Starts self-hosted Supabase with Postgres 17
8. Applies additional role migrations (new in Postgres 17)

### Create a backup

You should create your own independent backup in case of disk failure or other issues.

The upgrade script automatically preserves the pgsodium key and original data directory as `./volumes/db/data.bak.pg15` as the final step. However, it is recommended to **always** create your own independent backup before starting:

Back up the database data directory:

```sh
cp -a ./volumes/db/data ./volumes/db/data-manual-backup
```

Back up the pgsodium encryption key (stored in a Docker named volume):

```sh
docker compose run --rm db cat /etc/postgresql-custom/pgsodium_root.key > ./pgsodium_root.key.backup
```

The `db-config` Docker named volume contains the pgsodium root encryption key. If you lose this key and have vault secrets, they become unrecoverable. The `cp -a` above backs up the data directory but NOT the named volume.

Optionally, take a logical backup too:

```sh
docker exec supabase-db pg_dumpall -h localhost -U supabase_admin > ./pg15_dump.sql
```

### Requirements

- At least **2x your current database size + 5 GB** of free disk space (`pg_upgrade` copies the data directory; the upgrade tarball is ~1.2 GB compressed)
- The script prompts for confirmation at each major step (use `--yes` to skip prompts)
- All self-hosted Supabase containers must be running before starting the upgrade
- Requires `bash`
- Must be run as root or using `sudo`

### Extensions removed in Postgres 17

The following extensions are **not available** in Postgres 17 builds. The upgrade script will prompt you to drop them if any of these are found:

| Extension     | Notes                     |
| ------------- | ------------------------- |
| `timescaledb` | Not built for Postgres 17 |
| `plv8`        | Not built for Postgres 17 |
| `plcoffee`    | Companion to plv8         |
| `plls`        | Companion to plv8         |

None of the above extensions are installed by default in the self-hosted Supabase setup. If you have installed any of them manually and need to keep them, **do not proceed** with the upgrade.

### Run the upgrade

```sh
sudo bash utils/upgrade-pg17.sh
```

The script might require your confirmation at some steps (e.g., while checking for disk space, or whether to disable extensions, or remove previous backups).

### After the upgrade

After a successful upgrade, always use both compose files:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml up -d
```

To verify that Postgres 17 is running:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml exec db psql -U postgres -c "SELECT version();"
```

The original Postgres 15 data is preserved at `./volumes/db/data.bak.pg15`. The pgsodium root key is saved as `./volumes/db/pgsodium_root.key.bak.pg15`. The upgrade binaries tarball is cached at `./volumes/db/pg17_upgrade_bin_*.tar.gz`. Once you have verified that everything works, you can reclaim disk space:

```sh
rm -rf ./volumes/db/data.bak.pg15 \
./volumes/db/pgsodium_root.key.bak.pg15 \
./volumes/db/pg17_upgrade_bin_*.tar.gz
```

Do not delete `data.bak.pg15` until you have verified the upgrade. Rollback is only possible while the backup exists.

### Rollback

If you need to revert to Postgres 15 (run the following commands as root):

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml down && \
rm -rf ./volumes/db/data && \
mv ./volumes/db/data.bak.pg15 ./volumes/db/data && \
docker compose run --rm db chown -R postgres:postgres /etc/postgresql-custom/ && \
docker compose up -d
```

This restores the original data directory, fixes file ownership on the `db-config` volume (Supabase's Postgres 15 and 17 images use different user IDs), and starts with the old Postgres 15 image.

### Custom Postgres configuration

The Postgres 17 image loads any `.conf` files from `/etc/postgresql-custom/conf.d/` on startup. This directory is on the `db-config` named volume, so changes persist across restarts.

This is a Supabase Postgres 17 image feature. The Postgres 15 image does not load files from `conf.d/`.

To add custom Postgres settings, create a `.conf` file in the volume. Since `conf.d/` is on a Docker named volume (not a bind mount), you need to write through the container:

```sh
docker exec supabase-db bash -c 'cat > /etc/postgresql-custom/conf.d/custom.conf << EOF
max_connections = 200
EOF'
```

Restart to apply (`max_connections` requires a full restart):

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml restart db
```

Verify the new settings:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml exec db psql -U postgres -c "SHOW max_connections;"
```

### Upgrade process details

The upgrade script delegates the core migration work to two scripts from the [supabase/postgres](https://github.com/supabase/postgres) repository (`ansible/files/admin_api_scripts/pg_upgrade_scripts/`).

**Phase 1 - Migrate data (Postgres 15 container):**

1. Disables extensions that are incompatible with `pg_upgrade` (`pg_graphql`, `pg_stat_monitor`, `pg_backtrace`, `wrappers`, `pgrouting`) and generates SQL to re-enable them after the upgrade
2. Temporarily grants superuser to the `postgres` role (required by `pg_upgrade`)
3. Extracts the previously saved Postgres 17 binaries tarball and runs `initdb` to create a new empty database
4. Runs `pg_upgrade --check` to verify the upgrade can succeed before making changes
5. Stops Postgres 15 and runs `pg_upgrade` to migrate all data to the new database
6. Copies Postgres configuration and the SQL scripts generated by `pg_upgrade` to a staging directory for the next phase

**Phase 2 - Finalize (Postgres 17 container):**

1. Moves the upgraded data directory into place and starts Postgres 17
2. Applies extension compatibility patches for Wrappers, `pg_net`, `pg_cron`, and Vault (fixes ownership, grants, and foreign server options)
3. Runs the SQL scripts generated by `pg_upgrade` to update system catalogs and extension versions
4. Re-enables the extensions that were disabled in phase 1
5. Grants predefined roles (`pg_monitor`, `pg_read_all_data`, `pg_signal_backend`, and on Postgres 16+ also `pg_create_subscription`) and revokes the temporary superuser grant
6. Restarts Postgres and runs `vacuumdb --all --analyze-in-stages` to rebuild optimizer statistics

After both phases complete, the upgrade script preserves the original Postgres 15 data directory as a backup and starts the full Supabase stack with Postgres 17.

## Troubleshooting

### pg_upgrade fails with replication slot errors

`pg_upgrade` cannot proceed if there are active replication slots. Default self-hosted installs don't have any, but if you set up logical replication or have custom replication configurations, drop the slots before upgrading:

```sh
docker exec supabase-db psql -h localhost -U supabase_admin -d postgres -c "
    SELECT pg_drop_replication_slot(slot_name)
    FROM pg_replication_slots;
"
```

Then re-run the upgrade script. Replication slots will need to be manually recreated after the upgrade.

### "Permission denied" on the data directory

The upgrade script fixes file ownership automatically (Postgres 15 and 17 use different UIDs). If you still see permission errors, run:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml run --rm db \
chown -R postgres:postgres /var/lib/postgresql/data
```

### pgsodium / Supabase Vault errors

The `db-config` named volume contains the pgsodium root encryption key at `/etc/postgresql-custom/pgsodium_root.key`. This volume is preserved during the upgrade. Never run `docker compose down -v` as this destroys named volumes and makes vault secrets unrecoverable.

### Services fail to connect after upgrade

Restart all services to pick up the new database:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml down && \
docker compose -f docker-compose.yml -f docker-compose.pg17.yml up -d
```

### Disk space issues during upgrade

The upgrade needs space for:

- The upgrade tarball (~1.2 GB compressed, cached for re-runs)
- A full copy of your database (created by `pg_upgrade`)
- The original data (kept as backup)

The script uses `/tmp` (or `TMPDIR` if set) for its staging directory, which holds the downloaded tarball and upgrade scripts. If your `/tmp` filesystem is small or mounted with limited space, you can point it to a different location, e.g.:

```sh
sudo TMPDIR=/mnt/my-tmp bash utils/upgrade-pg17.sh
```

If you run out of space mid-upgrade, the safest path is to roll back and free up disk space before retrying.

### Postgres 17 fails to start with a leftover db-config volume

If you are starting a **fresh** Postgres 17 deployment (not using the upgrade script) and the container fails to start, the most likely cause is a leftover `db-config` volume from a previous Postgres 15 installation. Start the containers without the `-d` option or check the logs for errors about `postgresql.conf` or other configuration mismatch.

To fix, remove the old volume and let Postgres 17 initialize a clean configuration:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml down && \
docker volume rm $(docker volume ls --filter "name=db-config" --format '{{.Name}}') && \
docker compose -f docker-compose.yml -f docker-compose.pg17.yml up -d
```

Removing the `db-config` volume destroys any custom Postgres configuration and the pgsodium root key. Only do this for fresh installations with no existing data or vault secrets.

### Restoring from a manual backup

If the upgrade fails and the script's built-in rollback isn't sufficient, restore from the manual backups created in the [Create a backup](#create-a-backup) step:

Restore the data:

```sh
docker compose -f docker-compose.yml -f docker-compose.pg17.yml down && \
rm -rf ./volumes/db/data && \
cp -a ./volumes/db/data-manual-backup ./volumes/db/data
```

Restore the pgsodium key to the `db-config` volume:

```sh
docker compose run --rm db \
  sh -c 'cat > /etc/postgresql-custom/pgsodium_root.key' < ./pgsodium_root.key.backup && \
docker compose run --rm db \
  chown postgres:postgres /etc/postgresql-custom/pgsodium_root.key && \
docker compose run --rm db \
  chmod 600 /etc/postgresql-custom/pgsodium_root.key
```

Start Postgres 15:

```sh
docker compose up -d
```