OK, so I'm relatively new to linux. Some months ago, I managed to set up a website using Amazon's build of CentOS, PHP and Postgres 9.1. I'd done an initdb on /pgdata which was mounted on an Amazon volume I'd created for that purpose. The site's been humming ever since, and I felt like this wasn't so hard after all! :)

But right now, my site is down. Here's what happened...

Yesterday I logged in to begin some website changes. First thing I saw is that there were some updates available (naturally), so I ran "yum update". It did a bunch of things and reported success. Cool!

All was going well on my website changes until I executed a change to a Postgres function. I got an error:

ERROR: could not load library "/usr/lib64/pgsql/plpgsql.so": "/usr/lib64/pgsql/plpgsql.so": undefined symbol: SPI_plan_get_cached_plan

(I'm using plpgsql in my functions)

Google led me to various places which seemed to say I just needed to restart Postgresql. I tried that various ways. Postgres would stop, but not start again.

At some point along the way, I got another (bit more helpful) error message:

"The data directory was initialized by PostgreSQL version 9.1, which is not compatible with this version 9.2.3."

What the? Had my yum update installed a new, incompatible version of Postgres? Is this how we roll in the Linux world? Major upgrades without warnings about what that entails or asking my permission? Foowee, I'll be more careful next time. Incidentally, what should I have done if I had my time again?

Anyway, that led me to these links, and of course the postgres docs. I'm stuck now on running pg_upgrade. Here's my command so far:

pg_upgrade -d /pgdata_temp -D /pgdata -b /usr/lib64/pgsql/postgresql-9.1/bin/ -B /??/ -v

How do I find out where the new Postgres bin directory is (for the /B argument)? It doesn't seem to be anywhere. And yet, when I ran

su - postgres

initdb -D /pgdata -E UTF8

cat /pgdata/PG_VERSION

...earlier, I got "9.2".

Any help would be very much appreciated. Maybe I just roll back to 9.1? Is that my best strategy? Then how do I avoid upgrades when I run yum update next time? Or else, I'm happy to upgrade, but how do I do that and upgrade my existing database to 9.2.3?


EDIT: I decided my next best option was to abandon 9.1 and my database (I had a copy of the data in CSV format), and rebuild a 9.2 database from scratch with the scripts I kept in the initial 9.1 build.

sudo su -
cd /pgdata
rm -fr * .* (delete everything in /pgdata from 9.1 database - I kept a backup first)
su - postgres
initdb -D /pgdata -E UTF

Edit postgresql.conf and pg_hba.conf and start the server.

I still have no idea

  • where yum put the 9.2 version binaries - and yet the 9.1 binary directory still exists
  • how to get service postgresql upgrade working

But after days of Googling and server downtime, I don't care anymore.

  • check the /usr directory. Additionaly, you can use update-alternatives command toswitch betwen versions.
    – kofemann
    Mar 8, 2013 at 19:33
  • @tigran, thanks, I don't see anything 9.2ish in /usr, nor in /usr/lib64. Also did various searches, eg find / -name '*postgres*' 2>/dev/null. All I can find is the /usr/lib64/pgsql/postgresql-9.1/bin/ directory. How does Amazon install 9.2 and have NO directory for the binaries? Where are they? Is there any documentation for their update? I've already read all of this: forums.aws.amazon.com/thread.jspa?messageID=389634
    – poshest
    Mar 10, 2013 at 19:49
  • I wrote about this a while ago (dba.stackexchange.com/q/28245/7788); I'm horrified that they haven't fixed this unacceptable packaging despite - I thought - assuring me at the AWS Re:Invent conference in late 2012 that they had. Mar 11, 2013 at 11:29
  • @CraigRinger: Yes, I saw your excellent post there after I created this one - great work! Wish I'd seen it sooner, but I just hadn't included "Amazon" in my Googling before Daniel's steer.
    – poshest
    Mar 11, 2013 at 12:37

1 Answer 1


The unintended upgrade is Amazon's packaging fault. The other Linux distributions normally don't break PostgreSQL when upgrading.

To get out of this mess, see this entry in their FAQ:

What steps do I take to upgrade from PostgreSQL 9.1 to 9.2?

PostgreSQL 9.2 offers important new features and performance improvements and it has been included in the Amazon Linux AMI 2012.09 release based on customer requests.

After upgrading PostgreSQL from 9.1 to 9.2, the database service will no longer start. This happens because the 9.1 version of the database format is not immediately usable with the 9.2 server. We have provided the postgresql-upgrade package as an automatic install alongside the latest release of postgresql 9.2. This allows you to perform an in-place upgrade on your database using service postgresql upgrade.

Behind the scenes, this runs pg_upgrade to migrate your database to the new format. Note that the upgrade will reset configuration files such as pg_hba.conf to a clean state. Your old configuration files are stored in /var/lib/pgsql9/data-old, and can be copied over the default files in /var/lib/pgsql9/data after your review.

Once the upgrade is finished and the configuration files are restored, the service should start normally.

  • you're a star! I had no idea. I normally Google every possibility, but that one escaped me. So I ran the command "service postgresql upgrade" as suggested in that FAQ, and got "cat: /var/lib/pgsql9/data/PG_VERSION: No such file or directory... Cannot upgrade because database is not of version 9.1." Maybe I've stuffed it by emptying out /pgdata and running initdb -D /pgdata -E UTF8 again? Any other things you can suggest?
    – poshest
    Mar 8, 2013 at 22:32
  • @poshest: if the data directory for 9.1 was /pgdata and you deleted its contents before reissuing initdb with 9.2, then the 9.1 data is lost. If you restored it then you could do a symlink /var/lib/pgsql9/data to pgdata so that postgresql-upgrade could work. Mar 9, 2013 at 14:05
  • Great idea. Yes, it seemed the upgrade process didn't like the fact that I used a custom data directory. I kept copies of the original contents of /pgdata, so I restored it as you said by copying the 9.1 stuff back into /pgdata. Then ran service postgresql and got an error ... Checking current, bin, and data directories ok could not open file "/var/lib/pgsql9/data-old/postmaster.pid" for reading: Permission denied Failure, exiting. I checked /var/lib/pgsql9/ and there's no data-old directory at all.
    – poshest
    Mar 10, 2013 at 17:23
  • oh, sorry, and before running service postgresql upgrade, I created the symlink as you suggested
    – poshest
    Mar 10, 2013 at 19:05
  • I tried symlinking /var/lib/pgsql9/data-old to the original 9.1 data, and /var/lib/pgsql9/data to a new /pgdata which had had an initdb for v9.2 run on it. Still no luck: Cannot upgrade because database is not of version 9.1.. Why not? The old data is in data-old. So I made both ...data and ...data-old point to separate copies of the 9.1 pgdata, and still could not open file "/var/lib/pgsql9/data-old/postmaster.pid" for reading: Permission denied
    – poshest
    Mar 10, 2013 at 21:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .