How to change PostgreSQL version

From ISPWiki
Jump to: navigation, search

The steps described below should be done unless you know exactly what you are doing and have enough server administration skills.

You should make a backup of all server's databases before you start. We will provide an example how to change PostgreSQL version from 9.2 to 9.3 on CentOS 7, and how to change PostgreSQL from 9.4 to 9.5 on Debian 8.

Centos 7 PostgreSQL 9.3

Connect the repository. You can find a repository with a required version at http://yum.postgresql.org

wget https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-3.noarch.rpm
rpm -ivh ./pgdg-centos93-9.3-3.noarch.rpm

Check what postgresql packages have been installed:

rpm -qa | grep postgresql
postgresql-server-9.2.15-1.el7_2.x86_64
postgresql-libs-9.2.15-1.el7_2.x86_64
postgresql-9.2.15-1.el7_2.x86_64

Install the packages version 9.3 and the contrib package

yum install postgresql93-server.x86_64 postgresql93-libs.x86_64 postgresql93.x86_64 postgresql93-contrib.x86_64

In the /usr/lib/systemd/system/postgresql-9.3.service file specify a port it differs from the default one, for example PGPORT=5432 Check that PGDATA differs from /var/lib/pgsql/data/, for example PGDATA=/var/lib/pgsql/9.3/data/ Initialize 9.3 Postgresql

/usr/pgsql-9.3/bin/postgresql93-setup initdb

Make sure that the /var/lib/pgsql/9.3/data/ directory and its files are created. Suspend the postgresql server

service postgresql stop

Run the pg_upgrade utility. Its options:

-b   binary directory of the old postgresql version
-B   binary directory of the new postgresql version
-d   directory with data of the old postgresql version
-D   directory with data of the new postgresql version

Run pg_upgrade

su postgres
cd /var/lib/pgsql/9.3/
/usr/pgsql-9.3/bin/pg_upgrade -v -b /usr/bin/ -B /usr/pgsql-9.3/bin/ -d /var/lib/pgsql/data/ -D /var/lib/pgsql/9.3/data/

Possible issues:

*failure*
There were problems executing ""/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b  
-c listen_addresses= - c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql/9.3/data'" start >> "pg_upgrade_server.log" 2>&1"
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

Errors in pg_upgrade_server.log

command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data/" -o "-p 50432 -b  -c listen_addresses= -c
unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql/9.3/data'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....FATAL:  unrecognized configuration parameter "unix_socket_directory"
stopped waiting
pg_ctl: could not start server
Examine the log output.

Run the commands as root:

mv /usr/bin/pg_ctl{,-orig} 
echo '#!/bin/bash' > /usr/bin/pg_ctl 
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl 
chmod +x /usr/bin/pg_ctl

connection to database failed: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off

In order to resolve the issue, add the following line into /var/lib/pgsql/data/pg_hba.conf:

local   all         all                               trust

Compare the configuration file of the old server with the new one.

diff /var/lib/pgsql/data/pg_hba.conf ./pg_hba.conf

Copy the pg_hba.conf configuration file, if needed

cp /var/lib/pgsql/data/pg_hba.conf ./pg_hba.conf

Change the port to a default one in /usr/lib/systemd/system/postgresql-9.3.service PGPORT=5432 Run the new server

service postgresql-9.3 start

Run analyze_new_cluster.sh

su postgres 
/var/lib/pgsql/9.3/analyze_new_cluster.sh

Run, delete_old_cluster.sh, in order to delete the directory from the old server. All databases will be deleted

/var/lib/pgsql/9.3/delete_old_cluster.sh

Copy the contents of the new directory to the working directory of the server.

cp -R /var/lib/pgsql/9.3/data /var/lib/pgsql/
chown -R postgres:postgres /var/lib/pgsql/data

in /usr/lib/systemd/system/postgresql-9.3.service change PGDATA to /var/lib/pgsql/data/ Restart

service postgresql-9.3 restart

Delete the old server

yum remove postgresql-server-9.2.15-1.el7_2.x86_64 postgresql-9.2.15-1.el7_2.x86_64 postgresql-libs-9.2.15-1.el7_2.x86_64

Run the script with the following contents:

ls /usr/pgsql-9.3/bin/  |while read line
do 
ln -s /usr/pgsql-9.3/bin/$line /usr/bin/$line
done

In the /var/lib/pgsql/.bash_profile file change the version to 9.3 Once completed, download the existing DB backup copy and make sure that the server is running correctly.

Debian 8 PostgreSQL 9.5

Connect a repository with different postgresql versions In the /etc/apt/sources.list file add the following lines

# PostgreSQL repository
deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

Import the key for the repository

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Renew the packages:

apt-get update -y

Check that PostgreSQL versions are specified

apt-cache search postgresql- | less

Install PostgreSQL 9.5

apt-get install postgresql-9.5 -y

Delete the new version of the cluster:

pg_dropcluster --stop 9.5 main

Use the pg_upgradecluster script to upgrade to the major version; the configuration files and data will be copied to the new cluster.

pg_upgradecluster -v 9.5 9.4 main

Possible issues that may occur during the upgrade:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/PgCommon.pm line 915.
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/PgCommon.pm line 921.
Use of uninitialized value $ctype in scalar chomp at /usr/share/perl5/PgCommon.pm line 924.
Use of uninitialized value $collate in scalar chomp at /usr/share/perl5/PgCommon.pm line 925.
Error: could not get cluster locales

Add the following line to the /etc/postgresql/9.4/main/pg_hba.conf file

local   all         all                               trust

After upgrade delete this line from /etc/postgresql/9.5/main/pg_hba.conf Delete the old server, all databases will be deleted:

pg_dropcluster --stop  9.4 main

Once completed, make sure that the server is working correctly. If the database is missing, download the backup copy.