How to install PostgreSQL 14 on Debian 11/10

In more than 30 years of existence and active development, PostgreSQL has won a good reputation in terms of robustness, reliability, and performance. PostgreSQL is a free and open source relational database management system. PostgreSQL 14 is the latest release of the world’s most advanced open source database.

The latest version of PostgreSQL 14 introduces a number of new features to help simplify the development and management of data-driven applications. It continues to make improvements in the area of ​​complex data types, including more accessible JSON access and support for discontinuous data ranges. PostgreSQL 14 has played an important role in the history of PostgreSQL’s high performance and distributed workloads, with improvements in logical replication, query parallelism, high write workloads, and connection concurrency.

Features of PostgreSQL 14

PostgreSQL 14 has the following features and enhancements:

  • Significantly improved performance through parallel queries, highly concurrent workloads, partitioned databases, logical replication, and cleanup.
  • OUT parameters can now be used to return data from stored procedures.
  • JSON convenience and multiple ranges-supports the representation of non-continuous data ranges.
  • The subscript operator has been added to the jsonb and hstore types.
  • The update of the B-tree index is managed more effectively, thereby reducing index bloat.
  • Support pipeline query Library, Which can significantly improve the throughput of high-latency connections.
  • Security enhancement

In this guide, we will systematically introduce how to install PostgreSQL 14 On Debian 11 | Debian 10.

Set up prerequisites

For this guide, please make sure you have the following:

  • Debian 11|Debian 10 server.
  • User and sudo Right to use.

Make sure your system is updated.

sudo apt update && sudo apt upgrade

Then go ahead 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 repository, but the available version is not the latest. Use the following command to check the available version:

sudo apt-cache search postgresql | grep postgresql

In this guide, we are interested in PostgreSQL 14 which is not available in the default repository. Therefore, we need to add a repository that provides packages.

sudo sh -c 'echo "deb https://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 following command.

sudo apt install postgresql-14

Verify your PostgreSQL 14 installation is as follows.

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

After the complete installation, PostgreSQL 14 will be initialized. Check the status of the service as follows.

$ 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 the PostgreSQL database

In PostgreSQL, there is a concept called Role Used for client authentication. PostgreSQL is set to use Authentication by default. This method associates roles with matching Linux system accounts. If it exists, you can log in.

After installation, a user account named postgres Is created and associated with the role postgres. With this user, you can log in to the PostgreSQL shell.

The multiple authentication methods supported by PostgreSQL include:

  1. password – Allow the role to connect by providing a password.
  2. identity – Work on TCP/IP connections by obtaining the client’s operating system user name and optional mapping user name.
  3. Peer– Similar to ident, but only supports local connections.
  4. Believe – It is defined in pg_hba.conf. As long as the conditions in the conf file are met, the user can connect without a password

There are two ways to connect to the PostgreSQL database;

method one:

Switch to postgres user.

sudo -i -u postgres

Now use the command to access the PostgreSQL shell.

psql

Sample output:

[email protected]:~$ psql
psql (14.0 (Debian 14.0-1.pgdg110+1))
Type "help" for help.

postgres=# 

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

Method Two:

This method does not need to switch to the postgres user, directly access the PostgreSQL shell sudo The command is as follows.

sudo -u postgres psql

Sample output:

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

postgres=# 

Step 3-Configure PostgreSQL 14 instance for remote access

In order to remotely access the PostgreSQL 14 instance, we need to modify the file in /etc/postgresql/14/main/pg_hba.conf

First, change the peer ID to trust, as shown 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

Access from anywhere is now allowed.

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

In the file, add the following line.

# 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* by editing the following file.

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

Edit the file as follows.

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

To apply the changes you have made, 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 super user for database management.

Connect to the PostgreSQL role.

sudo -u postgres psql

Create a super user, admin The password is password as follows.

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

Sample output:

psql (14.0 (Debian 14.0-1.pgdg110+1))
Type "help" for help.

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

Manage application users

Create a database and grant users permissions on the database.In this guide, we will create a database named test_db and a user with a password of test_user password as follows.

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 the 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/[email protected] <->
tcp   LISTEN 0      244             [::]:5432          [::]:*    uid:117 ino:30523 sk:e cgroup:/system.slice/system-postgresql.slice/[email protected] v6only:1 <->

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

sudo ufw allow 5432/tcp

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

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

for example, On another host, I will try to connect to my PostgreSQL14 instance using a super user account as shown below.

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

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

Sample output:

in conclusion

We have successfully walked through how to install PostgreSQL 14 on Debian 11 | Debian 10. In addition, we have also configured PostgreSQL 14 and accessed it through a remote host. Learning is enough!

see more:

  • How to use Ansible to manage PostgreSQL database
  • The best books for learning PostgreSQL database
  • Install PostgreSQL 13 Fedora
  • How to install PostgreSQL 13 on CentOS 7