Skip to content

Postgres Upgrade 13.3

Jump to bottom
jashaik edited this page Jan 7, 2022 · 33 revisions

Upgrades page: https://docs.chef.io/server/upgrades/ Code for upgrades: https://github.com/chef/chef-server/blob/master/docs-chef-io/content/server/upgrades.md

Standalone Internal Postgres

Upgrade steps

  1. It is strongly recommended to run Vacuum full for smooth upgrade $BINDIR/vacuumdb --all –full
  2. Run ANALYZE on the PostgreSQL database.This process gathers statistics for the query planner to create the most efficient query execution paths. Accurate statistics will help the planner to choose the most appropriate query plan, and thereby improve the speed of query processing. $BINDIR/vacuumdb --all --analyze-only
  3. Stop the chef-server chef-server-ctl stop
  4. Download the upgrade build (14.6.33+20210719175317 used for testing)
  5. Install the package dpkg -iEG /path/to/chef-server-core-<version>.deb or rpm -Uvh --nopostun /path/to/chef-server-core-<version>.rpm
  6. Upgrade the chef-server CHEF_LICENSE='accept' chef-server-ctl upgrade
  7. Start and cleanse the chef-server 
    chef-server-ctl start
chef-server-ctl cleanup
  8. Reindex the database $BINDIR/reindexdb --all

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
  1. Stop the PostgreSQL service. sudo systemctl stop postgresql.service
  2. Log in as the postgres user. su postgres
  3. 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
  1. 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'
  1. Go back to the postgres user. exit
  2. 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"
  1. Start the PostgreSQL service. sudo systemctl start postgresql.service
  2. Log in as the postgres user again and Check the new PostgreSQL version.
sudo su postgres
psql -c "SELECT version();"
  1. log in to the chef-server box and see the status chef-server-ctl status

RDS Postgres

create steps

https://aws.amazon.com/getting-started/hands-on/create-connect-postgresql-db/

upgrade steps

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

Draft Customer Instructions, Standalone Scenario

  1. It is strongly recommended to run vacuum full before starting the upgrade.
$ sudo su postgres
$ cd /opt/opscode/embedded/postgres/<version>/bin/
$ /usr/bin/vacuumdb --all --full
$ exit

  1. Consult the documentation on chef-server-ctl backup. https://docs.chef.io/server/server_backup_restore/

    Backup the database. MAKE A NOTE OF WHERE THE BACKUP WAS PLACED (normally under /var/opt/chef-backup). Please note that chef-server will go offline to perform the backup. Example:

$ sudo chef-server-ctl backup

  1. If you are running chef-server version 12.17.15 or greater, skip this step and proceed with step 4 below. If you are running a chef-server release prior to 12.17.15, you cannot upgrade directly to 14.8.X. You must perform a stepped upgrade first.

    If you are running chef-server 12.3.0, you must first upgrade 12.17.15. If you are running chef-server 11, you must first upgrade to 12.3.0, and then to 12.17.15. Please consult the docs linked below for information on these upgrades.

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

    After performing the stepped upgrade, return here and continue with step 4 below.

  2. Download the chef-server version 14.8.X upgrade package.

https://downloads.chef.io/tools/infra-server

  1. Stop services.
$ # NOTE: umbrella scenarios stop rabbitmq, so I'm including that below.  Correct?  Incorrect?
$ sudo chef-server-ctl stop || sudo chef-server-ctl kill rabbitmq

  1. WE MAY NOT USE THIS - THIS STEP MAY BE DELETED

    Move the postgresql directory to a safe location or it could be deleted and cause errors. Example:

