Until now, I only had to upgrade my PostgreSQL databases between computers so I would pg_dump + pg_restore the databases.

However, PostgreSQL offers a tool named pg_upgradecluster that does that work for you, plus it will copy all the different things that pg_dump doesn't always catch such as functions, existing users, etc.

This sounds all good...

So I ran it against our cluster of 298 databases and got a little surprise: it takes about 10 minutes to run on one database, whatever its size. Yes! 10 minutes. Our big database has over 1 million rows, the smallest have a few hundred. Impressive that either one would take about the same amount of time to duplicate (probably because the table data was not dumped + restored as such.)

Yet, 10 minutes per database when you have 298 is LONG: 49 hours1. It actually took 3 days because the hard drive is checked by tripwire once a day and I started websites that were ready as they became available instead of waiting for everything to be done.

Next time, I'll use pg_dump + pg_restore. I'll have to handle functions and users by hand, but that's no biggie. The users I can create them at the start once the destination cluster exists, and the functions, I can have a small .sql script that is applied on each newly created database. That way, I could turn off a website, do the pg_dump/pg_restore really quick, then restart the website. That would take no more than 5 minutes per website instead of 2 to 3 days...

Well... that's how it goes. We lost about 2 days worth of traffic.

For those interested, the upgrade cluster tool usage is explained in a manual page (man pg_upgradecluster).

pg_upgradecluster -v 8.4 8.3 main

The -v option is used to specify the destination cluster. By default it is the newest available or the one on port 5432. The other version is the source cluster. "main" is the name of the cluster. Unless you created a cluster with a different name, that's the name you need to use in your upgrade.

• 1. To Be Noted: the two clusters where both on the same hard drive making it very slow just and only for that reason. Since it is a remote server, I couldn't hear whether it was seeking a lot, but it is very likely. Having separate hard drives and a lot of RAM would most certainly make things go faster.