Thursday, October 16, 2014

Upgrading from PostgreSQL 8.x to 9.x

I was upgrading a server with Postgres v8.4 to v9.3 and ran into an issue when I tried to import the data that I had dumped before the upgrade.  After doing a little digging, I found a way to update it without dumping the whole database and reloading it.

Here are my full instructions for doing so on RHEL 6.5.  Follow them at your own risk.

*See the Installation Guide to install PostgreSQL 9.3

yum install postgresql93 postgresql93-devel postgresql93-contrib

vi /etc/init.d/postgresql

Note the following variables (examples given)
PGVERSION=8.4.20
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/data
PGLOG=/var/lib/pgsql/pgstartup.log

vi /etc/init.d/postgresql-9.3
Change PGPORT=5432 to 5433

Note the following variables (examples given)
PGVERSION=9.3.5
PGENGINE=/usr/pgsql-9.3/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/9.3/data
PGLOG=/var/lib/pgsql/9.3/pgstartup.log

Run initdb
service postgresql-9.0 initdb

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

You should see the following:

#Upgrade Complete
#----------------
#Optimizer statistics are not transferred by pg_upgrade so,
#once you start the new server, consider running:
#    analyze_new_cluster.sh
#
#Running this script will delete the old cluster's data files:
#    delete_old_cluster.sh
#

[root pgsql]# su postgres
bash-4.1$ /var/lib/pgsql/analyze_new_cluster.sh
bash-4.1$ /var/lib/pgsql/delete_old_cluster.sh

Remove the old version of PostgreSQL
yum remove postgresql

This will also remove postgresql-contrib and postgresql-server

Set up your path
[root ~]# cd /usr/bin
[root bin]# ln -s /usr/pgsql-9.3/bin/psql

Optionally
export PATH=$PATH:/usr/pgsql-9.3/bin

vi /etc/init.d/postgresql-9.3
Change PGPORT=5432

 
service postgresql-9.3 start

psql --username=postgres

su postgres
psql
\list

You should now see a list of databases on your server.

No comments: