Linux – Postgresql unintended upgrade to 9.2.3

linuxpostgresqlyum

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?

Thanks!

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.

Best Answer

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.

Related Solutions

Ubuntu – Multiple instances of Postgres after Ubuntu upgrade

Ubuntu being a Debian-based system has the ability to run multiple instances of Postgres as you are seeing. Generally unless configured otherwise the first instance started will have the default Postgres port and each subsequent instance will take the next higher port if it is available. These ports can be defined in the config files under /etc/postgresql/<version>/<cluster>/ so that they don't change should you need to but I'd actually need to. In fact a cursory look at the /etc/init.d/postgresql would also show you that you can set environment variables through /etc/postgresql/<version>/<cluster>/environment.

The package managers were even nice enough to include /usr/share/doc/postgresql-common/README.Debian.gz which has a section that quickly explains how the cluster management is handled including the pg_lsclusters command which will give you the Postgres version and port number along with other nice details about each configured cluster.

To answer question in comment regarding stopping an instance... You can edit the start.conf file under /etc/postgresql/<version>/<cluster>/ and change it from auto to disabled or manual. To quote the default start.conf file itself...

# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
#         pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
#           circumvented and is only meant to be a small protection for
#           accidents).

Postgresql – Complete solution to upgrade PostgreSQL on debian production server

It's pretty hard to give you a step-by-step upgrade process that's tailored to your situation/environment.
I've tried to hit some of the highlights below, but you really need to tailor the process to your environment.

Step Zero, decide if you need to upgrade. Postgres 9.1 brings Synchronous Replication and a bunch of other cool stuff which is useful if you need it, but not a reason to upgrade right away if you don't.

Step 1, expect that this will take a long time if your database is big.
Even using pg_upgrade the simple process of copying the data for a backup can take a while: our 16GB databases take an hour or morefor a pg_dump/pg_restore, or about 10-20 minutes for a pg_upgrade).

Step 2, I suggest reading through the 9.1 Release Notes. Make sure nothing you need is broken and nothing you use has changed.
There is nothing like a database upgrade that destroys your production systems to really ruin your month.

Step 3, The upgrading section of the Postgres manual is required reading.

Step 4, (insert any Debian-specific stuff here, but that's really not my bag :-)

Step 5, Create an upgrade plan.

Step 6, Test the upgrade plan (technically optional, but strongly recommended).

Step 7, Do the upgrade on your production systems.

Standard caveats apply:

Upgrading could destroy your database. Make a backup.
- Be sure you can restore it.
- Be sure you can restore the binaries for the database system too.
  (Hang on to your old Debian packages if you can - I've seen people unable to back out of a failed upgrade because they don't have the old database binaries anymore, and it's a very sad sight indeed.)
It will take longer than you expect. Plan for this.
My company's DB upgrade policy is "Start at 6PM on Friday. If it's not working by 9AM on Sunday roll back."
Mount a scratch monkey
If your data is really important restore a copy of it elsewhere and test your upgrade path.
Yeah, I do this with 16GB databases. I'll still do it with 160GB databases if I can find a way to make it work :-)

Best Answer

Related Solutions

Ubuntu – Multiple instances of Postgres after Ubuntu upgrade

Postgresql – Complete solution to upgrade PostgreSQL on debian production server

Related Topic