5.4. Upgrading Remote Databases from PG 9.2 to 9.5

Stop the service running on the machine:

Enable the required repository to receive the PostgreSQL 9.5 package:

Enable either the Red Hat Virtualization Manager repository:

# subscription-manager repos --enable=rhel-7-server-rhv-4.2-manager-rpms

or the SCL repository:

# subscription-manager repos --enable rhel-server-rhscl-7-rpms

Install the PostgreSQL 9.5 packages:

# yum install rh-postgresql95 rh-postgresql95-postgresql-contrib

Stop and disable the PostgreSQL 9.2 service:

# systemctl stop postgresql
# systemctl disable postgresql

Upgrade the PostgreSQL 9.2 database to PostgreSQL 9.5:

# scl enable rh-postgresql95 -- postgresql-setup upgrade

Start and enable the rh-postgresql95-postgresql.service and check that it is running:

# systemctl start rh-postgresql95-postgresql.service
# systemctl enable rh-postgresql95-postgresql.service
# systemctl status rh-postgresql95-postgresql.service

Ensure that you see an output similar to the following:

rh-postgresql95-postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/rh-postgresql95-postgresql.service;
enabled; vendor preset: disabled)
   Active: active (running) since Mon 2018-05-07 08:48:27 CEST; 1h 59min ago

Log in to the database and enable the uuid-ossp extension:

# su - postgres -c "scl enable rh-postgresql95 -- psql -d database-name"

Execute the following SQL commands:

# database-name=# DROP FUNCTION IF EXISTS uuid_generate_v1();
# database-name=# CREATE EXTENSION "uuid-ossp";

Copy the pg_hba.conf client configuration file from the 9.2 environment to your 9.5 environment:

# cp -p /var/lib/pgsql/data/pg_hba.conf  /var/opt/rh/rh-postgresql95/lib/pgsql/data/pg_hba.conf

Update the following parameters in the postgresql.conf file:

# vi /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf

listen_addresses='*'
autovacuum_vacuum_scale_factor='0.01'
autovacuum_analyze_scale_factor='0.075'
autovacuum_max_workers='6'
maintenance_work_mem='65536'
max_connections='150'
work_mem = '8192'

Restart the PostgreSQL 9.5 service to apply the configuration changes:

# systemctl restart rh-postgresql95-postgresql.service

The remote databases have been upgraded.