Upgrading Postgres to the latest version on Centos 7 Server

I recently upgraded postgres to the latest version and I think the steps can help someone else too. So here they are. This assumes Centos 7 server installed.

First, take a backup of database

First SSH into the server.

Now connect to postgres:
sudo -i -u postgres

Now do a dump of the entire install:
pg_dumpall > outputfile

I usually use all_db.sql as the filename, but you can use whatever.

Since the file gets dropped into the pgsql folder, good to move it out of there into your home directory, since we are removing old versions of postgres. Use CTRL^d to get out of postgres.

Then do a mv:
sudo mv var/bin/pgsql/all_db.sql all_db.sql

Make sure the file is in your home folder:
ls

Does the file get listed? Good if yes. If not then you didn’t follow the above exactly.

Next, remove postgres

Next remove all installs of postgres:
sudo yum -y remove postgres\*

Now Install PG latest version

Right now postgres 9.6 is the latest version, but in the future you can follow the same steps replacing 96 or 9.6 with 97 or 9.7 etc.

First add the latest version to your rpm for install using yum. Find latest packages here:

https://yum.postgresql.org/repopackages.php

Copy the link for the OS you have.

screen-shot-2016-10-21-at-1-16-22-pm

…in this case I have centos 7. So I copied this link:

https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Now build the rpm command by changing
https://download.postgresql.org/pub/repos/yum

to

http://yum.postgresql.org

… in the next command.

Now run it:
sudo rpm -Uvh http://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Then run the install command:
sudo yum install postgresql96-server postgresql96

Initialize with this:
sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

Start postgres:
sudo systemctl start postgresql-9.6
sudo systemctl enable postgresql-9.6

Change Configuration Files

If this install of postgres needs to be accessible to the outside world, you can open it up. We’re using firewall rules on the server which opens port 5432 only to certain IP addresses. So without that additional step, you would want to be careful with the below.

Find the configuration file.
sudo -i -u postgres
psql
SHOW config_file;

The location of the configuration file is listed now. So open it for editing. (use your location, the below is mine)

sudo vim var/lib/pgsql/9.6/data/postgresql.conf

Scroll to “CONNECTIONS AND AUTHENTICATION” section and find this line:
#listen_addresses = 'localhost' # what IP address(es) to listen on;

Edit this line as follows:
listen_addresses = '*' # what IP address(es) to listen on;

Exit the file saving changes.
ESC
:wq

Next edit the pg_hba.conf file, in the same folder:
sudo vim var/lib/pgsql/9.6/data/pg_hba.conf

Scroll to the bottom of the file and add these lines if they don’t already exist:
#IPv4 remote connections (all users and IP addresses):
host all all 0.0.0.0/0 md5

On the second line beginning with ‘host’ make sure there is no # added. You want it to read as above.

Exit saving changes.

Now restart postgres.
sudo service postgresql-9.6 restart

Add users

If you had different users setup in postgres, add them again.

CREATE USER user_name WITH PASSWORD 'pass_word';

Replace user_name and pass_word with yours.

Make the user a superuser if that’s what you want.

ALTER USER user_name WITH SUPERUSER;

All done!

Mentions: Thanks to http://tecadmin.net/install-postgresql-9-5-on-centos/ for help with the rpm and install steps.

Leave a Reply

Your email address will not be published. Required fields are marked *