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.

Answer 2

This answer is not directly about removing a postgres instance, rather, about resoliving the issue,

Error: The cluster is owned by user ...

I got this error while trying to spin up a docker container pointed to a postgres data directory that was produced via a different container (on a different host machine).

The error is directly related to directory ownership. In my case, the system was unable to find the user that certain postgres directories was owned by in the current environment. By re-owning those directories to the right user resolves the issue. Following is an example mapping (that worked for me):

chown -R postgres:postgres /var/lib/postgresql
chown -R postgres:postgres /etc/postgresql
chown -R postgres:postgres /var/log/postgresql
chown -R postgres:postgres /var/run/postgresql