Install and Configure PostgreSQL 16 on Ubuntu [Guide]

Welcome to this guide on how to install and configure PostgreSQL 16 on Ubuntu 22.04|20.04|18.04.

Original content from computingforgeeks.com - post 145060

PostgreSQL is one of the popular relational databases that has been in existence for over 35 years. It was developed by the University of California at Berkeley in 1986. PostgreSQL also known as Postgres holds a high reputation for data integrity, extensibility, high performance and reliability. This database system finds high use in areas where high performance is required. Some of these areas include analytics, and web and mobile apps.

On 14th September 2023, PostgreSQL made another powerful release, PostgreSQL 16 which comes as a predecessor to PostgreSQL 15. This new release comes with a lot of cool features and improvements that benefit both the developers and administrators. There are also several advancements in monitoring, SQL/JSON syntax, and managing access policies amongst many others.

[ebook product=”2″ theme=”dark”]

Below are the features and improvements made in PostgreSQL 16:

Improved monitoring capabilities: this new release includes pg_stat_io which can be used to examine the I/O statistics. It records all the statistics in the last sequential and index scans in tables.
Improved performance: There are several performance enhancements in PostgreSQL 16. These include the addition of heap and index pages, caching RANGE and LIST partition lookups, controlling shared buffer usage by vacuum and analyze, increased speed of updating the process title etc.
Logical replication enhancement: In PostgreSQL 16, a significant enhancement has been introduced in the form of direct logical replication from standby instances. This means that a server can now efficiently communicate logical changes to other servers. This upgrade offers notable benefits in workload management, as it allows users to leverage standby servers for logical replication, relieving the primary servers of additional activity.
Additional server configurations: There are many other features added to this new release. These include support for Kerberos credential delegation, the ability for the SCRAM iteration count to be set with server variable scram_iterations, prevention of configuration file recursion beyond 10 levels etc.
Security advancements: In this release, there’s a notable advancement in security and access control mechanisms. It brings improved methods for securing access and introduces a more refined approach to various security features. Specifically, there’s enhanced management of the pg_hba.conf and pg_ident.conf files, which play a crucial role in client identification and authentication.

Follow the below steps to install and configure PostgreSQL 16 on Ubuntu 22.04|20.04|18.04.

1. Configure the PostgreSQL Repository

To be able to install PostgreSQL 16 on Ubuntu 22.04|20.04|18.04, we need to add the repository that provides the packages. Begin by updating the APT package index:

sudo apt update -y

Also, install the required packages:

sudo apt install gnupg2 wget vim -y

The PostgreSQL repository can then be added using the command:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Now download the key signing for the repository:

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

Update your package list:

sudo apt update -y

2. Install PostgreSQL 16 on Ubuntu

After adding the APT repository, installing PostgreSQL 16 is so easy. To achieve that, issue the below command:

sudo apt install postgresql-16

Dependency tree:

The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm10 libpq5
  libtypes-serialiser-perl postgresql-16 postgresql-client-16 postgresql-client-common
  postgresql-common sysstat
0 upgraded, 11 newly installed, 0 to remove and 435 not upgraded.
Need to get 35.8 MB of archives.
After this operation, 146 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

To install the latest available version, run:

sudo apt-get -y install postgresql postgresql-contrib

Once installed, start and enable the service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Check if the service is running:

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2023-09-16 09:44:21 EAT; 1min 7s ago
   Main PID: 4090 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4564)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Ful 16 09:44:21 thor-Standard-PC-i440FX-PIIX-1996 systemd[1]: Starting PostgreSQL RDBMS...
Ful 16 09:44:21 thor-Standard-PC-i440FX-PIIX-1996 systemd[1]: Finished PostgreSQL RDBMS.

Check the installed PostgreSQL version:

$ psql --version
psql (PostgreSQL) 16.1 (Ubuntu 16.1-1.pgdg20.04+1)

You can also check the version with the command:

$ sudo -u postgres psql -c "SELECT version();"
                                                             version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.1 (Ubuntu 16.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
(1 row)

3. Configure PostgreSQL 16

After the installation, you can proceed and configure PostgreSQL as desired. PostgreSQL supports several authentication methods. The default method is ident authentication. This authentication method associates the UNIX user with the Postgres role.

The other authentication methods are:

Password authentication – Connects to PostgreSQL using a password.
Trust authentication – allows a role to connect as long as conditions defined in the pg_hba.conf are met.
Peer authentication – This is similar to ident but only supported on local connections.

We will begin by configuring PostgreSQL 16 to allow remote connections. This can be done by editing the below file:

sudo vim /etc/postgresql/16/main/postgresql.conf

In the file, locate and modify the below line as shown:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

Once the changes have been made, save the file.

Proceed and allow password authentication using the command:

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf

Now change the identification method from peer to trust with the command:

sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf

Finally, allow PostgreSQL to be accessed remotely by editing the below lines:

$ sudo vim /etc/postgresql/16/main/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0           scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                md5

Save the made changes and restart the service:

sudo systemctl restart postgresql

If you have a firewall enabled, allow the PostgreSQL port through it:

sudo ufw allow 5432/tcp

4. Connect to PostgreSQL 16

There are two ways one can use to connect the PostgreSQL. These are:

Method 1: Connect to PosgreSQL directly:

$ sudo -u postgres psql
psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
Type "help" for help.

postgres=#

Method 2: Switching to the Postgres User:

sudo -i -u postgres

Then connect to the instance:

$ psql
psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
Type "help" for help.

postgres=#

You can connect to PostgreSQL remotely from the client using the command with the syntax:

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

For example:

psql 'postgres://[email protected]:5432/postgres?sslmode=disable'

5. Getting Started with PostgreSQL 16

After connecting to the database, there are several activities you can perform. In this guide, we will learn the basic operations. These include:

Securing the Admin user

We will start by setting a password for the default postgres user:

ALTER USER postgres PASSWORD 'Str0ngP@ssw0rd';

Replace the password with your own desired password string. You can then exit the shell:

postgres=# \q

Now test if the password is working:

$ psql -h localhost -U postgres
Password for user postgres: Enter_set_password
psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

Creating Users on PostgreSQL

In this guide, we will learn how to create a superuser with a password. To achieve that, run the command below, replacing the username and password where required:

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';

Verify the user creation:

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 admin     | Superuser, Create role, Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

Creating a Database on PostgreSQL

To create a test database, use the command:

CREATE DATABASE sampleDB;

You can also create a user with permissions to manage the database:

CREATE USER demo_user with encrypted password 'PassW0rd';
GRANT ALL PRIVILEGES ON DATABASE sampleDB to demo_user;

Verdict

That is the end of this detailed guide on how to install and configure PostgreSQL 16 on Ubuntu 22.04|20.04|18.04. I hope you benefitted from it.

See more: