Postgres Upgrade 13.3

Standalone Internal Postgres

get a system with chef-server with 14.5.29 version

chef-server-ctl version 14.5.29

check the data

du -hs /var/opt/opscode/postgresql/9.6/data 158G /var/opt/opscode/postgresql/9.6/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" 49 GB

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

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

Vacumme

su opscode-pgsql

bash

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

time ./vacuumdb --all --full real 183m49.091s user 0m0.006s sys 0m0.000s

check the data

du -hs /var/opt/opscode/postgresql/9.6/data 21G /var/opt/opscode/postgresql/9.6/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" 21 GB chef-server-ctl psql bifrost <<<"SELECT pg_size_pretty( pg_database_size('bifrost') ); \q" 7127 kB chef-server-ctl psql oc_id <<<"SELECT pg_size_pretty( pg_database_size('oc_id') ); \q" 30 MB

Upgrade pg

time sudo chef-server-ctl stop real 0m5.939s user 0m0.739s sys 0m0.155s mkdir /tmp/opt-opscode-embedded-postgresql mv --verbose /opt/opscode/embedded/postgresql/* /tmp/opt-opscode-embedded-postgresql du -hs /tmp/opt-opscode-embedded-postgresql 29M /tmp/opt-opscode-embedded-postgresql time curl -vo /tmp/chef-server.deb https://packages.chef.io/files/unstable/chef-server/14.6.33+20210719175317/ubuntu/18.04/chef-server-core_14.6.33+20210719175317-1_amd64.deb real 0m1.660s user 0m0.229s sys 0m0.633s du -hs /tmp/chef-server.deb 243M /tmp/chef-server.deb time dpkg -iEG /tmp/chef-server.deb real 1m37.425s user 0m49.128s sys 0m11.315s time cp --force --recursive --verbose /tmp/opt-opscode-embedded-postgresql/* /opt/opscode/embedded/postgresql real 0m4.117s user 0m0.024s sys 0m0.135s time CHEF_LICENSE='accept' chef-server-ctl upgrade real 9m15.803s user 0m29.392s sys 0m33.886s du -hs /var/opt/opscode/postgresql/9.6/data/ 21G /var/opt/opscode/postgresql/9.6/data/ du -hs /var/opt/opscode/postgresql/13.3/data/ 21G /var/opt/opscode/postgresql/13.3/data/ time chef-server-ctl start real 0m3.840s user 0m0.730s sys 0m0.168s time chef-server-ctl cleanup real 0m8.775s user 0m5.702s sys 0m2.263s

check the data

du -hs /var/opt/opscode/postgresql/9.6 4.0K /var/opt/opscode/postgresql/9.6/ 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" 21 GB chef-server-ctl psql bifrost <<<"SELECT pg_size_pretty( pg_database_size('bifrost') ); \q" 8949 kB chef-server-ctl psql oc_id <<<"SELECT pg_size_pretty( pg_database_size('oc_id') ); \q" 32 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