Self-Hosted: Upgrading PostgreSQL

If your self-hosted Hudu instance is running a PostgreSQL version older than 18, this guide walks through upgrading to PostgreSQL 18 using a dump and restore, without losing data. As of Hudu v2.44.1, 18.4 is the suggested minimum version, and Hudu recommends all self-hosted users start upgrading, since new features in upcoming updates will likely break on older PostgreSQL versions. That guidance applies whether you are on an older release such as 12.2 or a more recent one such as 17.2, so 18.4 is effectively becoming the baseline going forward.

Before you begin

Reference environment

These steps are written against the standard Hudu layout. Confirm your own docker-compose.yml looks like this before following the commands below, with service name db and volume postgres_data mounted at /var/lib/postgresql/data. The example below shows a postgres:12.2 image; if your db service shows a different version, that is the version you are upgrading from, and you should substitute it wherever the steps reference 12.2 (most notably the sed commands in step 7).

volumes:
  postgres_data: {}
services:
  db:
    image: 'postgres:12.2'
    restart: unless-stopped
    volumes:
      - postgres_data:/var/lib/postgresql/data
    env_file:
      - '.env'
  app:
    ...
  worker:
    ...

Confirm your .env has DB_USERNAME and DB_NAME set. Run every command below from your Hudu install directory (the one containing docker-compose.yml and .env, typically ~/hudu2 or /root/hudu2).

Back up and check disk space

Back up your server. If your host supports VM or disk snapshots (most cloud providers do), take one now. This procedure never deletes your old data volume, but a full server snapshot is the fastest way to undo everything if something goes wrong for a reason outside this procedure's control, such as power loss or a host crash.

Check free disk space. You will want roughly 3x your current database size free on the disk where your Hudu install and Docker volumes live. This holds the dump file plus the new PostgreSQL 18 data alongside your existing PostgreSQL 12 data, which you keep until you have confirmed the upgrade is solid.

Upgrade steps

1. Load your DB credentials and confirm your current setup

DB_USERNAME=$(sed -n 's/^[[:space:]]*DB_USERNAME=//p' .env | tail -1)
DB_NAME=$(sed -n 's/^[[:space:]]*DB_NAME=//p' .env | tail -1)
echo "DB_USERNAME=$DB_USERNAME DB_NAME=$DB_NAME"

# both must be non-empty before continuing; if either is blank,
# your .env uses different key names, so open it and check
[ -n "$DB_USERNAME" ] && [ -n "$DB_NAME" ] || echo ">> ONE IS EMPTY, do not continue until both print a value"

# confirm what's actually backing the db container's data directory
# don't assume it's named "postgres_data" if you've ever customized this
docker inspect "$(docker compose ps -q db)" \
  --format '{{range .Mounts}}{{.Name}} -> {{.Destination}} ({{.Type}}){{println}}{{end}}'

Run every step in this guide from the same terminal session. These shell variables are not saved, so they will be empty in a new terminal or after a reboot, which causes later steps to fail.

You should see a line like postgres_data -> /var/lib/postgresql/data (volume). Note the volume name on the left; you will need it later, and it may not be exactly postgres_data if your compose project has a different name prefix (for example hudu2_postgres_data).

2. Measure your database size and confirm free disk space

docker compose exec -T db psql -U "$DB_USERNAME" -tAc \
  "SELECT pg_size_pretty(SUM(pg_database_size(datname))) FROM pg_database;"

df -h .

Make sure the free space shown is comfortably more (aim for 3x) than your database size before continuing.

3. Stop the app and worker

The database stays up for now; only the app tier stops:

docker compose stop app worker

4. Pull the PostgreSQL 18 image

docker pull postgres:18.4

5. Dump the cluster using the target version's pg_dumpall

Using the new version's dump client against the old running server is the recommended approach, because it understands both the old and new formats:

: "${DB_USERNAME:?empty, re-run step 1 in this same terminal before dumping}"

docker run --rm --network "container:$(docker compose ps -q db)" postgres:18.4 \
  pg_dumpall -h localhost -U "$DB_USERNAME" --clean --if-exists > pg_upgrade_dumpall.sql

tail -3 pg_upgrade_dumpall.sql   # should end with "PostgreSQL database cluster dump complete"
du -h pg_upgrade_dumpall.sql

6. Stop the database and back up docker-compose.yml

