How to install PostgreSQL 15 on Ubuntu 22.04

In this guide, we are going to walk through installing PostgreSQL 15 on Ubuntu 22.04.

PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. It was designed to handle a range of workloads from single machines to data warehousing.

Every single database must conform to ACID properties i.e Atomicity, Consistency, Isolation, and Durability. This is true for PostgreSQL as well, it must conform to those features for transactions to pass the tests.

Notable Features introduced for PostgreSQL 15.

• It removes both the long deprecated exclusive backup and support from Python 2 from PL/Python.
• It revokes the CREATE permission from all users except for the database owner from the default.
• It adds the new built-in extension, the pg_walinspect, that lets users inspect the contents of write-ahead log files directly from an SQL interface.
• Server-level statistics are now collected in shared memory, eliminating the statistics collector process and periodically writing this data to disk.
• It makes it possible to make an ICU Collation the default collation for a cluster or an individual database.
• It introduces the new logging format jsonlog which outputs log data using a defined json structure and allows PostgreSQL logs to be processed in structured logging systems. This gives database administrators more flexibility on how users can manage PostgreSQL configurations.
• It provides more flexibility for managing logical replication. It introduces row filtering and column lists for publishers, letting users choose to replicate a subset of data from a table.
• It added a feature to simplify conflict management, including the ability to skip replaying a conflicting transaction and to automatically disable a subscription if an error is detected.

1. Update the Ubuntu repository

The first thing to do is to update the ubuntu repository in order to make them up to date.

$ sudo apt update && apt upgrade -y

2. Install PostgreSQL 15 on Ubuntu 22.04

In order to run PostgreSQL 15 on Ubuntu 22.04, we are supposed to add 15 component to /etc/apt/sources.list.d/pgdg.list entry so that version 15 will be available for installation.

In order to add 15 to sources.list file, use the following command.

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'

Next, we need to import the repository signing key.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

The next thing is to update the system again for the changes to take effect.

sudo apt update 

Lastly, install PostgreSQL with the following command.

apt-get install postgresql-15

You will get the following as the output

The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl pgdg-keyring
  postgresql-client-15 postgresql-client-common postgresql-common ssl-cert
  sysstat
Suggested packages:
  lm-sensors postgresql-doc-15 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl pgdg-keyring
  postgresql-15 postgresql-client-15 postgresql-client-common
  postgresql-common ssl-cert sysstat
0 upgraded, 15 newly installed, 0 to remove and 1 not upgraded.
Need to get 43.7 MB of archives.
After this operation, 173 MB of additional disk space will be used.
Do you want to continue? [Y/n] 

After installation is complete, we can check to see if PostgreSQL is running with the following command.

$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Fri 2022-10-14 07:14:55 UTC; 3min 55s ago
    Process: 16129 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 16129 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Oct 14 07:14:55 ip-172-31-88-167 systemd[1]: Starting PostgreSQL RDBMS...
Oct 14 07:14:55 ip-172-31-88-167 systemd[1]: Finished PostgreSQL RDBMS.

3. Setting up password for Postgres role

Local connections on Posgtresql use peer to check the authenticated users. That is Postgresql instead of asking for the user password, it only checks on the logged-in user to ascertain if the user has credentials to get access to the resources.

Let’s change the way we log in and use a more strong password instead. Let’s set the password for the Postgres user first.

Open psql as the Postgres user with this command sudo -u postgres psql

$ sudo -u postgres psql

You will get output like this.

could not change directory to "/root": Permission denied
psql (15rc2 (Ubuntu 15~rc2-1.pgdg22.04+1))
Type "help" for help.

postgres=# SHOW server_version;
           server_version            
-------------------------------------
 15rc2 (Ubuntu 15~rc2-1.pgdg22.04+1)
(1 row)

The reason we are getting could not change directory to "/root": Permission denied psql is because of the Postgres authentication, we need to set the password first.

To change the Postgres password use the following command inside of the Postgres interface.

postgres=# ALTER USER POSTGRES WITH ENCRYPTED PASSWORD 'strgpasswd';

After we have set the password, we now need to tell Postgres to use this password. To do that we need to change pg_hba.conf file. I am using vim as my text editor here, you are free to use your preferred editor as well.

Look for this line of code and change peer to md5

local  all  postgres  peer
local  all  postgres  md5

To edit the configuration file use the following command.

sudo vi /etc/postgresql/15/main/pg_hba.conf

Restart PostgreSQL for the changes to take effect.

sudo systemctl restart postgresql

We can also ascertain whether PostgreSQL is running on our system with the following command.

sudo systemctl status postgresql

If you get the status active then we are good to go.

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Fri 2022-10-14 07:27:38 UTC; 14s ago
    Process: 16326 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 16326 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Oct 14 07:27:38 ip-172-31-88-167 systemd[1]: Starting PostgreSQL RDBMS...
Oct 14 07:27:38 ip-172-31-88-167 systemd[1]: Finished PostgreSQL RDBMS.

After this, we can log in again to the Postgres

psql -U postgres
Password for user postgres: 
psql (15rc2 (Ubuntu 15~rc2-1.pgdg22.04+1))
Type "help" for help.

postgres=# 

We have successfully changed Postgres to use password authentication.

Conclusion

Congratulations, we have successfully installed PostgreSQL 15 on Ubuntu 22.04 and also set the password for the default user Postgres. For more information get the latest from PostgreSQL documentation.