Tag Archives: postgresql 10

Upgrading to PostgreSQL 10 on Centos 7

(An updated version of this post for upgrading to PostgreSQL 11 is available here)

Here’s a quick rundown on upgrading a very simply configured PostgreSQL 9.x server to PostgreSQL 10 running on Centos 7.

First, and this goes without saying, backup your server!

In these examples, I’m using upgrading from PostgreSQL 9.5. If you’re upgrading from a different version, just replace 9.5 and 95 wherever you see it with your appropriate version number.

Install the repo RPM for PosgresSQL 10

sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm

Install PosgreSQL 10

sudo yum install postgresql10-server

Stop Postgresql 9.5 and Postgresql 10

sudo systemctl stop postgresql-9.5.service && sudo systemctl stop postgresql-10.service

Initialize the PostgreSQL 10 database

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

Migrate your database from the 9.x version to 10

/usr/pgsql-10/bin/pg_upgrade --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/10/data/ --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-10/bin/

Make any necessary changes to postgresql.conf . I’d recommend making the changes to the new version rather than copying over postgresql.conf from 9.5, since there are a bunch of new options in the PostreSQL 10 version of the file.

You can view your 9.5 configuration with:

nano /var/lib/pgsql/9.5/data/postgresql.conf

You can make your changes to the 10 configuration with:

nano /var/lib/pgsql/10/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 = '*'

(or whatever is appropriate for you)

Now do the same with pg_hba.conf

View the old configuration

nano /var/lib/pgsql/9.5/data/pg_hba.conf

Edit the new configuration

nano /var/lib/pgsql/10/data/pg_hba.conf

Start the server

systemctl start postgresql-10.service

Analyze and optimize the new cluster

./analyze_new_cluster.sh

If everything is working, set the PostgreSQL 10 service to start automatically

systemctl enable postgresql-10

If you wish to remove PostgreSQL 9.x and its data

./delete_old_cluster.sh
exit
sudo yum remove postgresql95-server
sudo yum remove pgdg-centos95

That should do it!