How to upgrade PostgreSQL9.5 to 12

PostgreSQL9.5 is the end of life now. You can use the steps mentioned in the below video to upgrade PostgreSQL9.5 to 12.

Before upgrade please make sure sufficient disk space is available. Some extensions like. PostGIS will not be upgraded during this process. You may drop the extension from 9.5, installed it on Postgres12, and create the extension. It's recommended to take a backup before doing the up-gradation process on the production environment.

Please find the below list of commands used in the up-gradation process. # Install the repository RPM:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL9.5:

sudo yum install -y postgresql95-server

# Initialize the database and start service:

sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb

# Install PostgreSQL12:

# Initialize the database and start service:

sudo yum install -y postgresql12-server

# PostgeSQL upgrade command syntax

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

# PostgeSQL Cluster compatibity check

pg_upgrade -b oldbindir -B newbindir -d oldconfigdir -D newconfigdir [option...]

/usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-12/bin/ --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/12/data --link --check

# Upgrade Command /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-12/bin/ --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/12/data --link

Install PostgreSQL-12, pgAgent and PostGIS in CentOS 7

PostgreSQL is a free and open-source relational database management system and pgAgnet used for scheduling the job. PostgreSQL is also is known as Postgre. 

Please find the below steps to install PostgreSQL12, PostGIS, and pgAgnet in CentOS 7.

• Update Cent OS 7 and install EPEL repo

yum update

yum install epel-release

• Add the PostgreSQL12 Repository

You may find the repo of PostgreSQL 12 using link https://www.postgresql.org/download/linux/redhat/

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm  

• Install PostgresSQL12, pgAgnet and PostGIS

yum install postgresql12

yum install postgresql12-server

yum install pgagent_12

yum install postgis30_12

• Create a PostgreSQL data directory, set permission and set a password for postgres user

By default, Postgres data directory will be initializes at location /var/lib/pgsql/12/data. In this case, we use custom data directory.

mkdir /data/PG_DATA12
chown -R postgres:postgres /data/PG_DATA12
echo "postgres" | passwd --stdin postgres

• Initialize postgres database in a custom directory "/data/PG_DATA12"

su postgres -c "cd /usr/pgsql-12/bin/;./initdb -D /data/PG_DATA12" 

• Enable the service

systemctl enable postgresql-12 

• Change data directory path in postgresql-12.service

Open file postgresql-12.service which is located at /usr/lib/systemd/system/postgresql-12.service and replace "PGDATA=/var/lib/pgsql/12/data/" with "PGDATA=/data/PG_DATA12/". You may use below command also.

sed -i -e 's#Environment=PGDATA=/var/lib/pgsql/12/data/#Environment=PGDATA=/data/PG_DATA12/#g' /usr/lib/systemd/system/postgresql-12.service 

• Reload systemd 

systemctl daemon-reload

• Start and enable the service

systemctl start postgresql-12

systemctl enable pgagent_12

systemctl start pgagent_12 

• Change listen address in  postgresql.conf

 sed -i -e "s/^#listen_addresses = '127.0.0.1'/listen_addresses = '*' /g" /data/PG_DATA12/postgresql.conf

 sed -i -e "s/^#listen_addresses = 'localhost'/listen_addresses = '*' /g" /data/PG_DATA12/postgresql.conf

• Change authentication method from ident to trust in pg_hba.conf

sed -i -e "s#host    all all 127.0.0.1/32 ident#host    all all 127.0.0.1/32 trust#g" /data/PG_DATA12/pg_hba.conf

• Restart postgresql-12 and pgagent_12 service

    systemctl restart postgresql-12
    systemctl restart pgagent_12

• Create an extension for pgAgnet and PostGIS

psql -h localhost -p 5432 -U postgres -d template1 -c "CREATE EXTENSION postgis;"

psql -h localhost -p 5432 -U postgres -c "CREATE EXTENSION pgagent;"

• Start service

    systemctl start pgagent_12
    systemctl start postgresql-12