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
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. Include vacuum full process
2. Need same amount of free space as the pg data consumes
3. Total time take for 158G data is approximately 3.5 hr

Tiered Internal Postgres

External Postgres