pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.
Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)
pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g. by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.
pg_upgrade supports upgrades from 8.4.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.
Source : postgresql.org
Steps Of Upgrade
- Install PostgreSQL new release packages
- Create new directory for 9.6
- Initialize 9.6 version in 9.6 directory
- Configure pg_hba.conf and postgresql.conf regarding to changed parameters of 9.5 version
- Stop 9.5 PostgreSQL service
- Start pg_upgrade
- Change PostgreSQL service name and start PostgreSQL 9.6
System Information
OS : Centos 7 x64
PG : 9.5
DIR : /pgdata/9.5/data/
SERVICE : postgresql.service
Install PostgreSQL new release packages
[root@testdb /tmp]# yum install postgresql96-libs.x86_64 postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-contrib.x86_64 postgresql96-devel.x86_64 postgresql96-python.x86_64 postgresql96-plpython.x86_64
Create new directory for 9.6
[root@testdb ~]# mkdir -p /pgdata/9.6/data
[root@testdb ~]# chown postgres.postgres -R /pgdata/
Initialize 9.6 version in 9.6 directory
Change PGDATA parameter as /pgdata/9.6/data/
[root@testdb ~]# vi /usr/lib/systemd/system/postgresql-9.6.service
Initialize PostgreSQL
[root@testdb ~]# cd /usr/pgsql-9.6/bin/
[root@testdb bin]# ./postgresql96-setup initdb
Configure pg_hba.conf and postgresql.conf regarding to changed parameters of 9.5 version
If you changed or added new parameter in pg_hba.conf and postgresql.conf file, you have to move these changed parameters to 9.6 (/pgdata/9.6/data/)
Stop 9.5 PostgreSQL service
[root@testdb ~]# systemctl stop postgresql.service
Start pg_upgrade
[root@testdb ~]# su – postgres
[postgres@testdb ~]# cd /tmp/
[postgres@testdb tmp]# /usr/pgsql-9.6/bin/pg_upgrade -b /usr/pgsql-9.5/bin/ -B /usr/pgsql-9.6/bin/ -d /pgdata/9.5/data/ -D /pgdata/9.6/data/ -v-b old bin dir
-B new bin dir
-d old data dir
-D new data dir
Change PostgreSQL service name and start PostgreSQL 9.6
You do not have to change service name. But if you use specific name, you can change it.
[root@testdb ~]# cd /usr/lib/systemd/system/
[root@testdb ~]# mv postgresql.service postgresql.service.old
[root@testdb ~]# cp postgresql-9.6.service postgresql.service
[root@testdb ~]# systemctl daemon-reload
[root@testdb ~]# systemctl start postgresql.service