Postgres Upgrade to 8.4 from 8.x
Subscribe

From OpenNMS

Jump to: navigation, search

Contents

Assumption:

Original rpms used to install the current version you are running, just in case you need to rollback and reinstall.

Website for Postgresql yum repo: http://yum.pgsqlrpms.org/reporpms/repoview/pgdg-centos.html


Stop opennms

sudo /etc/init.d/opennms stop

Change directory to /opt/opennms

cd /opt/opennms

Backup all etc files to be safe

sudo tar czvpf etc.`date +%F`.tar.gz etc

Backup all rrd files to be safe

sudo tar czvpf rrd.`date +%F`.tar.gz share/rrd/

Clean up events

Clean up events in opennms.events so nothing older than 90days is kept. I choose 90 days you can choose to or not to do this. At worst make sure vacuum is run otherwise you will have a lot of just in your DB that likely isn't needed. 

psql -U opennms opennms
DELETE FROM events WHERE eventtime < NOW() - INTERVAL '90 days';

Note: this will also delete all outages older than 90 days and will effect your non-persisted SLA reports. Try adjusting the contributed "maint_events.sh" script to suit your purposes.

Download and install the Postgresql Repos

sudo rpm -ivh http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm

Exclude Postgresql from using the CentOS base/updates since they are quite a bit behind

Edit /etc/yum.repos.d/CentOS-Base.repo 

[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-5
exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-5
exclude=postgresql*

Backup postgres config files

mkdir /var/tmp/pgbak
cp /var/lib/pgsql/data/*.conf /var/tmp/pgbak
pg_dumpall -h localhost -p 5432 -U postgres --globals-only > /var/tmp/pgbak/globals_`date +%F`.sql

Backup the whole postgres database

sudo -u postgres /usr/bin/pg_dumpall > /var/tmp/pgbak/psql.`date +%F`.sql

Remove iplike from the so you can reinstall after the database reinstall

sudo yum remove iplike

Stop postgres

sudo /etc/init.d/postgresql stop

Backup the raw postgres database file just in case something goes wrong with the sql backup.

sudo mv /var/lib/pgsql/data /var/lib/pgsql/dataold

Note: should probably do a vacuum full and reindexing while you have the database stopped and before doing the copy since a lot of diskspace will be reclaimed. Additionally, should make sure that all table spaces are backed up since postgresql 8 allows table spaces to be created on other mounted filesystems (backing up /var/lib/pgsql/data might not do what you intended) The safest backup is the pg_dumpall backup.

Determine what PSQL packages need to be updated

sudo yum list installed | grep postgres

Use rpm to remove PSQL packages and not ONMS

sudo rpm -e --nodeps <postgresql packages from above>

Use yum to install PSQL packages for 8.4

sudo yum install postgresql postgresql-server postgresql-libs

Create the postgres data structure

sudo /etc/init.d/postgresql initdb

Verify postgres is still set to start on reboots, likely it is not.

sudo /sbin/chkconfig --list postgresql

Make postgres start when system starts

sudo /sbin/chkconfig postgresql on

Start postgres

sudo /etc/init.d/postgresql start

Check the differences between 8.x and 8.4 postgres configs.

sudo sdiff /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/dataold/pg_hba.conf

Note: pg_hba.conf doesn't change much. there are likely on additional comments so you can manually make the change or copy over the backup of pg_hba.conf to /var/lib/pgsql/data/pg_hba.conf. There are likely to be major differences in /var/lib/pgsql/dataold/postgresql.conf between 8.x and 8.4. These are best done by hand. If you didn't change postgresql.conf like recommend on the ONMS page, then no need to worry.

Load your postgres global

psql -U postgres -d postgres -f /var/tmp/pgbak/globals_`date +%F`.sql

Restart postgres to take your changes

sudo /etc/init.d/postgresql restart

Create a new opennms DB

createdb -U postgres -E UNICODE opennms

Install iplike

install iplike for postgres 8.4 (http://opennms.org/~jeffg/iplike-pgsql84-rpms/) until prod is ready. wget http://opennms.org/~jeffg/iplike-pgsql84-rpms/iplike-1.0.9-1.i386.rpm when signed you can use this or the next one sudo yum localinstall iplike-1.0.9-1.i386.rpm sudo yum install iplike for now use rpm 

sudo rpm -ivh iplike-1.0.9-1.i386.rpm

Reload the database dump you did earlier

psql -U postgres -f /var/tmp/pgbak/psql.`date +%F`.sql template1

Update opennms packages

sudo yum update opennms

Update the existing ONMS database with the new schema

/opt/opennms/bin/install -dis

Copy over your changes

Look for rpmsave files throughout $OPENMS_HOME and make sure you changes make it over to the newer files.

Start ONMS and enjoy

sudo /etc/init.d/opennms
Retrieved from "http://www.opennms.org/wiki/Postgres_Upgrade_to_8.4_from_8.x"