upgrading to postgresql 8.3 on gentoo

Thu, Jul 31, 2008 ops

---

When I initially built my primary server, the latest stable postgresql package was 8.0.x. At this time that same tree is still marked as stable. I’m sure that this is largely due to the overall postgresql upgrade philosophy, which involves dumping (backing up) all your dbs and the restoring them after the upgrade. Since that isn’t a seamless process, I’m sure they want an upgrade to be a conscious decision. That being said, the current upgrade path involves switching from the dev-db/postgresql package to the dev-db/postgresql-base dev-db/postgresql-server packages.

Many thanks to @dberkholz for pointing me at the package maintainer’s blog (which doesn’t come up when searching google due to the large number of portage search/info listings). Here’s what I ended up doing:

pg_dumpall > /tmp/please_work.dump
/etc/init.d/postgresql stop && emerge unmerge libpq postgres

echo "dev-db/postgresql-base ~amd64" >> /etc/portage/package.keywords
echo "dev-db/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-server ~amd64" >> /etc/portage/package.keywords
echo "virtual/postgresql-base ~amd64" >> /etc/portage/package.keywords

emerge -uD postgresql-base postgresql-server
emerge --config "=dev-db/postgresql-server-8.3.3"
rc-update add postgresql-8.3 default

Now here’s where you get to rebuild everything that links to libpq and update postgresql.conf as needed. Once that’s done you use pg_restore to reload all your data. I had some grants fail, so I needed to redo them manually. So far so good, the db seems to be a bit faster and less resource hungry. I may post the rrd graphs from cacti later, but there is a noticeable difference.