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.

Guide to using GPS with Airodump CSV Tools

There is an Android app included that will capture GPS coordinates from your Android device and save them to a file. Airodump CSV Tools will...