Eliminating non-working PostgreSQL installations on Ubuntu 10.04 and starting afresh

Question

I find I have the wreckage of two old PostgreSQL installations on Ubuntu 10.04:

$ pg_lsclustersVersion Cluster   Port Status Owner    Data directory                     Log file
Use of uninitialized value in printf at /usr/bin/pg_lsclusters line 38.
8.4     main      5432 down   /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log  
Use of uninitialized value in printf at /usr/bin/pg_lsclusters line 38.
9.1     main      5433 down   /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log  
$

Attempts to perform basic functions return errors, for instance:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

More information comes when I try to start the database server:

$ sudo /etc/init.d/postgresql start 
 * Starting PostgreSQL 9.1 database server
 * Error: The cluster is owned by user id 109 which does not exist any more
   ...fail!
$ 

My question: how do I completely remove both clusters and set up a new one? I've tried removing, purging, and reinstalling postgresql, following the advice here: https://stackoverflow.com/a/2748644/621762. Now pg_lsclusters shows no clusters in existence, but the No such file or directory error persists when I try to createuser, createdb or run psql. What have I failed to do?

Answer 1

First, that answer you linked to was pretty unsafe - hand-editing /etc/passwd ?!? dselect where an apt wildcard would do? Crazy stuff. I'm not surprised you're having issues.

As for the no such file or directory messages: You need to make sure you have a running PostgreSQL server ("cluster") before you can use admin commands like createdb, because they make a connection to the server. The No such file or directory message is telling you that the server doesn't exist or isn't running.

Here's what's happening:

Ubuntu uses pg_wrapper to manage multiple concurrent PostgreSQL instances. The issues you're having are really with pg_wrapper.

Ideally you would've just used pg_dropcluster to get rid of the unwanted clusters. Unfortunately, by following bad advice it sounds like you've got your system into a bit of a messed-up state where the PostgreSQL packages are half-installed and kind of mangled. You need to either repair the install, or totally clean it out.

I'd clean it out. I'd recommend:

• Verify that pg_lsclusters lists no database clusters
• apt-get --purge remove postgresql\* - this is important
• Remove /etc/postgresql/
• Remove /etc/postgresql-common
• Remove /var/lib/postgresql
• userdel -r postgres
• groupdel postgres
• apt-get install postgresql-common postgresql-9.1 postgresql-contrib-9.1 postgresql-doc-9.1

It's possible that the apt-get --purge step will fail because you've removed the user IDs, etc. Re-creating the postgres user ID with useradd -r -u 109 postgres should allow you to re-run the purge successfully then delete the user afterwards.