Postgres Upgrade 13.3

Upgrades page: https://docs.chef.io/server/upgrades/

Standalone Internal Postgres

Upgrade steps

1. Vacuum full (recommendation)
2. Stop the chef-server
3. Take a copy of the /opt/opscode/embedded/postgresql/* (need to remove this step)
4. Download the upgrade build (14.6.33+20210719175317 used for testing)
5. Install the package
6. Restore /opt/opscode/embedded/postgresql/* (need to remove this step)
7. Upgrade the chef-server
8. Start and cleanse the chef-server
9. Reindex the database

Testing data

1. Install version -> 14.5.29
2. Upgrade version -> 14.6.33+20210719175317
3. PG data -> 158G /var/opt/opscode/postgresql/9.6/data
4. Number of nodes -> 565596
5. opscode_chef size -> 49 GB
6. bifrost -> 68 GB
7. oc_id -> 27 GB
8. machine type -> t3.2xlarge

Vacuum testing data

1. Total time take for vacuum full -> 183m49.091s
2. PG data -> 21G /var/opt/opscode/postgresql/9.6/data (reduced from 158G)
3. Number of nodes -> 565596
4. opscode_chef size -> 21 GB (reduced from 49 GB)
5. bifrost -> 7127 kB (reduced from 68 GB)
6. oc_id -> 30 MB (reduced from 27 GB)

Upgrade testing data

1. Total time take for upgrade process -> approx 15 min
2. PG data -> 21G /var/opt/opscode/postgresql/13.3/data (same as before)
3. Number of nodes -> 565596
4. opscode_chef size -> 21 GB (same as before)
5. bifrost -> 8949 kB (increased from 7127 kB)
6. oc_id -> 32 MB (increased from 30 MB)

Reindex testing data

1. Total time take for reindex process -> approx 5 min
2. PG data -> 21G /var/opt/opscode/postgresql/13.3/data (same as before)
3. Number of nodes -> 565596
4. opscode_chef size -> 20 GB (reduced from 21 GB)
5. bifrost -> 8773 kB (reduced from 8949 kB)
6. oc_id -> 32 MB (same as before)

Recommendation and Results

1. Running vacuum full before the upgrade is recommended as it will reduce the data size to optimal and all the further steps will take less time
2. Need same amount of free space as the pg data consumes
3. Total time take for vacuuming, upgrading and reindexing 158G data is approximately 3.5 hr

Tiered Internal Postgres

Upgrade steps

1. Vacuum full (recommendation)
2. Stop the front end chef server only
3. Take a copy of the /opt/opscode/embedded/postgresql/* (need to remove this step)
4. Download and dpkg install the upgrade build on front end(14.7.11+20210727061052 used for testing)
5. Restore /opt/opscode/embedded/postgresql/* (need to remove this step)
6. Stop the back end chef server
7. Take a copy of the /opt/opscode/embedded/postgresql/* (need to remove this step)
8. Download and dpkg install the upgrade build on back end(14.7.11+20210727061052 used for testing)
9. Restore /opt/opscode/embedded/postgresql/* (need to remove this step)
10. Upgrade the chef-server on the back end
11. Zip and copy the /etc from back end to front end and unzip /etc on front end
12. Reconfigure and start the chef-server on front end

Testing data (already vacuumed data)

1. Install version -> 14.5.29
2. Upgrade version -> 14.7.11+20210727061052
3. PG data -> 23G /var/opt/opscode/postgresql/9.6/data/
4. Number of nodes -> 565596
5. opscode_chef size -> 22 GB
6. bifrost -> 8311 kB
7. oc_id -> 7327 kB
8. machine type -> t3.medium (extended the storage to 150 GB)

Upgrade testing data

1. Total time take for upgrade process -> approx 20 min
2. PG data -> 22G /var/opt/opscode/postgresql/13.3/ (reduced from 23 GB)
3. Number of nodes -> 565596
4. opscode_chef size -> 22 GB (same as before)
5. bifrost -> 8885 kB (increased from 8311 kB)
6. oc_id -> 7909 kB (increased from 7327 kB)

Recommendation and Results

1. Include vacuum full process
2. Need same amount of free space as the pg data consumes
3. Total time take for upgrading 23GB vacuumed data is approximately 20 min

External Postgres

Upgrade steps

chef-server box

1. Download the upgrade build (typically using curl or mixlib).
2. Install the package.
   sudo rpm -U -v --force <path-to-package>
   or
   sudo dpkg -iEG <path-to-package>
3. Upgrade chef-server.

sudo CHEF_LICENSE='accept' chef-server-ctl upgrade
sudo chef-server-ctl start
sudo chef-server-ctl cleanup

postgresql box

1. Update packages and install the new PostgreSQL 13.3.
2. Check if there are any differences in the config files.Make sure to update them if required

diff /etc/postgresql/12/main/postgresql.conf /etc/postgresql/13/main/postgresql.conf
diff /etc/postgresql/12/main/pg_hba.conf /etc/postgresql/13/main/pg_hba.conf

3. Stop the PostgreSQL service. sudo systemctl stop postgresql.service
4. Log in as the postgres user. su postgres
5. Check clusters (notice the --check argument, this will not change any data).

/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

6. Migrate the data (without the --check argument).

/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'

7. Go back to the regular user. exit
8. Swap the ports for the old and new PostgreSQL versions.

sudo vim /etc/postgresql/13/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"

sudo vim /etc/postgresql/9.6/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"

9. Start the PostgreSQL service. sudo systemctl start postgresql.service
10. Log in as the postgres user again and Check the new PostgreSQL version.

sudo su postgres
psql -c "SELECT version();"

11. log in to the chef-server box and see the status chef-server-ctl status