docker compose stop db
cp docker-compose.yml docker-compose.yml.bak.$(date +%Y%m%d%H%M%S)

7. Edit docker-compose.yml: new image, new volume

Change the db service's image and volume mount, and declare the new volume alongside the old one. Do not remove the old volume declaration, as it is your rollback path:

 volumes:
+  postgres18_data: {}
   postgres_data: {}
 services:
   db:
-    image: 'postgres:12.2'
+    image: 'postgres:18.4'
     restart: unless-stopped
     volumes:
-      - postgres_data:/var/lib/postgresql/data
+      - postgres18_data:/var/lib/postgresql
     env_file:
       - '.env'

Note that the mount path itself changes too, from /var/lib/postgresql/data to /var/lib/postgresql. That is intentional: the official PostgreSQL 18 image manages the versioned data directory itself underneath that mount point.

You can edit this by hand, or with sed if your compose file matches the reference layout exactly:

sed -i.orig \
  -e "s#image: 'postgres:12.2'#image: 'postgres:18.4'#" \
  -e "s#- postgres_data:/var/lib/postgresql/data#- postgres18_data:/var/lib/postgresql#" \
  docker-compose.yml
sed -i.orig "s#^volumes:#volumes:\n  postgres18_data: {}#" docker-compose.yml
rm -f docker-compose.yml.orig

docker compose config -q && echo "compose file is valid" 

8. Recreate the db container on the new image

docker compose rm -sf db
docker compose up -d db

# wait for it to accept connections
until docker compose exec -T db pg_isready -U "$DB_USERNAME" -q 2>/dev/null; do
  echo "waiting for db..."; sleep 2
done

docker compose exec -T db psql -U "$DB_USERNAME" -tAc "SHOW server_version;" 

Confirm the version printed is 18.x before continuing. If it is not, stop here; something is wrong with the compose edit, and restoring into it would be restoring into the wrong place.

9. Restore the dump

docker compose exec -T db psql -U "$DB_USERNAME" -d postgres < pg_upgrade_dumpall.sql > restore.log 2>&1

# check for anything other than expected "--clean --if-exists" noise
grep -iE 'error|fatal' restore.log \
  | grep -viE 'current user cannot be dropped|role ".*" already exists|database ".*" already exists'

That last command should print nothing. If it prints anything, read it closely before deciding whether to proceed, because it means something restored abnormally.

10. Verify the data landed, then re-analyze

docker compose exec -T db psql -U "$DB_USERNAME" -d "$DB_NAME" -tAc \
  "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';"

docker compose exec -T db vacuumdb -U "$DB_USERNAME" --all --analyze-in-stages

The table count should look right for your install. ANALYZE rebuilds the query planner's statistics, which are empty right after a restore; skipping this can mean degraded query performance until it is run.

11. Persistence check

Recreate the container once more (not just restart) to prove the data is really in the named volume and not some transient state:

docker compose rm -sf db
docker compose up -d db
until docker compose exec -T db pg_isready -U "$DB_USERNAME" -q 2>/dev/null; do sleep 2; done

docker compose exec -T db psql -U "$DB_USERNAME" -d "$DB_NAME" -tAc \
  "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';" 

Compare this count against step 10's; it should be identical.

12. Bring the whole stack back up

docker compose up -d
docker compose ps

Log into Hudu and confirm everything looks right: assets, articles, and attachments are all present and load correctly. Give it a day or two of normal use before moving on to cleanup, especially on a larger install.

Rollback

If something goes wrong at any point, or you just want to undo the upgrade after the fact, your original PostgreSQL 12 data is still sitting in its original volume. Nothing in this procedure deletes it:

docker compose stop app worker db
cp docker-compose.yml.bak.<timestamp> docker-compose.yml
docker compose rm -sf db
docker compose up -d

Cleaning up (optional, once you're confident)

Once you are satisfied the upgrade is solid and no longer need the rollback path, reclaim the disk space the old volume is using. Use the volume name you noted in step 1; do not guess it:

docker volume ls              # confirm the exact name if you're not sure
docker volume rm <old-volume-name-from-step-1>

Troubleshooting

The suggested container configuration for 18+ is to place a single mount
at /var/lib/postgresql which will then place PostgreSQL data in a
subdirectory, allowing usage of "pg_upgrade --link" without mount point
boundary issues.