====== Upgrading PostgreSQL 9.2 to 9.6 ======

This is a tutorial for upgrading PostgreSQL database on CentOS 7 server. Upgrading your database is necessary to have it properly patched, secure and to have all DB features your applications need. Upgrade process in this article installs another PostgreSQL engine alongside the old one and migrates data. Since original data are untouched during the upgrade, this howto is also suitable for production deployments.

In this howto we upgrade from PostgreSQL 9.2 (with systemd unit ''postgresql.service'' and base directory ''/data/pgsql/9.2'') to PostgreSQL 9.6 (with systemd unit ''postgresql-9.6.service'' and base directory ''/data/pgsql/9.6''). However, this tutorial is suitable for migrating between other versions too, assuming you take proper precautions.

<note important>This tutorial is written for CentOS 7 and may need adjustments for other Linux distributions.</note>
==== Risks and considerations ====

When you upgrade the PostgreSQL database where the IdM repository is located, you should take care of a few things.
  * CzechIdM application must be stopped during whole PostgreSQL upgrade.
  * Upgrading the database may potentially damage your data.
    * It is vital to have backups of database before upgrade.
    * It is vital to have a procedure for confirming data was not corrupted during the DB upgrade.
    * We advise to make another backup right after the upgrade, after you verify that data is OK.
==== Preparation and prerequisites====

Before you start the upgrade, check that you have all necessary thing prepared:
  * Check that you have enough space on the disk.
    * Space for backup that is made with ''pg\_dump''. Can be located on any volume.
    * Enough space to hold raw copy of databases (basically at least ''du -sh /data/pgsql/9.2'' space). The free space should be located/available for the ''/data/pgsql/9.6'' directory.
  * Check you have rollback plan for situation when upgrade doesn't go as planned.
  * Create those temporary directories for upgrade (we usually have one mountpoint ''/data'' that is big enough to conatins everything we need or create during the upgrade).<code>
mkdir /data/postgres_upgrade
mkdir /data/postgres_upgrade/backup_postgres92
mkdir /data/postgres_upgrade/backup_postgres96
chown postgres:root /data/postgres_upgrade/backup_postgres92
chown postgres:root /data/postgres_upgrade/backup_postgres96
</code>

  * List all configuration options of old PostgreSQL that you use in ''postgresql.conf'' and ''pg\_hba.conf'' . Save this configs into ''/data/postgres\_upgrade/postgresql.conf.new'' and ''/data/postgres\_upgrade/pg\_hba.conf.new''.

  * If you are using certificate and key - backup them from old postgres.<code>
cp /data/pgsql/9.2/data/server.crt /data/postgres_upgrade/	
cp /data/pgsql/9.2/data/server.key /data/postgres_upgrade/
</code>
===== Upgrade process =====

==== Turn off applications and change listening port of old postgres ====
Check that you have turned off all applications that use postgres(Tomcat - IdM).
During the upgrade, the PostgreSQL needs to be running, but we also have to make sure that no daemon accesses the database. We ensure this by changing the TCP port the database listens on.
In file ''/etc/systemd/system/postgresql.service'' set:
	Environment=PGPORT=6000		

Then restart postgres and check it's listening on port ''6000'':
<code>
systemctl daemon-reload			
systemctl restart postgresql			
systemctl status postgresql			

netstat -nlpt | grep 6000
#expected output: 
#tcp        0      0 127.0.0.1:6000          0.0.0.0:*               LISTEN      1815/postmaster

netstat -nlpt | grep 5432
#expected output: nothing
</code>

==== Backup all databases on old postgres ====

Under user ''postgres'', create dumps of all databases you want to preserve (in this example is database czechidm and postgres).
<code>
cd /data/postgres_upgrade/backup_postgres92	
su - postgres			
pg_dump --compress=9 --create --port=6000 czechidm > czechidm_backup.sql.gz					
pg_dump --compress=9 --create --port=6000 postgres > postgres_backup.sql.gz
</code>

==== Stop old postgres ====

<code>
systemctl stop postgresql
systemctl disable postgresql
</code>

==== Install new postgres ====

Install new postgres(example 9.6) and, create new data directory:
<code>
yum install -y postgresql96-server postgresql96-contrib pgstat2_96 pg_top96 postgresql96-libs	
	
mkdir -p /data/pgsql/9.6/data/	
chown -R postgres:postgres /data/pgsql/9.6/
chmod 700 /data/pgsql/9.6/
cp /usr/lib/systemd/system/postgresql-9.6.service /etc/systemd/system/	
</code>

<note important>
During the installation ''yum'' will complain that it cannot create symlinks to postgres binaries. This is because there are still binaries from the 9.2 version. Save the list of symlinks that could not be created so you can create them manually after uninstalling the 9.2 version.</note>


Set up new data directory for newly installed postgres.

