PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It’s standards-compliant and has many advanced features like reliable transactions and concurrency without read locks.

As there could be some reasons for us to install multiple versions of PostgreSQL on the same machine, this tutorial demonstrates how to quickly get multiple versions of PostgreSQL up and running on a Ubuntu 22.04 machine, from installing PostgreSQL to setting up a new user (a.k.a role) and database.

Adding official PostgreSQL PPA

As Ubuntu 22.04 come fresh with PostgreSQL version 14 only and `apt` even does not know about other versions of Postgres, we need to add the official PPA from PostgreSQL author

# Create the file repository configuration: 
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' 
# Import the repository signing key: 
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg 
# Update the package lists: 
sudo apt-get update 

Install Postgres 9.6, 10, 12, 14

As the version 14 is the default one available in Ubuntu 22.04, we install it priorily

sudo apt install postgresql # version 14

Install the others

sudo apt install postgresql-9.6 # version 9.6 
sudo apt install postgresql-10 # version 10 
sudo apt install postgresql-12 # version 12 
sudo apt install postgresql-client # client version 14 

Now, you will have 4 versions of PostgreSQL up and running on different ports of the same machine.

Changing PostgreSQL listening port

By default, PostgreSQL installer will automatically set port for a newly installed PostgreSQL cluster by increasing the port number by 1. For the example in the procedure above, we installed PostgreSQl 14 first and it took the default port 5432. The second installed one is PostgreSQL 9.6 and it took the port 5433. And so on.

In case you would want to rearrange ports assignment, you could revise the corresponding PostgreSQL configuration files provided that the new port must not be used by any other PostgreSQL clusters nor other services on the same server. Here is the configration file locations for each PostgreSQL versions:

To change the port, you can use any text editor to edit the postgresql.conf. For example, I prefer to use nano:

nano /etc/postgresql/9.6/main/postgresql.conf

After the postgresql.conf is opened, you can use Ctrl+W to search for the directive port, and change its value to a port you prefer.

After revising the configuration file, you will need to restart all the postgresql instances:

sodu systemctl restart postgresql

Install additional facilities

The postgresql contrib package adds some additional utilities and functionality such as unaccent that helps query with accent insensitive. To install this package, just execute the following commands

sudo apt install postgresql-contrib-9.6 # for version 9.6 
sudo apt install postgresql-contrib # for other versions-

Log-in PostgreSQL

Postgres uses a concept called “roles” to handle authentication and authorization. Each role has username and group which are, in some ways, similar to regular Unix-style users and groups.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

At the same time, the installation procedure also created a user account called postgres that is associated with the default Postgres role. To utilize this account to access Postgres, you need to execute either of the following commands:

sudo su - postgres

or

sudo -i -u postgres

Then you can access the PostgreSQL prompt by running the following command:

psql # no port specified here for the port 5432

By default, if you do not specify port number with the comand psql, the port 5432 will be used. It means you will get PostgreSQL promt for the version 14. If you wish to login the version 9.6, please specify the corresponding port using -p option. For example:

psql -p 5433 # this logs you in PostgreSQL 9.6

Now, you can log out Postgres to bring you back to Linux command prompt by entering the following and hit Enter:

\q

To exit Linux postgres user, just run the command:

exit

There is another way to acccess PostgreSQL prompt:

sudo -u postgres psql -p 5432 # adjust port 5432 for other versions 

Create new role for each versions of PostgreSQL

If you are logged in as the postgres account, you can create a new role by executing the following command:

createuser --interactive

Otherwise, you could run the following:

sudo -u postgres createuser --interactive

Either of the above, the chosen command will promt you for name of role and ask you if you want to grant superuser rights to the new role.

Enter name of role to add: david 
Shall the new role be a superuser? (y/n)

When being asked for name of role, you could input the one that is the same as your Linux account so that you don’t need to create another Linux account later. In my case, I input `david`.

As the default port 5432 is assigned to the version 14 in this tutorial, the above command created role for version 14 only. To create role for other versions, you need to use -p option to specify a port that corresponds to the version you want. For example, to create a role for version 9.6 and 10, you could execute the following Linux commands:

sudo -u postgres createuser --interactive -p 5434

Login PostgreSQl with new roles

The following will log you in PostgreSQL 14 with role david for the database postgres.

sudo -u david psql postgres

By default, PostgreSQL authentication system tries to connect you to the database of the same name as the role’s. That’s why we need to specify postgres at the end of command to ask it to connect the database postgres instead.

To login another version, for example 9.6, please execute the following command

sudo -u david psql -p 5433 postgres

Create a new database

If you are logged in as the postgres account, you could execute the following command to create a database named `david` for PostgreSQL 14:

createdb david

Or, if you prefer sudo when you are not logged in as david

sudo -u postgres createdb david

Install pgAdmin 4

pgAdmin 4 is a tool that helps you manage your PostgreSQL database using web-based graphical user interface that gives more convenient than using command lines.

You can read my post on how to install pgAdmin 4 on Ubuntu 22.04 and follow the instructions there to install pgAdmin 4 Desktop for your development environment.

Please note that, depending on your demands, you may need to either install pgAdmin 4 server or to install pgAdmin 4 Desktop or both.