Upgrade Postgres to version 10 for vCloud Director 10 compatibility

You are here because you are working on your vCloud Director upgrade to one of latest version could be 9.7 or 10 and realised you need to upgrade your PostgreSQL to a newer major version for this.

In our demo cloud we had PostgreSql version 9.5 with vCloud Director 9.1 so was about time to upgrade this old version. This time we are upgrading to vCloud Director 9.7 on linux then we will migrate to VCD appliance.

Our PostgreSql runs on Ubuntu and version 9.5 was originally installed from default repos time ago and version 10 was not available.
Reason for this is higher versions are not part of default repos so PostgreSql repo needs to be added to be able to download binaries of higher major versions

Before jumping into this upgrade ensure you run backups of your system and databases for any necessary roll back and ensure you have scheduled outage for your database servers and applications.

This upgrade process requires stopping PostgreSQL service

Configuring repo using script to download binaries

This script will enable the PostgreSQL APT repository on apt.postgresql.org on your system. The distribution codename used will be xenial-pgdg

sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Install binaries for version 10

sudo apt-get install postgresql-10

List bin folder for version 10 is present
 # tree /usr/lib/postgresql/
 /usr/lib/postgresql/
 ├── 10
 │   ├── bin
 └── 9.5
     ├── bin

List Clusters

Note that installed server version 10 runs on consecutive TCP port to allow both servers operations and we don’t want this if firewall rules are in place to allow web servers connect with DB

# pg_lsclusters
 Ver Cluster Port Status Owner    Data directory              Log file
 9.5 main    5432 online postgres /mnt/data/9.5/main/         /var/log/postgresql/postgresql-9.5-main.log
 10  main    5433 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

Remove cluster version 10 created after installation

 sudo pg_dropcluster 10 main --stop 

Stop vCloud Director services and any other application consuming services from this DB server

./cell-management-tool cell -u [username] -s

Capture location of configuration file for v9.5

After installation of v10 binaries is completed a new conf file will be created and default values will be used on file /etc/postgresql/10/main/pg_hba.conf

Default path for configuration file pg_hba.conf is /etc/postgresql/9.5/main/pg_hba.conf

If your server has custom values like data directories; ensure to update your conf file. Find useful commands below for this task

## Show hba file location
sudo -u postgres psql -c "SHOW hba_file;"

## Show config file location
sudo -u postgres psql -c "SHOW config_file;"

## Show configured data directory
sudo -u postgres psql -c "show data_directory;"

## Stop postgresql service
sudo systemctl stop postgresql

## Start postgresql service
sudo systemctl start postgresql

Upgrade cluster version 9.5 to version 10

sudo pg_upgradecluster 9.5 main

Upgrade cluster progress

Cluster upgrade runs in a few minutes and will show information like below

Stopping old cluster…
 Restarting old cluster with restricted connections…
 Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
 Creating new PostgreSQL cluster 10/main …
 /usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5 --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
 The files belonging to this database system will be owned by user "postgres".
 This user must also own the server process.
 The database cluster will be initialized with locale "en_US.UTF-8".
 The default text search configuration will be set to "english".
 Data page checksums are disabled.
 fixing permissions on existing directory /var/lib/postgresql/10/main … ok
 creating subdirectories … ok
 selecting default max_connections … 100
 selecting default shared_buffers … 128MB
 selecting default timezone … Pacific/Auckland
 selecting dynamic shared memory implementation … posix
 creating configuration files … ok
 running bootstrap script … ok
 performing post-bootstrap initialization … ok
 syncing data to disk … ok
 Success. You can now start the database server using:
 pg_ctlcluster 10 main start
 Ver Cluster Port Status Owner    Data directory              Log file
 10  main    5433 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
 Starting new cluster…
 Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
 Roles, databases, schemas, ACLs…
  set_config
 (1 row)
  set_config
 (1 row)
  set_config
 (1 row)
 Fixing hardcoded library paths for stored procedures…
 Upgrading database postgres…
 Analyzing database postgres…
 Fixing hardcoded library paths for stored procedures…
 Upgrading database vcloud…
 Analyzing database vcloud…
 Fixing hardcoded library paths for stored procedures…
 Upgrading database template1…
 Analyzing database template1…
 Copying old configuration files…
 Copying old start.conf…
 Copying old pg_ctl.conf…
 Stopping target cluster…
 Stopping old cluster…
 Disabling automatic startup of old cluster…
 Configuring old cluster to use a different port (5433)…
 Starting target cluster on port 5432…
 Success. Please check that the upgraded cluster works. If it does,
 you can remove the old cluster with
     pg_dropcluster 9.5 main

Start vCloud and check cell log for DB connection

service vmware-vcd start ; tail -f /opt/vmware/vcloud-director/logs/cell.log

Look for these lines to validate DB connection completed and cell started

Successfully connected to database: jdbc:postgresql://10.11.12.13:5432/vcloud?socketTimeout=90
 Cell startup completed in 2m 10s

That is all after confirming your applications connected to your upgraded DB server you can drop the old version 9.5 cluster by running pg_dropcluster 9.5 main

Now that we have a supported DB server vCloud Director can be upgraded to latest versions

That is it for now see you next time!!