2

With many started postgresql services, psql chooses the lowest postgresql version

I have installed two versions of postgresql, 12 and 13 (in an earlier version of this question, these were 9.1 and 9.2, I change this to be in line with the added output details from the higher versions).

sudo service postgresql status
12/main (port 5432): down
13/main (port 5433): down

They are located at /etc/postgresql/12/ and /etc/postgresql/13/.

After installing an extension on version 13:

sudo apt-get install postgresql-contrib postgresql-plpython3-13

start the postgresql service:

sudo service postgresql start

which outputs:

 * Starting PostgreSQL 12 database server                                                                                                                                                                                             
 * Starting PostgreSQL 13 database server

Now let us create the extension in the database, running:

sudo su - postgres

and then:

postgres=# psql
psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1), server 12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# CREATE EXTENSION plpython3u;
ERROR:  could not open extension control file "/usr/share/postgresql/12/extension/plpython3u.control": No such file or directory

We see that the extension is searched in version 12 although I have installed the postgresql-python3u to the directory of version 13.

Aim

I want to use version 13 only, I don't need two different versions, and psql seems to choose the lowest available postgresql version of the started services by default, not the highest which I need.

How to either remove version 12 safely or make 13 the only started (or default) service, also using the standard port 5432 for version 13?

questionto42
  • 7,175
  • 4
  • 57
  • 90
Incerteza
  • 32,326
  • 47
  • 154
  • 261
  • 1
    I guess you should ask this kind of question on http://superuser.com/ and not here – Adassko Dec 17 '13 at 08:54
  • I had this problem and it offered the solution. So why should it not be part of the knowledge base? The edit did not lead to a reopening since the `Original close reason(s) were not resolved`, I would like to know how this could be improved, then, to become on-topic for Stack Overflow. – questionto42 Sep 02 '21 at 06:34

2 Answers2

6

This situation with two clusters in Ubuntu may happen when upgrading to a newer release providing an newer postgresql version.

The automatic upgrade does not remove the old cluster, presumably for fear of erasing valuable data (which is wise because some postgres upgrades may require human work to be complete).

If you know you want to drop it, just run:

sudo pg_dropcluster --stop 9.1 main

The corresponding data directory will be removed and service postgresql will no longer refer to 9.1

At this point the 9.2 cluster will still use the port 5433, which is unpractical.

To switch it to the default port, edit /etc/postgresql/9.2/main/postgresql.conf and change the line port = 5433 to port = 5432

Then restart PostgreSQL.

Finally to get rid of the postgresql-9.1 packages see the result of dpkg -l 'postgresql*9.1*'

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
2

psql fails because none of your postgres is running.
First, you should understand why there are 2 different servers, then delete one of them (through apt-get, I think), and if necessary reconfigure the other (if you type sudo service portgresql start, both of the servers will start, and to connect to 9.2 you must use psql --port=5433).
Edit your question to add more informations (Version of Ubuntu, origin of Postgres, etc.)...

AndrewQ
  • 409
  • 1
  • 13
  • 22