Since my previous article Upgrading to PostgreSQL 10 on Centos 7 was so popular, I though I’d do a follow-up for anyone looking to upgrade a very simply configured PostgreSQL 10 server to PostgreSQL 11 on Centos 7.
First, and this goes without saying, backup your server!
Install the repo RPM for PosgresSQL 10
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Install PosgreSQL 11
sudo yum install postgresql11-server
Extensions
If you’re using extensions like pg_crypto, you will also need the postgresql11-contrib package
sudo yum install postgresql11-contrib
Stop Postgresql 10 and Postgresql 11
sudo systemctl stop postgresql-10.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 10.x version to 11.x
/usr/pgsql-11/bin/pg_upgrade --old-datadir /var/lib/pgsql/10/data/ --new-datadir /var/lib/pgsql/11/data/ --old-bindir /usr/pgsql-10/bin/ --new-bindir /usr/pgsql-11/bin/
Edit configuration files
Make any necessary changes to postgresql.conf . I’d recommend making the changes to the new version rather than copying over postgresql.conf from 10.
You can view your 10 configuration with:
nano /var/lib/pgsql/10/data/postgresql.conf
You can make your changes to the 11 configuration with:
nano /var/lib/pgsql/11/data/postgresql.conf
If you need to connect from other servers, make sure to change:
#listen_addresses = 'localhost'
to (apostrophes may not survive copy/paste, may want to hand enter)
listen_addresses = '*'
Now do the same with pg_hba.conf
View the old configuration
nano /var/lib/pgsql/10/data/pg_hba.conf
Edit the new configuration
nano /var/lib/pgsql/11/data/pg_hba.conf
Start the server
systemctl start postgresql-11.service
Analyze and optimize the new cluster
./analyze_new_cluster.sh
Enable the PostgreSQL 11 Service (to start automatically)
systemctl enable postgresql-11
Remove PostgreSQL 10 and its data (if so desired)
./delete_old_cluster.sh
exit
sudo yum remove postgresql10-server
That should do it!
Thank you for the excellent guide!
You’re welcome! So glad it helped!
Very helpful, thank you 👍
This guide has been priceless! Thank you!
Case: upgrading from 9.6.19 to 11.16
after this procedure (no errors):
‘psql –version’ shows psql (PostgreSQL) 11.16
and
‘pg_config –version’ still shows PostgreSQL 9.6.19
Is this possible???
Hi Sam!
I ran a ‘dnf provides pg_config’ and it’s telling me that pg_config is coming from the packages libpq-devel and libpq5-devel .
It’s not a package I use, but I’m guessing you’ve got the libpq-devel or libpq5-devel package from the PostgreSQL 9 install still laying around.
Try running: dnf provides pg_config
Then install the full version name (ie: libpq-devel-13.5-1.el8.x86_64) of the latest package that looks like it will do the trick for you.
Hi Adam,
1) I didn’t get any wiser on performing ‘dnf provides pk_config’
Failed to open: /var/cache/dnf/x86_64/7/x86_64/7/epel/repodata/4c3567d9b1f027445b931b136048f2e11c296b6fd09d8d6350c9051afb505572-updateinfo.xml.bz2.
2) I found pg_config [find / -name pg_config]
/usr/bin/pg_config
/usr/pgsql-9.6/bin/pg_config
/usr/pgsql-11/bin/pg_config
and command [find / -name libpq] gives:
/usr/pgsql-9.6/include/internal/libpq
/usr/pgsql-9.6/include/libpq
/usr/pgsql-9.6/include/server/libpq
3) I ‘m running a CentOs7 so libpq-devel-13.5-1.el8.x86_64 is related to centos8
How can I find the right libpq to download and install for Postgresql11 and centOs7?
Sam,
Sounds like an issue with your dnf cache.
Can you try: sudo dnf clean dbcache
Then: sudo dnf provides pg_config
If that still gives you issues, try: sudo yum provides pg_config
You can remove the PostgreSQL 9 packages if you don’t need them anymore:
sudo yum remove postgresql96-server pgdg-centos96
If all else fails, you can manually symlink the pgsql 11 pg_config to /usr/bin
sudo rm /usr/bin/pg_config
sudo ln -s /usr/pgsql-11/bin/pg_config /usr/bin/pg_config