Skip to content

Postgresql 8.4.3 upgrade troubles

I recently upgraded several of postgres DB server to the latest version 8.4.3.  The process was pretty simple for the 8.3.x clusters.

Make a copy of the postgres configuration files.

cp /var/lib/pgsql/data/*.conf /tmp/.

pg_dump each DB instance in plaintext format:

pg_dump -d dbname -Fp -o ./dbname.dmp.sql

Stop postgres:

pg_ctl stop

Configure the new pgdg yum repository & install updated rpms.  (I used puppet for this – so I’m not going to expand on that step here…)

Initialize the postgres cluster:

service postgres initdb

Make a backup of the new 8.4.3 configuration files.

cp /var/lib/pgsql/data/*.conf /var/lib/pgsql/data/*.843.orig.conf

Restore the 8.3 configuration files:

mv /tmp/*.conf /var/lib/pgsql/data/

Start postgres

pg_ctl start

At this point, I thought I had everything dialed in and I was good to go.  Alas, I was wrong and the server wouldn’t start.  The pgstartup.log had a message complaining about IPv6.  I spent a few minutes on that and discovered that IPv6 was already disabled in the kernel and that this was a red herring.  Nothing in /var/log/messages.  No trail of bread crumbs.  What was I missing?

I diffed the conf files and compared version 8.3.x with 8.4.3 and discovered that pg_hba.conf was the problem.  The first thing I noticed was that the default authentication method used by postgres has been switched from trust to ident.  Trust had always seemed a bit too insecure to me so I suppose this was a good change but I was surprised to see ident.  I haven’t done anything with ident since I had a crash course in sendmail administration back in the 90s.  I had to read the bat book to fix some open relay issues.  I learned more about sendmail and the M4 macro processor then I really ever wanted to know.  But I digress.  Needless to say I didn’t want to use ident for authentication on my postgres server either.  In fact, I use MD5 for authentication. And that is when I realized the source of my problem.

When I upgraded to 8.4.3 I forgot to backup and restore my users and roles.  My 8.3 pg_hba.conf specified MD5 authentication for all users… but I no longer had any users, roles, or MD5 passwords installed on the cluster.  Luckily I had a backup on another host so this was a quick fix.  What I should have done was run this before blowing away the old 8.3 cluster:

pg_dumpall -g

So what should we take away from all this?  Well a couple of things.

  1. Make sure you have backups.  They save you when you do something stupid.
  2. Postgres startup error messages are non-existent for this scenario.  This is a silent fail.  This is too bad because I am probably not the only one to have encountered this problem.  Perhaps we will get a log message or an entry in pg_startup.log for this condition in some future release.

Post a Comment

Your email is never published nor shared. Required fields are marked *

Powered by WP Hashcash