In file ''/etc/systemd/system/postgresql-9.6.service '' set:

	Environment=PGDATA=/data/pgsql/9.6/data/
	Environment=PGPORT=7000
 
In file ''~postgres/.bash_profile'' set:
	
	PGDATA=/data/pgsql/9.6/data/

Reload setting and initialize new database (run all following commands as ''root''):
<code>
systemctl daemon-reload	
/usr/pgsql-9.6/bin/postgresql96-setup initdb	
systemctl start postgresql-9.6.service	
systemctl status postgresql-9.6.service	
</code>

==== Upgrade the database ====
Stop both postgres engines:
<code>
systemctl stop postgresql-9.6.service		
systemctl stop postgresql		
systemctl disable postgresql-9.6.service		
systemctl disable postgresql		
</code>

<note important>
In postgres 9.2 there is bug in ''pg_ctl'' so you need do this workaround:

Backup ''pg_ctl'':

<code>
cp /usr/bin/pg_ctl /data/postgres_upgrade/pg_ctl
</code>

Modify the binary and set correct permitions:
<code>
mv /usr/bin/pg_ctl /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
</code>
</note>

=== Run the upgrade ===
Run ''pg\_upgrade'' binary from the new postgres and add path to both old and new binaries and data directories. You also need to add old and new port number:
<code>
su - postgres		
/usr/pgsql-9.6/bin/pg_upgrade -v --old-datadir="/data/pgsql/9.2/data/" --new-datadir="/data/pgsql/9.6/data/" --old-bindir="/usr/bin/" --new-bindir="/usr/pgsql-9.6/bin/" --old-port=6000 --new-port=7000
</code>	
This command will migrate the database for you and it takes some time to complete. After it finishes, the database has been upgraded and datafiles under new postgres base have been populated.

Do **NOT** delete old data directory yet.

<note important>

Revert the changes you made to ''pg_ctl'' (if you had to do them).

<code>
mv -f /usr/bin/pg_ctl-orig /usr/bin/pg_ctl
</code>

</note>
Backup default configuration of new postgres. Then move prepared configuration options from ''/data/postgres\_upgrade/pg\_hba.conf.new'' and ''/data/postgres\_upgrade/postgresql.conf.new'' to new postgres's configuration with the necessary changes:

<code>
cp -v /data/pgsql/9.6/data/pg_hba.conf{,-orig}
cp -v /data/pgsql/9.6/data/postgresql.conf{,-orig}

cp /data/postgres_upgrade/pg_hba.conf.new /data/pgsql/9.6/data/pg_hba.conf
cp /data/postgres_upgrade/postgresql.conf.new /data/pgsql/9.6/data/postgresql.conf
</code>
Adjust settings for the new version of the database (some settings may be missing now and some may have become obsolete).

If you are using postgres certificate and key. Copy it from old postgres and set correct permissions.
<code>
cp /data/postgres_upgrade/server.crt /data/pgsql/9.6/data/	
cp /data/postgres_upgrade/server.key /data/pgsql/9.6/data/
chown postgres:postgres /data/pgsql/9.6/data/server.crt		
chown postgres:postgres /data/pgsql/9.6/data/server.key
</code>
==== Start and check postgres 9.6 ====

Start ''postgresql-9.6.service'':
<code>
systemctl start postgresql-9.6.service	
systemctl enable postgresql-9.6.service	
systemctl status postgresql-9.6.service	
</code>

Check that you can connect to the database.
<code>
su - postgres	
/usr/pgsql-9.6/bin/psql -p 7000	
#print all databases
\l	
#Go to the czechidm DB
\c czechidm	
#Print all roles and check that you can read from database.
select * from idm_role;
#than exit
\q
# also try connect as app user who will use the database
psql -h localhost -U czechidm
</code>

Set default port of new postgres to 5432 (the port the 9.2 postgres originally ran on)

In file ''/etc/systemd/system/postgresql-9.6.service'' set:	
	 Environment=PGPORT=5432

Restart postgres and check that it's running on port ''5432'':
<code>
systemctl daemon-reload	
systemctl restart postgresql-9.6	
systemctl status postgresql-9.6	

netstat -nlpt | grep 7000
#expected output: nothing

netstat -nlpt | grep 5432	
#expected output: 
#tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1815/postmaster
</code>

Then reboot system and check if postgres and other services started after reboot:
<code>
reboot	
systemctl status postgresql-9.6

# the old postgresql should remain stopped
systemctl status postgresql
</code>

==== Start IdM ====

Start IdM or other application that uses postgres and check if all data are correct.

==== Backup database on postgres 9.6 ====

Create "after-upgrade" backup of migrated databases.
<code>
cd /data/postgres_backup/backup_postgres96
su - postgres
/usr/pgsql-9.6/bin/pg_dump --compress=9 --create --dbname=czechidm > czechidm_backup.sql.gz
/usr/pgsql-9.6/bin/pg_dump --compress=9 --create --dbname=postgres > postgres_backup.sql.gz
</code>

