1. v. The act of writing a weblog or 2. n. Toby’s weblog.

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!

Published on 2007/04/04 at 14:54 by Toby, tags , ,

If you liked this article you can add me to Twitter

comment We're back!

Trackbacks are disabled

Powered by Publify – Thème Frédéric de Villamil | Photo Glenn