PostgreSQL Upgrade Hack

Here’s a handy tip for you Postgres admins on Windows if you’re getting a “insufficient privileges” error on a minor rev (i.e. 8.3.3 to 8.3.6) upgrade. This tip falls squarely into the hack realm, so use your best judgment and realize trying it could lead to “unexpected consequences”.* I take no responsibility if you hulk-smash your database and loose a few hours of sleep doing restores should you try this.

We were a couple of minor revs behind on our production PostgreSQL database (we were at 8.3.3 - current is 8.3.6), so after consulting with our Enterprise Database Management Team (EDMT FTW!), the other night I decided to upgrade our database.

Now, if you’re in the SQL Server world or SDE world or some combination of the two, database upgrades can cause a great deal of foreboding, drinking, and the dusting off of the resume. And that’s just for the planning stages. Generally not so in the Postgres world, even on Windows. I restored our production databases on my laptop Postgres instance (I keep it at the same version as production), upgraded it in ~3 minutes, and life was grand.

Imagine my chagrin** when, after running through 2 minutes 40 seconds of the upgrade on the production server, Postgres gave me an insufficient permissions error and rolled back the changes. Which it did, and the database was up and running again in a jiff, Bob’s your uncle. But at 8.3.3. And I was using an administrator account, so I had permissions to tap dance on that server’s face.

After some Googling, this appears to be a common problem when upgrading Postgres on Windows. It seems to be related to eating and recreating the Postgres service, and also seems to have something to do with the account that service runs under, and is otherwise generally mysterious. The recommended solution (for minor revs) seems to be to uninstall Postgres, leaving your files in place, and then reinstall the new version using the same folder locations, and Postgres will fire right up as the new version with your data intact. Yes, Postgres is that cool.

But that sounded like a lot of work. Technically the server would never really be down for any of it - during upgrades I point our web services at my laptop and nobody notices anything (again, Postgres = awesome). Database upgrades are not something that one should miss the season finale of Burn Notice for, and it was already 9:45PM.

Eventually I ran across this tip in my searches, and it sounded like a winner. Since it’s the service part that’s failing, and you really don’t need a new Postgres service if you already have one, why not just hack it out of the MSI?

This immediately appealed to my inner hacker, and if worst came to worst, I’d be back to doing it the long recommended way anyway. I grabbed Orca, opened up the MSI, and found the ServicesInstall section (not InstallServices as the tip I found indicated), and blasted it. Hit the upgrade.bat again - viola! 3 minutes later, the service cranked itself back up, and select version() returns 8.3.6.

Again, this solution falls squarely in the “hack” realm, and I’m sure there’s a Postgres developer out there that can list a dozen reasons why doing this will send you to straight to whatever version of Dante’s Easy-Bake you subscribe to, so if you’re uncomfortable with that or you’re not confident in your ability to get yourself out of a jam, please don’t go near it. But if you run into an “insufficient priviliges” error during a minor rev upgrade in Postgres on Windows, this little hack may save you some headaches.

*Read: a headcrab might suck out your brains.

**That was actually my second chagrin. My first chagrin was logging on to a production database server and having a popup message from our IT division saying something to the effect of “Mecklenburg County IST has installed new crud on your server. Failure to reboot the machine may cause software to stop responding”. Nice.