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

./delete_old_cluster.sh
./analyze_new_cluster.sh

Need to be run as "su postgres", as if you run them as root, scripts won't work and they will trigger an error.

I followed this to do a postgres 10 to postgres 11 upgrade on CentOS/RHEL8.

If you are running this on EL8 you need to change to: sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
and then run sudo dnf module -y disable postgresql before you install postgresql11-server, otherwise you will get errors about the package being filtered out by modular filtering

To stop postgres 10 service postgresql.service stop

When you do the data migration the command would be: /usr/pgsql-11/bin/pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/11/data/ --old-bindir /usr/bin/ --new-bindir /usr/pgsql-11/bin/

I am getting this error:

connection to database failed: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off