I installed Back Track recently, which is based on Ubuntu 10.04 (Lucid Lynx). As such, it’s not got the most up to date software, even with it operating on it’s own repos. So, when I migrated a site over that was used to PostgreSQL 9.1, to the new server running PostgreSQL 8.4, the database wouldn’t play nice. Some of the queries would work, but when it came to performing CONCAT() and similar operations, the server croaked.
After looking high and low on Google for how to solve this, I decided to just see what my options were on my system. This is where I discovered pg_upgradecluster. From what it seems like it’s the older version of pg_upgrade, but for the life of me I can’t figure out why Postgre would make this more difficult in pg_upgrade. Anyways…
The first thing you should (and I highly suggest) do is stop any instances of both the old and new PgSQL server. For me it was simply running these:
service postgresql-8.4 stop
service postgresql stop
Easy enough. Then I had to figure out where my 8.4 data was stored. Typically (as was the case here), it was stored in /var/lib/postgresql/8.4/main. It’s important to make note of the last two parts of that directory structure, too. You’ll see why in a bit.
To keep things organized and nice, I decided to use the same structure for 9.1. What I ran was this:
pg_upgradecluster 8.4 main /var/lib/postgresql/9.1
This took me about 3 minutes on a small-scale database (49 MB according to du -h .). What happens is pg_upgradecluster will migrate all of the data from /var/lib/postgresql// (in this case version = 8.4 and cluster = main) to the new data directory, keeping the same cluster (main). It will also migrate over the configuration files and any user accounts in the PgSQL system.
After that, just start up the PostgreSQL server instance and you should be running on 9.1 now.