About Me

Curriculum Vitae

A brief list of my current skill set

Bloggybits

Pulse Width Modulation and How 1-bit Music Works
Wednesday, 5th December 2012, 23:34

Beep beep multi-channel!

Making PDFs from HTML on your webapp in CentOS
Thursday, 29th November 2012, 14:00

Not as easy as it should be

Some Days I Wish For an Async String.replace
Monday, 19th November 2012, 12:59

MinnaHTML to the rescue!

Object Oriented Programming for Javascript Dummies - Part 2
Tuesday, 6th November 2012, 15:33

OOP it up to the OOPballs

Object Oriented Programming for Javascript Dummies - Part 1
Tuesday, 30th October 2012, 16:01

Not using OOP? Are you mad?

Strange Windows Errors and a Bowser in My Event Log
Wednesday, 24th October 2012, 11:10

It's like my own DailyWTF round-up!

Why Do People Come Here?
Monday, 15th October 2012, 15:47

They come to look at porn!

Idiot thinks Raspberry Pi Unsuitable for Education
Tuesday, 2nd October 2012, 15:24

Dumbest thing I've read since...

Upgrading to PostgreSQL 9.2 on CentOS 5
Tuesday, 25th September 2012, 14:52

It's easy as PI!

Fare Ye Well Work Email You Have Served Me Well
Monday, 17th September 2012, 14:36

Cause of death too much spam

Forest Racer - A HTML5 Game in Under 13K
Tuesday, 11th September 2012, 20:46

Including all assets, but only when zipped

Entering a 13k HTML5 Game Competition
Tuesday, 4th September 2012, 16:31

I'm so tempted to have a go at this

Faster Loops and Faster Iterations in Node.js and V8
Wednesday, 29th August 2012, 13:16

Is Object.keys faster than For...In?

And the Fastworks.js framework is Born!
Wednesday, 22nd August 2012, 16:23

Well I'm excited, even if you aren't

Libxmljs Update on CentOS 3.8 throws an SELinux Wobbley Fit
Monday, 20th August 2012, 15:40

The right way to fix this sort of issue

Projects and Sillyness

MAME Cabinet Diary

How I built my own arcade cabinet

Loading Screen Simulator

I don't miss the ZX Spectrum, I still use it!

The Little Guy Chat Room

It's a Pitfall inspired chat room

GPMad MP3

A fully featured MP3 player what I wrote

GP Space Invaders

My first little emulator

GP32 Development Page

Some info and links about this cute little handheld

Disney Nasties

Uncensored images, you must be 18 to view them

Minna's World

The cuttest fluffy puppy in the world

Diary of a Hamster

Learn about how hamsters think, first hand

Utilities

Time Calculator

A simple little online utility for working out how many hours to bill a client

A Few Links

Upgrading to PostgreSQL 9.2 on CentOS 5
Tuesday, 25th September 2012, 14:52

So you current run an older version of PostgreSQL and want to move to the latest 9.2 in the easiest way possible, with the minimum of downtime? Last week I did just this, after preparing and practising the steps on my test server. So I thought I'd share.

None of this is particularly unique or hard, in fact most of it is based on the documentation for the pgugrade utility on the official website. Some is CentOS specific though, in particular the installation and removal of the new and old versions. So here it is in case someone else finds it useful.

Installing 9.2

The first step requires us to install the correct repository, since we will want automatic updates with yum to work in future, hence using these instead of source.

So let's first get and install the official repository for CentOS 5:

    wget http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-5.noarch.rpm
rpm -i pgdg-centos92-9.2-5.noarch.rpm

Providing no errors occurred, you should be able to see with a "yum search postgresql92" all sorts of new packages you never had before.

Now lets install the new packages. Note that the list you need to install may contain more than the following, you can check with "yum list installed � grep postgresql" to see what you are currently running and adjust the following appropriately:

    yum install postgresql92 postgresql92-contrib postgresql92-libs postgresql92-devel postgresql92-server

So we now have our old and new servers installed, but we need to initialise the database for the new server before we can start it.

    /etc/init.d/postgresql-9.2 initdb

The default directory if you haven't moved it, in which case you are on your own and probably don't need an article like this in the first place, will be under /var/lib/pgsql/ and named after the version. Depending on how old your previous version was, the data for that should still reside in say /var/lib/pgsql/9.1 if like me you are only jumping one version.

