Postgres Upgrade 13.3

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 verison -> 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

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

su opscode-pgsql

bash

cd /opt/opscode/embedded/postgresql/13.3/bin

time ./psql -U opscode-pgsql -d opscode_chef -c 'REINDEX DATABASE opscode_chef' real 3m11.138s user 0m0.003s sys 0m0.000s

time ./psql -U opscode-pgsql -d bifrost -c 'REINDEX DATABASE bifrost' real 0m1.138s user 0m0.003s sys 0m0.000s

time ./psql -U opscode-pgsql -d oc_id -c 'REINDEX DATABASE oc_id' real 0m1.147s user 0m0.003s sys 0m0.000s

check the data

du -hs /var/opt/opscode/postgresql/13.3/data 21G /var/opt/opscode/postgresql/13.3/data/

chef-server-ctl psql oc_erchef <<<"select count(*) from nodes; \q" 565596

chef-server-ctl psql oc_erchef <<<"SELECT pg_size_pretty( pg_database_size('opscode_chef') ); \q" 20 GB

chef-server-ctl psql bifrost <<<"SELECT pg_size_pretty( pg_database_size('bifrost') ); \q" 8773 kB

chef-server-ctl psql oc_id <<<"SELECT pg_size_pretty( pg_database_size('oc_id') ); \q" 32 MB

Tiered Internal Postgres

External Postgres