==== Uninstall postgres 9.2 ====

Stop applications using postgres - just to be sure:
<code>
systemctl stop tomcat
systemctl disable tomcat
</code>

Uninstall old postgres and move old data directory.
<code>
mv /data/pgsql/9.2 /data/postgres_upgrade/
yum remove postgresql.x86_64
yum remove postgresql-libs.x86_64
</code>

Check that new postgres starts up correctly after the uninstallation of the old one is complete. Just to be sure.
<code>
systemctl stop postgresql-9.6	
systemctl start postgresql-9.6	
systemctl status postgresql-9.6
</code>

If new postgres started correctly remove the backup of the old data directory.
<code>
rm -rf /data/postgres_upgrade/9.2
</code>

Finally, create symlinks to new postgres binaries (use list of symlink the ''yum'' complained about when you were installing new postgres version):
<code>
ln -s /etc/alternatives/pgsql-psql /usr/bin/psql	
ln -s /etc/alternatives/pgsql-clusterdb /usr/bin/clusterdb	
ln -s /etc/alternatives/pgsql-createdb /usr/bin/createdb	
ln -s /etc/alternatives/pgsql-createlang /usr/bin/createlang	
ln -s /etc/alternatives/pgsql-createuser /usr/bin/createuser	
ln -s /etc/alternatives/pgsql-dropdb /usr/bin/dropdb	
ln -s /etc/alternatives/pgsql-droplang /usr/bin/droplang	
ln -s /etc/alternatives/pgsql-dropuser /usr/bin/dropuser	
ln -s /etc/alternatives/pgsql-pg_basebackup /usr/bin/pg_basebackup	
ln -s /etc/alternatives/pgsql-pg_dump /usr/bin/pg_dump	
ln -s /etc/alternatives/pgsql-pg_dumpall /usr/bin/pg_dumpall	
ln -s /etc/alternatives/pgsql-pg_restore /usr/bin/pg_restore	
ln -s /etc/alternatives/pgsql-reindexdb /usr/bin/reindexdb	
ln -s /etc/alternatives/pgsql-vacuumdb /usr/bin/vacuumdb	
</code>

Start IdM or your application and check if it started correctly.

The upgrade is complete.
==== Post upgrade notes and checks ====

After upgrade, you should check that automatic backup of database are executing properly. If they are using ''pg_dump'' there shouldn't be any issue.

If you monitor that postgres is running implemented with ''systemctl status ...'', note the change of unit name from ''postgresql.service'' to ''postgresql-9.6.service''.
You can either change your monitoring or change the unit name.

===== Recovery guide =====

==== Automatic upgrade fails or new PostgreSQL installation gets broken ====
In case the ''pg\_upgrade'' fails or the upgrade process corrupts data, you can try to import the new database from the backup. PostgreSQL backups made on older versions of RDBMS can be imported to its newer versions. Such recovery is also usable when your new PostreSQL installation (already containing data) gets corrupted. You can then use old or new backup, depending on which you already have at hand and which you trust it contains correct data.

If you do not have such backups and have to return back to old PostgreSQL installation, follow the steps below:

Turn off the application which is using postgres.
Under user postgres, drop all damaged databases on new postgres:
<code>
su - postgres
/usr/pgsql-9.6/bin/psql -p 7000

drop database [DB name]
#when you are done exit with \q
</code>

Then in ''/data/postgres\_upgrade/backup\_postgres92'', decompress all backups you need to restore and restore them to new postgres.
<code>
cd /data/postgres_upgrade/backup_postgres92
gunzip czechidm_backup.sql.gz
/usr/pgsql-9.6/bin/psql -p 7000 -d postgres -f /data/postgres_upgrade/backup_postgres92/czechidm_backup.sql
# ... and similarly for other database dumps ...
</code>

Then refer back to [[priv/postgres_upgrade_tutorial#run_upgrade|]] section of this document and continue as if the ''pg\_upgrade'' command completed successfully.
==== Recovery to old postgres ====
When upgrade fails and you don't have time to try it again use this guide to revert to old postgres.

Turn off the application which using postgres (if it is already running).
Then set default port on postgres. If you postgres is running on that port already - skip this step:

In file ''/etc/systemd/system/postgresql.service'' set:	
	 Environment=PGPORT=5432

Restart postgres and check that it's running on port ''5432'':
<code>
systemctl daemon-reload	
systemctl restart postgresql.service
systemctl status postgresql.service
netstat -nlpt | grep 6000
#expected output: nothing
netstat -nlpt | grep 5432	
#expected output: 
#tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1815/postmaster
</code>

Start your application and check if all data is correct.