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)
Jan 20 17:00:18 minerva systemd: Starting LSB: Start the PostgreSQL master daemon…
Jan 20 17:00:18 minerva postgresql: Your database files were created by PostgreSQL version 9.1.
Jan 20 17:00:18 minerva postgresql: Could not find executables for this version.
Jan 20 17:00:18 minerva postgresql: 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
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
Type "help" for help.
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.