CODEWITHSUNDEEP
linuxpostgresqlcommand-line-interfacepsqlubuntu-20.04
Chris Rad
Chris Rad

Reputation: 51

How to configure postgres to run only one version

I'm pretty new to using postgresql and seemed to have installed two versions that are running at the same time. Both 12 and 14.

Below is the list of commands that I've run, and the results they've yielded.

From WSL-2 Ubuntu 20.04 CLI

➜ which psql

/usr/bin/psql

➜ pg_config --version

PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg20.04+1)

➜ psql --version

psql (PostgreSQL) 14.4 (Ubuntu 14.4-1.pgdg20.04+1)

From psql

=# SHOW server_version;

           server_version
------------------------------------
 12.11 (Ubuntu 12.11-1.pgdg20.04+1)
(1 row)

=# SELECT version();

                                                               version
-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.11 (Ubuntu 12.11-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

I've have some persistent issues with my odoo 15 db and think this may be the cause but am unsure.

Any recommendations on how to transition from 12.11 server to 14.4 server and remove 12.11 server would be greatly appreciated.

Upvotes: 1

Views: 1571

Answers (2)

András Aszódi
András Aszódi

Reputation: 9660

I also ended up with two Postgres servers with different versions following a major Ubuntu upgrade. It turns out that there is a less radical way to get rid of the old version:

  1. Back up all databases: pg_dumpall -h localhost -U postgres -W > all_pg.sql. This will ask for the postgres user's password as many times as there are databases in the cluster.
  2. Stop the new (Version 14) DB cluster and drop it: sudo pg_dropcluster --stop 14 main
  3. Upgrade the old (Version 12) cluster to Version 14: sudo pg_upgradecluster 12 main
  4. Drop the old (Version 12) cluster: sudo pg_dropcluster 12 main
  5. Remove the old Version 12 packages: sudo apt-get purge postgresql-12 postgresql-client-12

In summary, there is no need to sudo apt purge all Postgres packages and then reinstalling the most recent one.

Upvotes: 0

Chris Rad
Chris Rad

Reputation: 51

Thanks to all the above for the help!

Commands I ran to fix on Ubuntu 20.04 / Windows-11 WSL-2:

  1. $ ~ pg_dumpall > export.sql
  2. $ ~ sudo apt-get --purge remove postgresql\*
  3. $ ~ sudo apt-get install postgresql-14 (or -version preferred)
  4. $ ~ sudo -u postgres -i
  5. postgres ~ $ psql
  6. #= CREATE ROLE username superuser
  7. #= ALTER ROLE username WITH LOGIN;
  8. #= \password username;
  9. #= \q
  10. postgres ~ $ exit

Upvotes: 2

Related Questions