$ sudo mkdir /my-postgresql-dir/opt-opscode-embedded-postgresql
$ sudo mv --verbose /opt/opscode/embedded/postgresql/* /my-postgresql-dir/opt-opscode-embedded-postgresql
  1. Install the chef-server 14.8.X package.

dpkg: $ sudo dpkg -iEG /path/to/chef-server-core-<version>.deb

rpm: $ sudo rpm -U /path/to/chef-server-core-<version>.rpm

  1. WE MAY NOT USE THIS - THIS STEP MAY BE DELETED

    Restore the previously-moved postgresql directory. Example:

$ sudo cp --force --recursive --verbose /my-postgresql-dir/opt-opscode-embedded-postgresql/* /opt/opscode/embedded/postgresql

  1. Upgrade chef-server.

$ sudo CHEF_LICENSE='accept' chef-server-ctl upgrade

  1. If the upgrade failed, SKIP THIS STEP and continue at step X below. If the upgrade was successful, start services, cleanup, and reindex the database.
$ sudo chef-server-ctl start
$ sudo chef-server-ctl cleanup
$ sudo /usr/bin/reindexdb --all

You are now finished with the chef-server upgrade. DO NOT PROCEED FURTHER.

  1. If the upgrade failed, and you are left with a corrupted chef-server and/or corrupted database, it is strongly recommended that you DO NOT UNDER ANY CIRCUMSTANCES RISK YOUR BACKUP OF THE DATABASE. You should take any and all steps in order to preserve the backup, including copying it to another disk.

    Reinstall the original version of chef-server you were using before attempting the upgrade process (if you had to perform a stepped upgrade, install your original version of chef-server before the stepped upgrade, not any of the versions in the stepped upgrade process). Again, it is recommended that you DO NOT RISK YOUR BACKUP OF THE DATABASE. Use a separate disk from your backup for the installation.

    Download chef-server: https://docs.chef.io/server/install_server/

    Install instructions for chef-server: https://docs.chef.io/server/install_server/

  2. Consult the documentation on restore, and restore the database from the path where it was saved previously. https://docs.chef.io/server/server_backup_restore/

    Example: chef-server-ctl restore /var/opt/chef-backup/chef-backup-2021-06-11-18-59-19.tgz

Draft Customer Instructions, External PostgreSQL Scenario

Vacuum the PostgreSQL database.

  1. Log into the external PostgreSQL machine.

  2. Run vacuum full. Example:

$ sudo su postgres
$ /usr/bin/vacuumdb --all --full
vacuumdb: vacuuming database "bifrost"
vacuumdb: vacuuming database "oc_id"
vacuumdb: vacuuming database "opscode_chef"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
$ exit
  1. Log out of the external PostgreSQL machine.

Backup the PostgreSQL database.

  1. Log into the chef-server machine.

  2. Consult the documentation on knife-ec-backup. https://blog.chef.io/migrating-chef-server-knife-ec-backup-knife-tidy

    If it is not already installed, install knife-ec-backup. A sample session follows (note that your steps could differ, depending on the versions of your software, the topology of your setup, your OS and distrubition, and a range of other factors). Example:

$ apt-get update
$ apt install ruby
$ apt install make
$ curl -L https://chef.io/chef/install.sh | sudo bash -s -- -P chefdk
$ export PATH=$PATH:/root/.chefdk/gem/ruby/2.6.0/bin
$ apt-get -y install gcc postgresql libpq-dev
$ /opt/chefdk/embedded/bin/gem install knife-ec-backup -- --with-pg-config=/opt/opscode/embedded/postgresql/9.6/bin/pg_config
  1. If it is not already configured, configure knife. A sample session follows (again, note that your steps could differ, depending on a range of factors). Example:
$ chef-server-ctl org-create 4thcafe 'Fourth Cafe, Inc.' --association_user janedoe --filename /tmp/4thcafe-validator.pem
$ chef generate repo chef-repo
$ cd chef-repo/
$ mkdir -p .chef
$ echo '.chef' >> .gitignore
$ cp /tmp/4thcafe-validator.pem .chef
$ cp /home/ubuntu/janedoe.pem .chef
$ cd .chef
$ knife configure
WARNING: No knife configuration file found. See https://docs.chef.io/config_rb/ for details.
Please enter the chef server URL: [https://1.2.3.4/organizations/myorg] https://1.2.3.4/organizations/4thcafe
Please enter an existing username or clientname for the API: [ubuntu] janedoe
$ knife ssl fetch
  1. Backup the database. Example:
$ mkdir /backup
$ /opt/chefdk/embedded/bin/knife ec backup /backup

Upgrade Chef Server.

  1. If you are running chef-server version 12.17.15 or greater, skip this step and proceed with step X below. If you are running a chef-server release prior to 12.17.15, you cannot upgrade directly to 14.8.X. You must perform a stepped upgrade first.

    If you are running chef-server 12.3.0, you must first upgrade 12.17.15. If you are running chef-server 11, you must first upgrade to 12.3.0, and then to 12.17.15. Please consult the docs linked below for information on these upgrades.

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

    After performing the stepped upgrade, return here and continue these instructions at step X below.

  2. Download the chef-server version 14.8.X upgrade package.

https://downloads.chef.io/tools/infra-server

  1. Stop services.
$ # NOTE: umbrella scenarios stop rabbitmq, so I'm including that below.  Correct?  Incorrect?
$ sudo chef-server-ctl stop || sudo chef-server-ctl kill rabbitmq

  1. WE MAY NOT USE THIS - THIS STEP MAY BE DELETED

    Move the PostgreSQL directory to a safe location or it could be deleted and cause errors. Example:

$ sudo mkdir /my-postgresql-dir/opt-opscode-embedded-postgresql
$ sudo mv --verbose /opt/opscode/embedded/postgresql/* /my-postgresql-dir/opt-opscode-embedded-postgresql
  1. Install the chef-server 14.8.X package.

dpkg: $ sudo dpkg -iEG /path/to/chef-server-core-<version>.deb

rpm: $ sudo rpm -U /path/to/chef-server-core-<version>.rpm

  1. WE MAY NOT USE THIS - THIS STEP MAY BE DELETED

    Restore the previously-moved postgresql directory. Example:

$ sudo cp --force --recursive --verbose /my-postgresql-dir/opt-opscode-embedded-postgresql/* /opt/opscode/embedded/postgresql

  1. Upgrade chef-server.

$ sudo CHEF_LICENSE='accept' chef-server-ctl upgrade

  1. IF THE UPGRADE WAS SUCCESSFUL, start services and cleanup.
$ sudo chef-server-ctl start
$ sudo chef-server-ctl cleanup

You are now finished with the chef-server upgrade. Proceed directly to the Upgrade PostgreSQL section.

  1. IF THE UPGRADE FAILED, and you are left with a corrupted chef-server and/or corrupted database, it is strongly recommended that you DO NOT UNDER ANY CIRCUMSTANCES RISK YOUR BACKUP OF THE DATABASE. You should take any and all steps in order to preserve the backup, including copying it to another disk.

  2. Reinstall the original version of chef-server you were using before attempting the upgrade process (if you had to perform a stepped upgrade, install your original version of chef-server before the stepped upgrade, not any of the versions in the stepped upgrade process). Again, it is recommended that you DO NOT RISK YOUR BACKUP OF THE DATABASE. Use a separate disk from your backup for the installation.

    Download chef-server here: https://docs.chef.io/server/install_server/

    Install instructions for chef-server: https://docs.chef.io/server/install_server/

  3. Consult the documentation on knife-ec-restore, and restore the database from the path where it was saved previously. https://blog.chef.io/migrating-chef-server-knife-ec-backup-knife-tidy

    Example: $ /opt/chefdk/embedded/bin/knife ec restore /backup/

  4. Do not proceed further until you have an uncorrupted Chef Server upgraded to version X.Y.Z, and an uncorrupted PostgreSQL database.

Upgrade PostgreSQL

  1. Log out of the chef-server machine.

  2. Log into the external PostgreSQL machine.

  3. Update packages and install the new PostgreSQL 13.3. Example (Ubuntu):

$ sudo apt-get update
$ sudo apt-get install postgresql-13
  1. 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
  1. Stop the PostgreSQL service. $ sudo systemctl stop postgresql.service
  2. Log in as the postgres user. $ su postgres
  3. 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
  1. 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'
  1. Log out of the postgres user. $ exit
  2. Swap the ports for the old and new PostgreSQL versions.
$ sudo vim /etc/postgresql/13/main/postgresql.conf
# change "port = 5433" to "port = 5432"

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

  1. Start the PostgreSQL service. $ sudo systemctl start postgresql.service

  2. Log in as the postgres user again and confirm that the new PostgreSQL version is 13.3.

$ sudo su postgres
$ psql -c "SELECT version();"
                                                                   version
---------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 (Ubuntu 13.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
(1 row)
  1. Run reindexdb. Example:
$ /usr/bin/reindexdb --all
reindexdb: reindexing database "bifrost"
reindexdb: reindexing database "oc_id"
reindexdb: reindexing database "opscode_chef"
reindexdb: reindexing database "postgres"
reindexdb: reindexing database "template1"

  1. Log out of the external PostgreSQL machine.

  2. Log into the chef-server machine.

  3. Check the status of chef-server. PostgreSQL should be connected.

$ sudo chef-server-ctl status
-------------------
 Internal Services
-------------------
run: bookshelf: (pid 15763) 219163s; run: log: (pid 16559) 228464s
run: elasticsearch: (pid 15797) 219162s; run: log: (pid 16345) 228507s
run: nginx: (pid 15901) 219162s; run: log: (pid 16745) 228452s
run: oc_bifrost: (pid 15909) 219161s; run: log: (pid 16237) 228519s
run: oc_id: (pid 15915) 219161s; run: log: (pid 16255) 228513s
run: opscode-erchef: (pid 15948) 219160s; run: log: (pid 16673) 228458s
run: redis_lb: (pid 15952) 219160s; run: log: (pid 16779) 228445s
-------------------
 External Services
-------------------
run: postgresql: connected OK to 10.0.11.0:5432
Clone this wiki locally