However, I think the 8.x series defaulted to /var/lib/pgsql/data so your old version may store its databases in there. You will need to know where that is because you'll want to compare your config files between the versions in the next step.

If you still aren't sure where that is, try "ps auxw � grep postmaster" if the old server is running and you should see after the -D parameter where the data directory is.

Update the Configuration Files

There are usually just two configuration files you are likely to have edited, these are the main one which specifies things like what you log, what ports it listens on, number of connections and memory usage options, and also the all important access config.

Here I am upgrading from 9.1 to 9.2, so I do a quick diff to compare the two and manually make any required changes to the new, so it reflects my desired settings.

    diff /var/lib/pgsql/9.1/data/pg_hba.conf /var/lib/pgsql/9.2/data/pg_hba.conf 
diff /var/lib/pgsql/9.1/data/postgresql.conf /var/lib/pgsql/9.2/data/postgresql.conf

Also, if like me you run a secure connection, you will need to copy across any certificate files with the following:

    cp /var/lib/pgsql/9.1/data/server.* /var/lib/pgsql/9.2/data/

Now we need to copy all of our data from the old server to the new one. This is the first time you will be experiencing downtime, so this bit needs to be done at a quiet time. In theory your old server will become your backup for this process, so if anything goes wrong, you can just stop the new one and start the old one back up.

Let's make sure both are stopped:

    /etc/init.d/postgresql-9.1 stop
/etc/init.d/postgresql-9.2 stop

Now we need to change to a directory that the user we run our server under has full access to. On CentOS 5 this defaults to /home/postgres since it runs under the postgres user. If yours is for some reason different, then you will need to adjust these lines accordingly.

    cd /home/postgres

Here we do the actual upgrade, note that you will have to alter the old-datadir and old-bindir to accurately reflect where your old server data and binaries are installed if they aren't 9.1.

    sudo -u postgres /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/9.1/data --new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/pgsql-9.1/bin --new-bindir=/usr/pgsql-9.2/bin

At this point, a LOT of things will happen, you will see plenty of information on whether it is working and if it isn't, why. Sometimes it will not work because you forgot to install an important dependency with yum earlier on. And there is a point of no return where you have to delete the new data server directory (remember if you do this to save the config files and restore both them and any certificates as before after another initdb).

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Copying user relation files
ok
Setting next OID for new cluster ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok

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

All going well, you can start the new server with the following, and everything should be hunky dory.

    /etc/init.d/postgresql-9.2 start

But we aren't done yet, we should analyse and vacuum the new database as the upgrade suggests, and we do this with the following command:

    sudo -u postgres ./analyze_new_cluster.sh

When this runs you should get output like the following, which will take a while depending on how many databases you have, how big they are, etc:

This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
vacuumdb --all --analyze-only

Generating minimal optimizer statistics (1 target)
--------------------------------------------------
vacuumdb: vacuuming database "mydatabase"
vacuumdb: vacuuming database "horses"
vacuumdb: vacuuming database "superbank"

The server is now available with minimal optimizer statistics.
Query performance will be optimal once this script completes.

Generating medium optimizer statistics (10 targets)
---------------------------------------------------
vacuumdb: vacuuming database "mydatabase"
vacuumdb: vacuuming database "horses"
vacuumdb: vacuuming database "superbank"

Generating default (full) optimizer statistics (100 targets?)
-------------------------------------------------------------
vacuumdb: vacuuming database "mydatabase"
vacuumdb: vacuuming database "horses"
vacuumdb: vacuuming database "superbank"

If you see any errors here, I would certainly panic and get the old server back up as soon as possible.

Finalising the Upgrade

If all went well, the next thing we need to do is ensure the old server doesn't start up after a reboot, and the new one does, so we do the following:

    chkconfig postgresql-9.1 off
chkconfig postgresql-9.2 on

And there we go, everything should be fine now.

Clearing Up

Once you are completely 100% sure that everything is working perfectly and that you do not want the old server at all, you can remove the old data with the following command:

    sudo -u postgres ./delete_old_cluster.sh

Although this just removes a directory you could remove easily yourself, and actually you might want to remove /var/lib/pgsql/9.1 yourself as well, since you are done with this version.

Then for good measure, because we really are done with the server once we have nuked all the data, we can remove the old server components with yum like so:

    yum remove postgresql91 postgresql91-contrib postgresql91-libs postgresql91-devel postgresql91-server

That was easy wasn't it?

Comments

Add Your Own Comment