Upgrading postgres to latest version

Recently, I worked on upgrading postgres database server from 9.6 to 12. I faced lot of challenges while performing this upgrade, especially storage running out of space, encoding issues, initialisation user was not matching and best practices.

I spent a lot of time overcoming the challenges and make the upgrade process success. I thought, I will share my experience, so others can save sometime in troubleshooting and make upgrade process much smoother.

I used ubuntu as base OS, already installed postgres9.6 .

Before installing postgres, please make sure OS locale is set to UTF-8. If not, please run the following command to set the locale.

sudo sed -i -e 's/# en_US.UTF-8 UTF-8/en_US.UTF-8 UTF-8/' /etc/locale.gen
sudo dpkg-reconfigure --frontend=noninteractive locales
sudo update-locale LANG=en_US.UTF-8

Setting locale will ensure all databases created in postgres uses UTF-8 encoding , Collate and CType. By default, postgres uses SQL ASCII encoding. UTF-8 encoding supports better localisation and supports international languages.

To install postgres12, please run the following command .

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
sudo apt-get update
sudo apt-get -y install postgresql-12

You can also follow the below link to perform the installation

Linux downloads (Ubuntu)

After installing postgres latest version, first thing we need to do is initialising the database server.

Create a directory where you prefer to store postgres server data. You can set directory path to environment variable PGDATA and export. PGDATA directory permission should be set 700 and user should be changed to postgres (that’s required for initialisation)

export PGDATA=/pgdata12
sudo mkdir $PGDATA
sudo chmod 700 $PGDATA
sudo chown -R postgres $PGDATA

(Note you will see the following error if above permissions are not set –

initdb: error: could not change permissions of directory “/pgdata12”: Operation not permitted)

Before proceeding with upgrade, please make sure you take backup of postgres9.6 server directory. To take the backup, stop the postgres server and just copy the postgres server directory (eg. /pgdata96 is postgres9.6 server directory)

sudo su — postgres -c "/usr/lib/postgresql/9.6/bin/pg_ctl -D /pgdata96 -w stop"
mkdir -p /pgdata96_backup
sudo cp -R /pgdata96 /pgdata96_backup

(Note — Please ensure enough storage space is available eg. 3times of size of /pgdata96 + 20GB storage)

We need to use same user credentials to initialise postgres12 (that we used while initialising postgres9.6). It’s always good practice to set initialising username and (healthy) password. By default, postgres just uses postgres username for initialisations.

If we used default postgres username for postgres9.6, don’t worry, we can still update the default username to username (of our choice) and with healthy password.

To update the postgres9.6 initialisation username/password, please follow the instructions below.

· Start the postgres server (if not already started)

sudo su — postgres -c "/usr/lib/postgresql/9.6/bin/pg_ctl -D /pgdata96 -w start"

· Bash into the postgres client

psql postgres

· Run the following query to check if initialisation user is postgres.

SELECT * FROM pg_roles where oid=10;

· Run the following command to alter the default postgres username to new username (eg. pgadm01)

ALTER USER postgres RENAME TO pgadm01;

(Note — Initialization username of postgres 9.6 and 12 should always match, otherwise, upgrade will not progress and throw error)

· To set the password SkgDqKJW3tyLBB22 to the username pgadm01, run the following query.

Alter user pgadm01 with password 'SkgDqKJW3tyLBB22';

(Note — Always choose healthy password with at least 16 characters, upper and lower case and numbers. I don’t prefer special characters, since I need to escape characters while using through shell, and may need to do some workarounds to support password with special characters in backend code)

If you initialised your postgres9.6 database with default or some other encoding , no worries at all. We can still go ahead and update to UTF-8 encoding .

update pg_database set encoding=6, datcollate='en_US.UTF-8',datctype='en_US.UTF-8';

(Note — While upgrading postgres to latest version, encoding should always match, if not, upgrade will not progress and throw error)

· Run \q to exit the psql client.

Now, it’s time for postgres12 initialisation

export POSTGRES_USER=pgadm01export POSTGRES_PASSWORD=SkgDqKJW3tyLBB22
sudo su - postgres -c "/usr/lib/postgresql/12/bin/initdb -D /pgdata12 -U $POSTGRES_USER --pwfile=<(echo '$POSTGRES_PASSWORD')"

(Note — Username and password should match the postgres9.6 initialisation username and password)

You can also follow the link below to understand more details on initialisation.

initdb

We are good to perform the postgres upgrade from 9.6 to 12.

There are 2 ways to perform postgres upgrade

· pg_upgrade (the most recommended way since postgres itself takes care of necessary things while upgrading)

· pg_dumpall (With this approach, we just take dump of database and use psql to restore)

We will use pg_upgrade to upgrade the database server.

su - postgres -c "/usr/lib/postgresql/12/bin/pg_upgrade -d /pgdata96 -D /pgdata12 -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/12/bin -U pgadm01"

Sample output of pg_upgrade

If you are facing any error while upgrade, please navigate to /var/lib/postgresql directory. You will find few log files which will have detailed information on error.

Here are some of the common issues that you will face

· Source and destination Initialisation user not matching

· Source and destination encoding not matching

Next, as recommended in screenshot, please run analyze_new_cluster.sh to perform vacuuming and analysing of database in postgres 12. Todo so, follow the commands

cd /var/lib/postgresql
su - postgres -c "/usr/lib/postgresql/12/bin/pg_ctl -D /pgdata12 -w start"
./ analyze_new_cluster.sh
su - postgres -c "/usr/lib/postgresql/12/bin/pg_ctl -D /pgdata12 -w stop"

To delete the old cluster, simply delete /pgdata96 folder

You can also follow the link below to understand more on pg_upgrade

pg_upgrade

Throughout the article, I didn’t explain more on each pg command. But, I have shared the links if you need to understand each command well. The intention is how easily you can upgrade your postgres database server from any source version to latest version. I welcome critiques to improve this document and more than happy to answer your queries and some help as well.