We're back!

After a couple of days of pain toblog is back, and it wasn’t the typo upgrade that caused the problem.

A few months ago gentoo removed a patch to their postgresql build. Unfortunately the removal of this patch caused a problem which meant that pretty much any query on the structure of the database returned:

ERROR: did not find ‘}’ at end of input node

This is not good.

I found someone who suggested some database surgery online but this only partly solved the problem. So, having partially updated typo because the upgrade requires structural queries to work, I finally took the plunge and did what was ultimately necessary.

1. Alert clients to a database / mail outage overnight
2. Stop the database
3. Take a backup of the data directory
4. Downgrade postgresql to the version with the pg-heir patch
5. Startup postgresql and undo the surgery performed above
6. Dump all the databases to file using pg_dump -Fc
7. Stop postgresql
8. Upgrade postgresql
9. Remove the old data directory
10. Reinitialise the postgres database
11. Start postgresql
12. Create the required users
13. Create the required databases
14. Import all the databases from the files dumped above

This took a good couple of hours, and pg_restore does take a lot longer than pg_dump. That said I now have a properly set up postgresql install that I am happy with and understand a lot more so some good has come from this exercise.

Bring on the 8.2.x upgrade!

no comments »

comment We're back!