Upgrade PostgreSQL
Origin by Corvus follows the PostgreSQL community versioning policy, which provides 5 years of support per major version. When a PostgreSQL version reaches end-of-life, Origin by Corvus drops support for it in a subsequent release. See the software and hardware requirements documentation for the currently supported PostgreSQL versions.
When upgrading PostgreSQL, refer to the official PostgreSQL upgrade documentation for comprehensive guidance. This page covers the steps specific to Origin by Corvus deployments.
Before you begin
- Back up your database. Always take a full database backup before upgrading. See the backup and disaster recovery documentation.
- Check supported versions. Confirm the target PostgreSQL version is supported by your Origin by Corvus release. See software and hardware requirements.
- Stop Origin by Corvus. Shut down the Origin by Corvus server before starting the database upgrade to prevent data writes during the process.
Upgrade a bare-metal PostgreSQL server
There are two main approaches for upgrading PostgreSQL on a bare-metal or virtual machine:
- pg_upgrade (in-place): Faster; upgrades the data directory without a full dump/restore cycle. Recommended for large databases.
- pg_dump / pg_restore (logical): Simpler and safer for cross-machine migrations or when in-place upgrade is not possible.
Using pg_upgrade
pg_upgrade allows you to upgrade between major PostgreSQL versions without a full export. Both the old and new PostgreSQL versions must be installed side-by-side.
-
Install the new PostgreSQL version alongside the existing one using your package manager.
-
Stop the existing PostgreSQL service:
sudo systemctl stop postgresql -
Run
pg_upgradeas thepostgresuser, specifying the binary and data directories for both versions. Replace<old_version>and<new_version>with the appropriate version numbers (e.g.15and16):sudo -u postgres /usr/lib/postgresql/<new_version>/bin/pg_upgrade \--old-datadir /var/lib/postgresql/<old_version>/main \--new-datadir /var/lib/postgresql/<new_version>/main \--old-bindir /usr/lib/postgresql/<old_version>/bin \--new-bindir /usr/lib/postgresql/<new_version>/bin -
Update your system to start the new PostgreSQL version by default, then start the service:
sudo systemctl start postgresql
For full pg_upgrade reference, see the official pg_upgrade documentation.
Using pg_dump and pg_restore
This approach exports the entire database, installs the new PostgreSQL version, and restores the data. It is simpler but requires downtime proportional to database size.
-
Export the Origin by Corvus database (replace
originwith your database name andmmuserwith your database user):pg_dump -U mmuser -Fc origin > origin_backup.dump -
Install the new PostgreSQL version and create the database user and database:
sudo -u postgres psql -c "CREATE USER mmuser WITH PASSWORD 'your_password';"sudo -u postgres psql -c "CREATE DATABASE origin OWNER mmuser;" -
Restore the database into the new PostgreSQL instance:
pg_restore -U mmuser -d origin origin_backup.dump
Upgrade PostgreSQL in Docker
When running PostgreSQL in Docker, pg_dump/pg_restore is the recommended upgrade approach. In-place pg_upgrade is complex in containers because it requires both old and new binaries in the same container.
-
Stop the Origin by Corvus container:
docker stop origin -
Dump the database from the running PostgreSQL container (replace
origin,mmuser, anddbwith your database name, user, and container name):docker exec postgres pg_dump -U mmuser -Fc origin > origin_backup.dump -
Stop the existing PostgreSQL container:
docker stop postgres -
Update
POSTGRES_IMAGE_TAGin your.envfile to the new version. For example:POSTGRES_IMAGE_TAG=16-alpine -
Recreate the PostgreSQL container so the new image tag from
.envis applied:docker compose up -d --force-recreate postgresNote
If your volume already contains data from the old major version, PostgreSQL will refuse to start. In that case, create a new named volume for the new container, then restore from the dump in the next step.
-
Recreate the database user and database in the new container, then restore:
docker exec -i postgres psql -U postgres -c "CREATE USER mmuser WITH PASSWORD 'your_password';"docker exec -i postgres psql -U postgres -c "CREATE DATABASE origin OWNER mmuser;"docker exec -i postgres pg_restore -U mmuser -d origin < origin_backup.dump
After the upgrade
After upgrading PostgreSQL, run ANALYZE VERBOSE on the Origin by Corvus database. This re-populates the pg_statistic table used by PostgreSQL to generate optimal query plans. Skipping this step can result in degraded database performance.
sudo -u postgres psql -d origin -c "ANALYZE VERBOSE;"
Once complete, restart Origin by Corvus:
sudo systemctl start origin