Learn how to – How To Install PostgreSQL 14 on Debian 11/10

Learn how to – How To Install PostgreSQL 14 on Debian 11/10.

For over 30 years of existence and active development, PostgreSQL has earned a strong reputation for robustness, reliability, and performance. PostgreSQL is a free and open-source relational database management system. PostgreSQL 14 is the latest release version of this world’s most advanced open-source database.

The latest version, PostgreSQL 14 introduces several new features that help ease developments and administration in implementing data-driven applications. It continues to make improvements in the sector of complex data types, including more accessible JSON access and the support for non-contiguous data ranges. PostgreSQL 14 has played a big role in PostgreSQL’s history of high performance and distributed workloads with improvements in logical replication, query parallelism, high-write workloads, connection concurrency.

Features of PostgreSQL 14

PostgreSQL 14 comes with the below features and enhancements:

• Significant performance increases through parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming.
• OUT parameters can now be used to return data from stored procedures.
• JSON Conveniences and multiranges- enabling the representation of non-contiguous data ranges.
• Subscripting operators have been added to the jsonb and hstore types.
• Updates to B-tree indexes are more efficiently managed, resulting in less index bloat.
• Supports pipelined queries through libpq, which can significantly increase throughput over high-latency connections.
• Security enhancements

In this guide, we will systematically go through how to install PostgreSQL 14 on Debian 11 | Debian 10.

Setup Pre-requisites

For this guide, ensure that you have the following:

• Debian 11|Debian 10 Server.
• User with sudo access.

Ensure that your system is updated.

sudo apt update && sudo apt upgrade -y

Then proceed and install the required packages.

sudo apt -y install gnupg2 wget vim

Step 1 – Install PostgreSQL 14 on Debian 11 | Debian 10

PostgreSQL is available in the default Debian repositories but the available versions are not up to date. Check the available versions using the command:

sudo apt-cache search postgresql | grep postgresql

In this guide, we are interested in PostgreSQL 14 which is not provided in the default repositories. Therefore, we are required to add a repository that provides the package.

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 GPG key for the added repository.

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

Next, update your APT package index.

sudo apt -y update

Now install PostgreSQL 14 on Debian 11/Debian 10 using the command below.

sudo apt install postgresql-14

Verify your PostgreSQL 14 installation as below.

$ sudo -u postgres psql -c "SELECT version();"
                                                           version                                                           
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.8 (Debian 14.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

With a complete installation, PostgreSQL 14 will be initialized. Check the status of the service as below.

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2021-10-25 07:26:42 EDT; 1min 10s ago
    Process: 3811 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 3811 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Oct 25 07:26:42 debian systemd[1]: Starting PostgreSQL RDBMS...
Oct 25 07:26:42 debian systemd[1]: Finished PostgreSQL RDBMS.

Step 2 – Connect and Secure PostgreSQL database

In PostgreSQL, there is a concept known as roles used for client authentication. PostgreSQL is set to use ident authentication by default. This method associates roles with matching Linux system accounts. If it exists, then you are able to log in.

After the installation, a user account with the name postgres is created and associated with the role postgres. With this user, one is able to log in to the PostgreSQL shell.

Amon the multiple authentication methods supported by PostgreSQL include:

1. Password – allows a role to connect by providing a password.
2. Ident – works over TCP/IP connections by obtaining the client’s operating system username with an optional username for mapping.
3. Peer– works similar to ident but supports local connections only.
4. Trust – it uses conditions defined on the pg_hba.conf. A user can connect without password as long as conditions in the conf file are met

There are two ways of connecting to the PostgreSQL database;

Method 1:

Switch to the postgres user.

sudo -i -u postgres

Now access the PostgreSQL shell with the command.

psql

Sample Output:

postgres@debian:~$ psql
psql (14.8 (Debian 14.8-1.pgdg110+1))
Type "help" for help.

postgres=# 

Now in the shell, you can now manage your database.

Method 2:

In this method, you do not need to switch to the postgres user, you can directly access the PostgreSQL shell with the sudo command below.

sudo -u postgres psql

Sample Output:

$ sudo -u postgres psql
psql (14.8 (Debian 14.8-1.pgdg110+1))
Type "help" for help.

postgres=# 

Step 3 – Configure PostgreSQL 14 Instance for Remote Access

For the PostgreSQL 14 instance to be accessed remotely, we need to modify the file at /etc/postgresql/14/main/pg_hba.conf

First, change the peer identification to trust as below.

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

Then, allow password login.

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

Now allow access from everywhere.

sudo vim /etc/postgresql/14/main/pg_hba.conf

In the file, add the lines below.

# Database administrative login by Unix domain socket
local   all             postgres                                trust
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/24              md5
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               md5

Now allow PostgreSQL to listen on * by editing the below file.

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

Edit the file as below.

# CONNECTIONS AND AUTHENTICATION
........
listen_addresses='*'

For the changes made to apply, you need to restart PosgreSQL.

sudo systemctl restart postgresql
sudo systemctl enable postgresql

Step 4 – User Management in PostgreSQL Database

Now that we have configured everything, let’s create a superuser for database management.

Connect to the PostgreSQL role.

sudo -u postgres psql

Create a superuser, admin with password as Passw0rd as below.

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

Sample Output:

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

Manage application users

Create a database and grant user privileges to the database. In this guide, we will create a database with the name, test_db and a user test_user with password as dbpassword as below.

create database test_db;
create user test_user with encrypted password 'dbpassword';
grant all privileges on database test_db to test_user;
q

Connect to the instance from remote host.

Verify that the service is running:

$ ss -tunelp | grep 5432
tcp   LISTEN 0      244          0.0.0.0:5432       0.0.0.0:*    uid:117 ino:30522 sk:d cgroup:/system.slice/system-postgresql.slice/postgresql@14-main.service <->
tcp   LISTEN 0      244             [::]:5432          [::]:*    uid:117 ino:30523 sk:e cgroup:/system.slice/system-postgresql.slice/postgresql@14-main.service v6only:1 <->

If you are using ufw, allow port 5432 through the firewall as below.

sudo ufw allow 5432/tcp

Now we want to connect to the PostgreSQL 14 instance from a remote host. The syntax is as below.

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

For example, on another host, I’ll try and connect to my PostgreSQL14 instance using the superuser account as below.

First, install PostgreSQL 14 on the remote host and proceed to access your PostgreSQL14 instance.

psql 'postgres://admin:Passw0rd@192.168.205.4:5432/postgres?sslmode=disable'

Sample Output:

Recommended books:

• Best Books To Learn PostgreSQL Databases

Conclusion

We have successfully walked through how to install PostgreSQL 14 on Debian 11 | Debian 10. In addition to that, we have made configurations to PostgreSQL 14 and also accessed it via a remote host. That was enough learning!

See more:

• How To Manage PostgreSQL Database with Ansible
• Best Books To Learn PostgreSQL Database
• Install PostgreSQL 13 on Fedora
• How To Install PostgreSQL 13 on CentOS 7