En

Migration of PostgreSQL to the orchestrator-pg:v5.9.2+ container#

Migrating PostgreSQL to Chainguard#

⚠️ Important: For PostgreSQL, the move to orchestrator-pg:v5.9.1+ must not be done on top of the old volume. The new image may ship with a newer PostgreSQL major version, and the old data directory will not start. There is only one safe scenario: pg_dump -> new volume -> restore.

1. What to prepare in advance#

The server must have:

  • the Sherpa Orchestrator installation directory;
  • an up-to-date docker-compose.yml;
  • a .env file with working values for POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DBNAME;
  • enough free disk space for the dump file and the new volume;
  • permission to run docker compose, docker exec, and docker volume.

Check the current values:

cd /opt/SherpaOrchestrator

grep -E "^(DB_ENGINE|POSTGRES_HOST|POSTGRES_PORT|POSTGRES_USER|POSTGRES_PASSWORD|POSTGRES_DBNAME|POSTGRES_SCHEMA_NAME)=" .env
💡 What the result should look like

You should see the PostgreSQL connection parameters from .env.

It is especially important to verify:

  • DB_ENGINE=pgsql
  • POSTGRES_HOST=orchestrator-pg for the bundled Docker database
  • POSTGRES_DBNAME=orchestrator

If .env points to an external PostgreSQL instance, this guide does not apply.

2. Check the current container and volume#

docker ps --format "table {{.Names}}\t{{.Image}}\t{{.Status}}" | grep orchestrator-pg

docker volume ls | grep orchestrator-postgres

Also check the PostgreSQL version:

docker exec orchestrator-pg sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DBNAME" -tAc "SELECT version();"'
💡 Why this is needed

If the current volume was initialized, for example, with PostgreSQL 14, while chainguard/postgres:latest is already on PostgreSQL 18, the new container will not be able to use the old data directory directly.

3. Put Orchestrator into maintenance mode#

Before the dump, stop the application so nobody writes to the database:

docker compose stop orchestrator orchestrator-nginx orchestrator-websocket orchestrator-vnc-proxy orchestrator-autopilot

If some services are not used in your setup, Docker will simply report that.

Make sure the database container is still running:

docker ps --format "table {{.Names}}\t{{.Status}}" | grep orchestrator-pg

4. Create a logical backup of PostgreSQL#

Create a backup directory:

sudo mkdir -p ./backups/postgresql
sudo chown -R "$USER:$USER" ./backups
export PG_MIGRATION_TS="$(date +%Y%m%d_%H%M%S)"
export PG_BACKUP_FILE="./backups/postgresql/orchestrator_${PG_MIGRATION_TS}.sql"

Create the dump:

docker exec orchestrator-pg sh -lc 'pg_dump \
  -U "$POSTGRES_USER" \
  -d "$POSTGRES_DBNAME" \
  --clean \
  --if-exists \
  --no-owner \
  --no-privileges' > "$PG_BACKUP_FILE"

Check that the file was created:

ls -lh "${PG_BACKUP_FILE}"
tail -n 20 "${PG_BACKUP_FILE}"
💡 Why pg_dump is used

pg_dump transfers the structure and data at the SQL level and does not depend on the internal data directory format. That makes it safe when moving between PostgreSQL major versions.

5. Stop the old PostgreSQL container#

docker compose stop orchestrator-pg
docker ps -a --format "table {{.Names}}\t{{.Status}}" | grep orchestrator-pg

Do not remove the old volume yet. It is needed for rollback.

6. Create a new volume for Chainguard PostgreSQL#

Check the old volume:

docker volume ls | grep orchestrator-postgres

Create a new volume:

docker volume create orchestrator-postgres-chainguard

Verify the creation:

docker volume inspect orchestrator-postgres-chainguard

7. Switch the client compose file to the new volume#

Open docker-compose.client.yml and in the orchestrator-pg service leave the old volume commented out and enable the new one:

volumes:
  # The old volume is kept as a reference during migration:
  # - orchestrator-postgres:/data/postgres
  - orchestrator-postgres-chainguard:/data/postgres

In the volumes: section at the bottom of the file, the new volume should also be active, and the old one should remain commented out:

volumes:
  # orchestrator-postgres:
  #   driver: local

  orchestrator-postgres-chainguard:
    driver: local

Also make sure that the orchestrator-pg image has already been built or pulled with the chainguard/postgres:latest base.

docker image ls orchestrator-pg
💡 Why the volume changes in compose

The new container must start on an empty data directory. If the old volume stays active, PostgreSQL will try to open the old data directory and fail because of version incompatibility.

8. Start the new PostgreSQL container#

Start PostgreSQL only:

docker compose --profile pg up -d orchestrator-pg

Check the logs:

docker logs -f --tail=100 orchestrator-pg

When the container is ready, verify the connection:

docker exec orchestrator-pg sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DBNAME" -tAc "SELECT 1;"'

Expected result:

1

9. Restore the dump into the new container#

docker exec -i orchestrator-pg sh -lc 'psql \
  -U "$POSTGRES_USER" \
  -d "$POSTGRES_DBNAME" \
  -v ON_ERROR_STOP=1' < "$PG_BACKUP_FILE"

After the restore, run basic checks:

docker exec orchestrator-pg sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DBNAME" -tAc "SELECT COUNT(*) FROM orchestrator.accounts;"'

docker exec orchestrator-pg sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DBNAME" -tAc "SELECT COUNT(*) FROM orchestrator.processes;"'

10. Verify the schema and data#

Check that the working schema exists:

docker exec orchestrator-pg sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DBNAME" -tAc "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '\''orchestrator'\'';"'

Check that the admin user is still present:

docker exec orchestrator-pg sh -lc 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DBNAME" -tAc "SELECT COUNT(*) FROM orchestrator.accounts WHERE login = '\''admin'\'';"'

If you have your own control tables, also verify them with SELECT COUNT(*).

11. Bring the application back up#

docker compose up -d orchestrator orchestrator-websocket orchestrator-nginx

If you use additional services:

docker compose up -d orchestrator-vnc-proxy
docker compose up -d orchestrator-autopilot
docker compose up -d orchestrator-vault

Check that the backend sees the database:

docker logs --tail=100 orchestrator

Check the UI and authentication.

12. Rollback if something goes wrong#

If anything goes wrong after the migration:

  1. Stop the new containers:
docker compose stop orchestrator orchestrator-nginx orchestrator-websocket orchestrator-pg
  1. Restore the old volume in docker-compose.yml:
volumes:
  - orchestrator-postgres:/data/postgres
  1. Start the old PostgreSQL image version that originally worked with the volume.
  2. Start the services again:
docker compose --profile pg up -d orchestrator-pg
docker compose up -d orchestrator orchestrator-websocket orchestrator-nginx

13. What can be removed after a successful migration#

Do not remove anything immediately after switching.

Once you are sure the new database is stable:

  • keep the SQL backup in a separate location;
  • keep the old volume for the monitoring period;
  • only then remove the old volume manually.

Command to remove the old volume:

docker volume rm orchestrator-postgres

Use it only after full verification and only if you are certain rollback is no longer needed.