Nov 11 2016

PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE

Category: AdministrationFatih Acar @ 11:03

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

Postgres_logo

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

13,037 total views, 4 views today

Tags: PostgreSQL, PostgreSQL Administration

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.