Upgrading PostgreSQL service from 9.x or 10.x to 11 version on CentOS 7

upgrade-postgresql-service-centos-7.md

This gist was created based on the following guide: upgrading to postgresql 11 on centos 7

Create a backup:

sudo -Hiu postgres pg_dumpall > mybackup.sql

Install PostgreSQL 11:

# Update your system
sudo yum update
# Install the repository RPM:
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install packages
sudo yum install postgresql11-server postgresql11-contrib

Use rpm -qa | grep postgresql to check which versions of PostgreSQL are installed:

postgresql96-contrib-9.6.17-1PGDG.rhel7.x86_64
postgresql11-server-11.7-1PGDG.rhel7.x86_64
postgresql96-libs-9.6.17-1PGDG.rhel7.x86_64
postgresql96-server-9.6.17-1PGDG.rhel7.x86_64
postgresql11-libs-11.7-1PGDG.rhel7.x86_64
postgresql11-11.7-1PGDG.rhel7.x86_64
postgresql11-contrib-11.7-1PGDG.rhel7.x86_64
postgresql96-9.6.17-1PGDG.rhel7.x86_64

In my case, I will be performing the upgrade from 9.6 to 11 version, but the guide can be also used to upgrade others PostgreSQL 9.x or 10.x versions. In order to provide the upgrade for others PostgreSQL version, just replace the 9.6 and 96 snippets in the next commands with actual values.

Stop PostgreSQL 9.6 and PostgreSQL 11:

sudo systemctl stop postgresql-9.6.service && sudo systemctl stop postgresql-11.service

Initialize the PostgreSQL 11 database:

sudo su postgres
cd ~/
/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data/

Migrate your database from the 9.6 version to 11:

/usr/pgsql-11/bin/pg_upgrade --old-datadir /var/lib/pgsql/9.6/data/ --new-datadir /var/lib/pgsql/11/data/ --old-bindir /usr/pgsql-9.6/bin/ --new-bindir /usr/pgsql-11/bin/

Edit configuration files:

Please refer to the article mentioned in the top of our gist, and navigate to the Edit configuration files section.

Start the PostgreSQL 11 service:

systemctl start postgresql-11.service

Analyze and optimize the new cluster:

./analyze_new_cluster.sh

Enable the PostgreSQL 11 Service (to start automatically on server startup, reboot):

systemctl enable postgresql-11

Remove old PostgreSQL service and its data (if so desired)

./delete_old_cluster.sh
exit 
sudo yum remove postgresql96-server