I think I have multiple postgresql servers installed, how do I identify and delete the 'extra' ones?

I seem to have a few installations of postgresql on my machine somehow. I'm not sure if this is a mistake or, if Ubuntu for some odd reason duplicates direcotories and keeps them elsewhere.

I have a postgresql directory in /etc

one in /usr/lib

and one in /opt

I'm properly confused at this point.

How do I go about deleting the extra ones?.
Which ones are he extra one?
I also need to make sure that my 'pg' gem in my rails env is pointing towards the correct posgresql db server.

Any thoughts on my issue would be huge.

There can be some confusion over what people mean by an installation.

• The /etc/postgresql/ folder is the config folder for your clusters.
• The /var/lib/postgresql/ folder is for data.
• The program binaries for each version are in separate folders usually in /usr/lib/postgresql/.

I really don't know about /opt/postgresql as I don't have that on mine. But /opt is for "optional" binaries, so it's possible that your installation is here instead of/usr/lib/postgresql/.

In short, I think you may just have one installation which has files in multiple locations.

If you want to look at what you have installed, this may help:

How postgresql is structured:

To make things a little clearer postgres is structured as follows:

• A version literally refers to which version of the postgresql program binaries. Each installed version may have a cluster installed under that version. If not then that version is effectively dormant as it has no data or running server associated.

• Under each version there many be a number of clusters. You can think of the cluster as a running prostgres server (process). Each cluster has to have its own port/socket file for clients to connect to. Each cluster will be managed by a single version.

• Inside each cluster will be a number of databases. When a client connects it selects a DB to connect to. It can ask to change which DB it's connected to without opening a new session, but it can only ever be connected to one.

What have you got installed?

To find out which versions are installed you can look to dpkg and apt. You should be able to uninstall versions using apt and dpkg, but be very careful not to do this before you've checked what clusters are under each version.

To find out what clusters you have use the command pg_lsclusters. When I call this I get the following, you will get something different:

Version Cluster   Port Status Owner      Data directory                     Log file
9.1     main      5432 online <unknown>  /var/lib/postgres/data/9.1/main    /var/log/postgresql/postgresql-9.1-main.log

Pay careful attention to the "Status" column. If a cluster is not online then it's just data on disk and is doing nothing. If it is online then it is running.

How do you merge clusters?

You can copy the content from one cluster to another using the pg_dumpall command to generate a backup and use psql to import it to the cluster you want to keep. Its worth keeping backups of everything before you start.

How do you remove a cluster that is no-longer used?

• Use pg_lsclusters to get the details about the clusters and note the data directory and log file for those clusters.

• Use pg_ctlcluster <version> <cluster> stop to stop the cluster.

• Remove the data folder and optionally the log file.

• Finally remove the data and config. The data folder should be /var/lib/postgres/data/<version>/<cluster> but check the output of pg_lsclusters to be sure of this. The config for the cluster: All clusters will have their own config folder in
  /etc/postgresql/<version>/<cluser>/.

Why did you get multiple clusters if you never asked for them?

Usually you have to specifically request a cluster to be created to get a new one. The only exception to this is when you upgrade a cluster, it will effectively create a new one and leave the old one in place.