External PostgreSQL Upgrade
This guide covers upgrading services used by Chef Automate.
Upgrade Amazon RDS for PostgreSQL
Amazon Web Services Relational Database Service (AWS RDS) should upgrade by Monday, January 17. Amazon is automatically migrating all AWS RDS customers from PostgreSQL from 9.6 to 13 starting Tuesday, January 18, 2022.
To upgrade AWS RDS, please follow instructions on Upgrading the PostgreSQL DB engine for Amazon RDS - Amazon Relational Database Service
Upgrade External PostgreSQL from 9.6 to 13.4
The upgrade process for PostgreSQL from 9.6 to 13.4 requires a one-time downtime to vacuum, upgrade, and re-index the database. The entire upgrade operation takes about five minutes for each 2GB of data in PostgreSQL. This process may take longer depending on your server hardware and the size of the node objects in Chef Automate.
This upgrade guide is for systems running:
- A Single PostgreSQL 9.6 installation
- Using Ubuntu 18.04 or higher
- On a virtual machine such as an EC2 instance or on a single physical machine
- Enough free space to run a second copy of the data that is in the existing PostgreSQL 9.6 installation. This upgrade requires a minimum of 55% free space on the machine.
Backup Chef Automate
Database migrations have inherent risk to your system. Create a backup before beginning any migration or update. This ensures that you have a recoverable state in case any step in the process fails. Copy the backup to a another disk that is not connected to Chef Automate. This ensures that you have state to restore, in case of a failure in the upgrade process.
Follow the Backup documentation to make a copy of your Chef Automate data.
Upgrade Chef Automate
For airgapped Chef Automate systems, follow the instructions in the Airgap Installation Guide to upgrade to the latest version.
Upgrade standard Chef Automate installations to the latest version that supports External PostgreSQL v13 with the command:
chef-automate upgrade run
Stop Chef Automate
After successful upgrade, stop Chef Automate Services.
sudo chef-automate stop
Upgrade from PostgreSQL 9.6 to 13.4
Install PostgreSQL 13.4
SSH or Login to the machine running PostgreSQL 9.6 with user who has sudo or root access.
ssh -i "<xyz.pem>" <sudo_username>@<postgres_machine_ip>
Install PostgreSQL v13
sudo apt-get update sudo apt-get install postgresql-13
If the PostgreSQL v9.6 config was modified or customized. Please check the differences and update the new PostgreSQL v13 config with similar changes. Please connect with your database administrator if you don’t know these changes.
sudo sdiff -s /etc/postgresql/9.6/main/postgresql.conf /etc/postgresql/13/main/postgresql.conf sudo sdiff -s /etc/postgresql/9.6/main/pg_hba.conf /etc/postgresql/13/main/pg_hba.conf
Prepare the Database for Migration
vacuumdb --all --full on the PostgreSQL database if you don’t have automatic vacuuming set up. This process will reduce the size of the database by deleting unnecessary data and speeds up the migration. This operation takes around 1 to 2 minutes per gigabyte of data depending on the complexity of the data, and requires at least as much free disk space as the size of your database.
For more information on upgrading using
vacuumdb see the PostgreSQL 13 documentation for vacuumdb.
sudo su - postgres
Run Vacuum DB before moving data from PostgreSQL v9.6 to v13
vacuumdb --all --full
vacuumdb: vacuuming database "automate-cs-bookshelf" vacuumdb: vacuuming database "automate-cs-oc-bifrost" vacuumdb: vacuuming database "automate-cs-oc-erchef" vacuumdb: vacuuming database "chef_applications_service" vacuumdb: vacuuming database "chef_authn_service" vacuumdb: vacuuming database "chef_authz_service" vacuumdb: vacuuming database "chef_cereal_service" vacuumdb: vacuuming database "chef_compliance_service" vacuumdb: vacuuming database "chef_config_mgmt_service" vacuumdb: vacuuming database "chef_infra_proxy" vacuumdb: vacuuming database "chef_ingest_service" vacuumdb: vacuuming database "chef_license_control_service" vacuumdb: vacuuming database "chef_session_service" vacuumdb: vacuuming database "chef_teams_service" vacuumdb: vacuuming database "chef_user_settings_service" vacuumdb: vacuuming database "data_feed_service" vacuumdb: vacuuming database "dex" vacuumdb: vacuuming database "nodemanager_service" vacuumdb: vacuuming database "notifications_service" vacuumdb: vacuuming database "postgres" vacuumdb: vacuuming database "secrets_service" vacuumdb: vacuuming database "template1"
Exit postgres user
For more information on upgrading using
pg_upgrade --check see the PostgreSQL 13 documentation for pg_upgrade.
Stop Both PostgreSQL Servers:
sudo systemctl stop postgresql.service
sudo su - postgres
Run pg_upgrade check command.
cd ~ /usr/lib/postgresql/13/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/9.6/main \ --new-datadir=/var/lib/postgresql/13/main \ --old-bindir=/usr/lib/postgresql/9.6/bin \ --new-bindir=/usr/lib/postgresql/13/bin \ --old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \ --check
Migrate the Data (run pg_upgrade command without –check):
cd ~ /usr/lib/postgresql/13/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/9.6/main \ --new-datadir=/var/lib/postgresql/13/main \ --old-bindir=/usr/lib/postgresql/9.6/bin \ --new-bindir=/usr/lib/postgresql/13/bin \ --old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf'
Exit the PostgreSQL shell
Swap the ports of PostgreSQL v9.6 and 13.4:
PG_9_6_PORT=5432 # Assuming Postgres v9.6 is currently running on this port PG_13_PORT=5433 # Assuming Postgres 13 is currently running on this port sudo sed -i "s/port = $PG_9_6_PORT/port = $PG_13_PORT/g" /etc/postgresql/9.6/main/postgresql.conf sudo sed -i "s/port = $PG_13_PORT/port = $PG_9_6_PORT/g" /etc/postgresql/13/main/postgresql.conf
Start both PostgreSQL Servers:
sudo systemctl start postgresql.service
Confirm the installed version:
sudo su - postgres psql -c "SELECT version();"
Run the generated
cd ~ ./analyze_new_cluster.sh
Reindexing is not required for Chef Automate. If
pg_upgradereported errors or need for reindexing please refer to pg_upgrade documentation for details.
Exit postgres user:
Cleanup PostgreSQL 9.6
After you have confirmed the installation and you no longer need PostgreSQL 9.6, remove PostgreSQL v9.6 and its data:
sudo su - postgres ./delete_old_cluster.sh
Restart Chef Automate
Start Chef Automate:
sudo chef-automate start
Check that the Chef Automate services are up and running:
sudo chef-automate status
Troubleshoot Upgrade Failures
If the upgrade failed and you are left with a corrupted Chef Automate or a corrupted PostgreSQL database, DO NOT RISK YOUR BACKUP OF AUTOMATE. Take all steps necessary to preserve the backup, including copying it to another disk.
Contact Chef customer support.
If you have configured the backup directory as a location other than the default directory (
/var/opt/chef-automate/backups), you must supply the backup directory path to the
backup restorecommand as shown in the snippet below. Without a backup ID, Chef Automate uses the most recent backup in the backup directory.
To restore on a new host, run:
chef-automate backup restore </path/to/backups/>BACKUP_ID
For other restoration types please refer this Restore
Do not upgrade PostgreSQL until you have an uncorrupted Chef Automate and an uncorrupted PostgreSQL database.