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.
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
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.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)
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: Starting PostgreSQL RDBMS... Oct 25 07:26:42 debian systemd: 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:
- Password – allows a role to connect by providing a password.
- Ident – works over TCP/IP connections by obtaining the client’s operating system username with an optional username for mapping.
- Peer– works similar to ident but supports local connections only.
- 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;
Switch to the postgres user.
sudo -i -u postgres
Now access the PostgreSQL shell with the command.
[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.
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
$ 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
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';
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 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/[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, 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.
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:[email protected]:5432/postgres?sslmode=disable'
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!