Home » Ubuntu » Install PostgreSQL 15 on Ubuntu 22.04 | 20.04
Install PostgreSQL 15 on Ubuntu 22.04 | 20.04
Last Updated on by Joshua James
PostgreSQL 15 is out and delivers many performance improvements. The new release improves PostgreSQL’s capabilities in several key areas, notably performance, data compression, and security. In terms of performance, PostgreSQL 15 includes several enhancements that should help to improve the database’s overall speed and responsiveness.
Some of the highlights of the release are as follows.
Improved Sort Performance and Compression.
Expressive Developer Features.
More Options with Logical Replication.
Logging and Configuration Enhancements.
The following tutorial will demonstrate how to install the software on Ubuntu 22.04 Jammy Jellyfish and 20.04 Focal Fossa Linux using the command line terminal and the PostgreSQL APT repository.
Table of Contents
Recommended Steps Before Installation
Before proceeding with the tutorial, ensuring your system is up-to-date with all existing packages is good.
sudo apt update
Optionally, you can list the updates for users who require review or are curious.
sudo apt --list upgradable
Proceed to upgrade any outdated packages using the following command.
sudo apt upgrade
Install Required Packages
The following packages must be installed to assist in installing the database software.
Next, you can import the stable or testing repository, depending on your desired installation. Use the stable repository for production, as it’s often updated quickly since you are installing directly from PostgreSQL repositories. The testing is for testing, as sometimes it can be behind or ahead, which is unsuitable for production.
Remember, import the version that matches your distribution LTS version; if you grab the wrong one, your installation will likely fail.
Import stable build (Recommended)
Import Jammy Jellyfish 22.04 stable repository:
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main | sudo tee -a /etc/apt/sources.list.d/postgresql.list
Import Focal Fossa 20.04 stable repository:
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main | sudo tee -a /etc/apt/sources.list.d/postgresql.list
Import snapshot build
Import Jammy Jellyfish 22.04 snapshot repository:
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg-snapshot main | sudo tee -a /etc/apt/sources.list.d/postgresql-testing.list
Import Focal Fossa 20.04 snapshot repository:
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg-snapshot main | sudo tee -a /etc/apt/sources.list.d/postgresql-testing.list
Import testing build (Caution)
Import Jammy Jellyfish 22.04 testing repository:
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg-testing main | sudo tee /etc/apt/sources.list.d/postgresql-testing.list
Import Focal Fossa 20.04 testing repository:
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg-testing main | sudo tee /etc/apt/sources.list.d/postgresql-testing.list
Install PostgreSQL 15
Now that you have imported the stable or testing repository of PostgreSQL, you need to update your repository sources list to reflect the new addition with the following command.
Alternatively, you can use just the non-specific install command. This is useful for users that install the snapshot or testing repositories, where future versions will always be prioritized, such as PostgreSQL 16, etc.
sudo apt install postgresql-client postgresql -y
Next, verify the status to ensure the software is installed and activated without errors using the following command.
By default, this should be enabled during the installation. If PostgreSQL is not activated, use the following command to enable it.
sudo systemctl enable postgresql --now
Example Service Commands
The PostgreSQL database server runs as a service under the name “PostgreSQL,” which can be managed with the systemd with the following command examples.
Stop PostgreSQL server
sudo systemctl stop postgresql
Start PostgreSQL server
sudo systemctl start postgresql
Restart PostgreSQL server
sudo systemctl restart postgresql
Reload PostgreSQL server
sudo systemctl reload postgresql
Check PostgreSQL status
systemctl status postgresql
Configure the PostgreSQL Server
Switching to the Postgres account
Before you continue, note that only superusers and roles with create role privilege can create new roles in Postgres. Postgres’s user account associated with the default Postgres role was created during the installation.
To engage with the account, you can do the following commands:
sudo -i -u postgres
By entering the above, you can immediately access the PostgreSQL prompt by typing “psql”.
psql
Next, you will notice that the terminal has changed with “postgres=#” now being in the terminal command line. This means you have successfully connected to the database.
To exit the Postgres database, you can do this by typing the following:
exit
Alternative to switching Postgres account
An alternative way to interact with the Postgres database without changing user accounts is to use a sudo command to connect directly. You can do this by typing:
sudo -u postgres psql
This is ideal to use more, as it saves time logging you directly without having to do extra terminal bash commands.
As the first option, you can exit by typing the following to exit the terminal.
exit
Create User & Database
Now you can create user roles if you wish. Only superusers and roles with “createrole” privilege can create new roles.
Create a new user; type the following.
sudo su - postgres -c "createuser <name>"
Now you can create a PostgreSQL database for the new user you created:
sudo su - postgres -c "createdb <namedb>"
To finish off, you need to switch to the superuser Postgres account to grant permission to the new database.
Connect to Postgres superuser account
sudo -u postgres psql
Grant access to <username database> to <username>
GRANT ALL PRIVILEGES ON DATABASE <usernamedb> TO <name>;
Example in your terminal output:
psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1))
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO josh;
GRANT
postgres=#
Once done, to exit, type the following
exit
Configure UFW Firewall
You will need to create a UFW firewall rule to allow PostgreSQL to communicate on its default port, 5432, which is required if you plan to use anything besides localhost.
First, ensure UFW is enabled.
sudo ufw enable
This can be done by typing the following command.
Subnet range:
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432
Individual IP:
sudo ufw allow proto tcp from 192.168.1.0 to any port 5432
How to Enable Remote Access
If you require default access to PostgreSQL, this can be quickly done by changing the default listening to the local interface of (127.0.0.1) to an IP, subnet, or all interfaces in the configuration file.
Remember to ensure that UFW has been set up correctly and does not block remote access as per the previous section.
First, determine which version of Postgresql you are using the ls command.
ls /etc/postgresql/
The tutorial has one of the latest versions installed, which is 15. This opens up the “postgresql.conf” file using the nano text editor.
sudo nano /etc/postgresql/15/main/postgresql.conf
Now scroll down until you find “Connection Settings” and change the (listen_addresses = ‘localhost’) to the <IP> address. Most would opt to listen to all interfaces, especially when running in multiple servers requiring connecting to the PostgreSQL database by changing localhost to (*).
Save the file (CTRL+O), exit (CTRL+X), and restart your PostgreSQL instance.
sudo systemctl restart postgresql
Users can do this with the ss utility built into Ubuntu to check that your changes are live.
ss -nlt | grep 5432
For further customization and securing PostgreSQL, you can configure the server to accept remote connections by editing the “pg_hba.conf” file using the nano text editor.
sudo nano /etc/postgresql/15/main/pg_hba.conf
You can set up various options from this point, such as changing “all” to a specific username and database, along with the address and authentication methods.
