SuSE 12.3 — PostgreSQL 9.2 error: Your database files were created by PostgreSQL version 9.1

I recently had to create some databases at a university SuSE Linux box, but the PostgreSQL installation was completely broken. At first, it looked good:

minerva:/var/lib # systemctl start postgresql

No errors as expected, but then:

minerva:/var/lib # psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Huh? I checked config file, looks fine and the server should run, but ps and netstat show that it really does NOT. Let’s see what systemctl thinks:

minerva:/var/lib # systemctl status postgresql
postgresql.service - LSB: Start the PostgreSQL master daemon
Loaded: loaded (/etc/init.d/postgresql)
Active: active (exited) since Mon, 2014-01-20 17:00:18 CET; 19h ago
Process: 18365 ExecStop=/etc/init.d/postgresql stop (code=exited, status=0/SUCCESS)
Process: 18375 ExecStart=/etc/init.d/postgresql start (code=exited, status=5)
CGroup: name=systemd:/system/postgresql.service

Jan 20 17:00:18 minerva systemd[1]: Starting LSB: Start the PostgreSQL master daemon…
Jan 20 17:00:18 minerva postgresql[18375]: Your database files were created by PostgreSQL version 9.1.
Jan 20 17:00:18 minerva postgresql[18375]: Could not find executables for this version.
Jan 20 17:00:18 minerva postgresql[18375]: Please install the PostgreSQL server package for version 9.1.

OK, looks like a PostgreSQL version conflict between 9.2 binaries and a 9.1 data directory. It could have mentioned this when I told it to start the server, but k. The interesting questions are:

What caused this? Answer: I’m not sure, but most likely someone installed and used PG 9.1 on the system a while ago, then the box was upgraded to a new SuSE release (openSUSE 12.3, Dartmouth) which installed PG 9.2 binaries but did nothing with the data directories, so now all databases are inaccessible. Of course, PG 9.1 is not in the package system of the new SuSE release.

So what now?

Let’s create a new database directory for PG 9.2 and init a new database there:

root@minerva:/ # mkdir /var/lib/pgsql92/
root@minerva:/ # chown -R postgres: /var/lib/pgsql92/

Now init a new data directory there:

root@minerva:/ # su postgres
postgres@minerva:/ $ initdb /var/lib/pgsql92
...
Success. You can now start the database server using:
postgres -D /var/lib/pgsql92
or
pg_ctl -D /var/lib/pgsql92 -l logfile start

I am not interested in the old database and do not want to start PG with the special directory everytime. I could edit the data directory in the settings, but I’ll just move the old one away and move our new directory to the default data dir location instead:

root@minerva:/ # cd /var/lib/
root@minerva:/var/lib/ # mv pgsql pgsql_91_unused
root@minerva:/var/lib/ # mv pgsql92 pgsql
root@minerva:/var/lib/ # su postgres

OK, now it should work with the new data directory:

postgres@minerva:/var/lib/ $ psql
psql (9.2.4)
Type "help" for help.
postgres=#

And it does! 🙂

NOTE: If you want to keep the old databases, you can convert them with the pg_upgrade tool. This is NOT installed by default with the PG server under SuSE Linux, you need to install the postgresql92-contrib package like this:

zypper in postgresql92-contrib

Check the documentation for info on how to use pg_upgrade.

Advertisements

About dfspspirit

PhD student in bioinformatics, interested in photography, level design, digital image manipulation, architecture and, of course, bioinformatics.
This entry was posted in databases, general, troubleshooting and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s