pg_upgradecluster to move clusters between versions

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 tabledata 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 .sqlscript 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 clustertool 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 sourcecluster. "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 remoteserver, 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.

Terms of Site Index

Inter-Network. The Internet was created at a University to attempt to connect computers between each others from "far away places" (i.e. two buildings in the same University). The main concept was to create a set of protocols to make the systems work. Namely, the SMTP, FTP and TELNET. Now we have many others such as the well known HTTP, which is based on the TELNET concept.