PostgreSQL 15 is the last stable version released on October 13, 2022 which was announced by The PostgreSQL Global Development Group. PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
PostgreSQL 15 brings various improvements from the previous version, there are at least 15 bug fixes that exist in this 15 version. This release also updates time zone data files to use tzdata release 2022f. In this tutorial, we will learn how to install PostgreSQL 15 on Ubuntu 22.04 operating system.
PostgreSQL 15 Instalation on Ubuntu 22.04
Prerequisites
Before we are going to the install PosrtgreSQL 15 on Ubuntu 22.04 (Jammy Jellyfish), we have to prepare the environment first as mentioned below :
- Ubuntu 20.04 System with updated repository
- a user with sudo privilege
- Sufficient disk space (At least 2 GB of RAM with an additional 512 MB of disk space) and good internet access
The PostgreSQL 15 installation will be consist of several steps :
- Adding PostgreSQL 15 Repository To Ubuntu 22.04
- Install PostgreSQL 15 on Ubuntu 22.04
- Set PostgreSQL admin user’s password
- Enabling Remote Access
The points above will be discussed in detail in the sub-chapters below.
1. Adding PostgreSQL 15 Repository To Ubuntu 22.04
By default, PostgreSQL 15 package is not available in the Ubuntu 22.04 default package repository, so we havet to add and enable its official package repository by using the following commands.
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main 15" > /etc/apt/sources.list.d/pgdg.list' $ wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
After adding PostgreSQL 15 repository, then we will update the package repository to fetch the latest package registered on the system. This task will be performed by submitting command line :
$ sudo apt-get update
Output :
ramans@infodiginet:~$ sudo apt-get update
Hit:1 https://download.docker.com/linux/ubuntu jammy InRelease
Hit:2 http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease
Hit:3 http://id.archive.ubuntu.com/ubuntu jammy InRelease
Hit:4 http://security.ubuntu.com/ubuntu jammy-security InRelease
Hit:6 http://id.archive.ubuntu.com/ubuntu jammy-updates InRelease
Hit:7 http://id.archive.ubuntu.com/ubuntu jammy-backports InRelease
Hit:8 https://ppa.launchpadcontent.net/git-core/ppa/ubuntu jammy InRelease
Hit:5 https://packages.gitlab.com/gitlab/gitlab-ce/ubuntu focal InRelease
Reading package lists… Done
2. Install PostgreSQL 15 on Ubuntu 22.04
On this stage we will install PostgreSQL 15 on Ubuntu 22.04. For this purpose we will do following command lines.
$ sudo apt-get install postgresql-15
Output :
ramans@infodiginet:~$ sudo apt-get install postgresql-15 Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-5.15.0-41 linux-headers-5.15.0-41-generic linux-image-5.15.0-41-generic linux-modules-5.15.0-41-generic linux-modules-extra-5.15.0-41-generic Use 'sudo apt autoremove' to remove them. The following additional packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libtypes-serialiser-perl pgdg-keyring postgresql-client-15 postgresql-client-common postgresql-common sysstat Suggested packages: postgresql-doc-15 isag The following NEW packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 libtypes-serialiser-perl pgdg-keyring postgresql-15 postgresql-client-15 postgresql-client-common postgresql-common sysstat 0 upgraded, 12 newly installed, 0 to remove and 215 not upgraded. Need to get 43,6 MB of archives. After this operation, 173 MB of additional disk space will be used. Do you want to continue? [Y/n] Y . . . fixing permissions on existing directory /var/lib/postgresql/15/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Asia/Jakarta creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok update-alternatives: using /usr/share/postgresql/15/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Processing triggers for man-db (2.10.2-1) ... Processing triggers for libc-bin (2.35-0ubuntu3) ...
By default, PostgreSQL service is running after the installation was completed done. So, we will verify the installation by checking PostgreSQL 15 service by submitting command line :
$ sudo systemctl status postgresql
Output :
ramans@infodiginet:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2022-11-05 22:00:42 WIB; 2min 45s ago
Main PID: 26334 (code=exited, status=0/SUCCESS)
CPU: 5ms
Nov 05 22:00:42 infodiginet systemd[1]: Starting PostgreSQL RDBMS...
Nov 05 22:00:42 infodiginet systemd[1]: Finished PostgreSQL RDBMS.
The next step is to check the PostgreSQL version by using shell or psql command line utility.
$ /usr/lib/postgresql/15/bin/psql --version
$ sudo -u postgres psql -c "SELECT version();"
Output :
ramans@infodiginet:~$ /usr/lib/postgresql/15/bin/psql --version psql (PostgreSQL) 15.0 (Ubuntu 15.0-1.pgdg22.04+1)
ramans@infodiginet:~$ sudo -u postgres psql -c “SELECT version();”
could not change directory to “/home/ramans”: Permission denied
version --------------------------------------------------------------------------------------------------------------------- PostgreSQL 15.0 (Ubuntu 15.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit (1 row)
3. Set PostgreSQL Admin User’s Password
By default, PostgreSQL has a user with Superuser privileges, namely the postgres user. We can connect to the PostgreSQL database using this user without using a password. This, of course, will be very risky if our database accommodates sensitive data while other people can access it freely by using a user without a password.
At this stage, we will update the password of the postgres user. For this purpose we have to enter into the psql prompt and update the password using the sql script. At this tutorial, we will also create a new user called as root and set its privilege as Superuser. Then we will update the posrgres user password.
ramans@infodiginet:~$ sudo -i -u postgres postgres@infodiginet:~$ psql psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1)) Type "help" for help.
List of user :
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Create new user :
postgres=# CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'newpass123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
Update postgres password :
postgres=# ALTER USER POSTGRES WITH ENCRYPTED PASSWORD 'newpass123';
ALTER ROLE
4. Enabling Remote Access
PostgreSQL by default is only accepts connections from the localhost. However, we can modify it. So we can accept connections from other place than localhost. PostgreSQL stores its configuration in the postgresql.conf file, which is usually located in the following directory: /etc/postgresql/15/main/pg_hba.conf and /etc/postgresql/15/main/postgresql.conf.
vi ramans@infodiginet:~$ sudo vi /etc/postgresql/15/main/pg_hba.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
to be :
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
To allow the connection from other site, we have to change the parameter :
#listen_addresses = ‘localhost’ to be #listen_addresses = ‘*’.
ramans@infodiginet:~$ sudo vi /etc/postgresql/15/main/pg_hba.conf # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the database using some other method. # Noninteractive access to all databases is required during automatic # maintenance (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 192.168.1.0/24 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256
To allow IPv4 connections from all clients, we will allow the IP Address, by changing :
# IPv4 local connections: # IPv4 local connections: host all all 192.168.1.0/24 scram-sha-256
Conclusion
In this tutorial, we have learned how to install PostgreSQL 15 on Ubuntu 22.04 LTS operating system.
