32

I have 2 different versions of PostgreSQL installed on my system (Ubuntu Lucid):

  • /var/lib/postgresql/8.4
  • /var/lib/postgresql/9.0

By default, when I run a PostgreSQL command such as createdb or psql, the 9.0 version is used.

How do I configure my system to use the 8.4 version by default instead?

Jorge Castro
  • 70,934
  • 124
  • 466
  • 653
coffee-grinder
  • 3,875
  • 8
  • 25
  • 22

3 Answers3

20

The various PostgreSQL command line tools will talk to the server listening on the default port (5432) by default.

You can determine which port each server is listening on by looking for the port variable in the /etc/postgresql/$VERSION/main/postgresql.conf file for the relevant server.

To get the command line tools to talk to the other server by default, you have two options:

First, you could switch the ports the two servers are listening on by editing the previously mentioned configuration files and then restarting both servers (you'll probably want to stop each one before starting either).

Alternatively, you can set the PGPORT environment variable to the port number of the desired default server. That should affect all applications using the PostgreSQL client library.

To list the contents of each database cluster use psql -l -p PORT_NUMBER. To migrate data see section "24.4. Migration Between Releases" in the PostgreSQL documentation.

Deleting old versions of PostgreSQL saves wear and tear on laptops & SSDs, through reduction of disk writes.

Bryce
  • 1,857
  • 2
  • 18
  • 30
James Henstridge
  • 40,476
  • 13
  • 109
  • 92
  • Good answer. Additionally, consider deleting the old versions of postgres. Each of them uses ram, and writes to the disk once a minute (bad for SSD's and laptops). – Bryce Mar 05 '15 at 17:21
3

As James/Bryce correctly point out, the two versions will run on different ports.

However the command line utilities will also be run as one version or the other. For example pg_dump --version might give you an unexpected result.

You can configure this in a file called ~/.postgresqlrc

First you need to know the cluster names, to find this, run the following command:

$ pg_lsclusters

This will output the clusters available eg on my system I'm running 9.6 and 13:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
13  main    5433 online postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log

Then create the file ~/.postgresqlrc and put the following to control the default version used:

# Version Clustername Database
13 main postgres

Change 13 to the version you want to run as default, in the case of this question that will be 8.4 or 9.0.

John Simmonds
  • 613
  • 6
  • 9
3

If you want to get rid of a version so the other one becomes default...

# see what you've got...
pg_lsclusters

# drop v13
sudo pg_dropcluster 13 main --stop
Harley
  • 139
  • 2
  • 1
    Suggesting to drop a whole database cluster is potentially VERY dangerous advice. Are you sure? – Erwin Brandstetter Feb 22 '22 at 20:48
  • Mine was a fresh setup with no data. So, use at own risk? – Harley Feb 23 '22 at 21:12
  • 1
    A word or warning wouldn't hurt. I guess my comment takes care of that now. – Erwin Brandstetter Feb 23 '22 at 21:14
  • After running the command, `psql --version` still returns the version that I've just deleted (dropped ?), even if not returned any more by `pg_lsclusters`. Edit: fixed, thanks to [how-to-uninstall-software-using-the-command-line-in-linux](https://www.howtogeek.com/229699/how-to-uninstall-software-using-the-command-line-in-linux/). – ThCollignon Mar 18 '22 at 07:32
  • THIS WILL DELETE all your data on the dropped cluster. – Capaj Jul 24 '22 at 07:45