Testing PG upgrade from 9.6 to 13 with chef-server 14.11.36

[vivekshankar1]

User Story

As a developer i want to try out the external postres upgrade from 9.6 to 13. Automate will have chef-server 14.11.36.
We also want to test and merge the PR: #6183 (this has the latest chef-server version)

Latest branch of Automate having Latest chef-server(14.11.31), running embedded chef-server with External 9.6.11, add data, Then Upgrade External PG to version 13.4

• Test putting in all data in small quantities -> 1000 nodes(client run, compliance scan), Measure time taken backup, vacuum, pg_upgrade, re-indexing (with size)
• Test putting in bulk data in large quantise -> 10,000 nodes(client run, compliance scan), Measure time taken backup, vacuum, pg_upgrade, re-indexing (with size)

Definition of Done

Results of the upgrade with load testing data

Demo Script / Repro Steps

[vivekshankar1]

Backing up automate

1. Time taken to backup automate : 1min 29sec

Testing data

1. pg version : 9.6.24
2. Upgraded to : 13.5.1
3. PG data -> 1.7 GB /var/lib/postgresql/9.6/main
4. Number of nodes -> 299770
5. Databases combined size - 1439 MB
6. machine type -> m5.xlarge

Vacuum testing data

1. Total time take for vacuum full -> 41sec
2. PG data -> 1.5 G /var/lib/postgresql/9.6/main (reduced from 1.6GB)
3. Number of nodes -> 299770
4. Database combined size -> 1290 MB

Upgrade testing data

1. Total time take for upgrade process -> 29 secs
2. PG data -> 1.4Gb /var/lib/postgresql/13/main (same as before)
3. Number of nodes -> 299770
4. Database combined size -> 1339 MB

Reindex testing data

1. Total time take for reindex process -> 26 sec
2. PG data -> 1.4Gb /var/lib/postgresql/13/main (same as before)
3. Number of nodes -> 299770
4. Database combined size -> 1315 MB

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. Running pg_upgrade with or without link option does not make any difference in disk space, only difference that can be seen is lower time when running pg_upgrade with link option
4. Time to pg_upgrade with link- 9sec
5. Time to pg_upgrade without link - 27sec