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:
|Version||Configuration File Location|
To change the port, you can use any text editor to edit the postgresql.conf. For example, I prefer to use nano:
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-
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
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:
To exit Linux postgres user, just run the command:
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:
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:
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.