How to Install PostgreSQL 14 on Debian 11

Install PostgreSQL 14 on Debian 11 and start seamlessly managing your data. Known for robustness, performance, and reliability, PostgreSQL 14 shelters super-efficient development features to implement data-driven applications.

With PostgreSQL, you’re looking at an open-source database management system that emphasizes extensibility. This article will uncover the best possible way to prepare and install PostgreSQL 14 on your system.

Pre Requisites:

To install PostgreSQL 14 on Debian 11, ensure you’ve access to the following:

  • A fully stable and operational Debian 11 Server.
  • User with sudo privileges.

How to Install PostgreSQL 14 on Debian 11

Considering that you’ve all the resources ready, let’s now learn how to install PostgreSQL 14 on Debian 11.

Step 1: Update Your System

Before installing any utility, it is always a good idea to verify whether or not you’re using the latest system repositories and the scenes are no different with PostgreSQL 14. Doing this is pretty simple.

Launch the Terminal by hitting “Ctrl+Alt+T”.

Run the following command:

$ sudo apt update && sudo apt upgrade
update your system repository

Step 2: Install the Required Packages

After verifying your system repositories, it is time to install all the required packages. For this, invoke the apt command alongside the wget flag in the following way:

$ sudo apt -y install gnupg2 wget vim

Step 3: Install PostgreSQL 14 on Debian 11

Until this point, we’ve been preparing our system, ensuring that the installation process concludes without any issue. You’re now ready to install PostgreSQL on Debian 11.

In case you don’t know, the PostgreSQL utility is already available in Debian’s default repositories. However, the version is sadly not the latest one. Don’t worry; here, I’ll guide you in getting the PostgreSQL 14 by adding a repository.

But before that, make sure to check the version available on the default repository simply invoking using the command:

$ sudo apt-cache search postgresql | grep postgresql
Install PostgreSQL 14 on Debian 11

Are you done already? Well then, let’s add the required repository.

Launch the Terminal

Run the following command with -c flag:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
add required repository and Install PostgreSQL 14 on Debian 11

Step 4: Importing GPG Key

Next up is importing the GPG key for the recently added repository. You can do that by employing the wget command alongside --quiet and -O flag in the following way:

$ wget --quiet -O - [Link] | sudo apt-key add -
import gpg key

Step 5: Updating APT Package Index

To update your APT package index, all you need to do is run the following command:

$ sudo apt -y update
update apt package index and Install PostgreSQL 14 on Debian 11

Finally, use the command below and install PostgreSQL 14 on Debian 11

$ sudo apt install postgresql-14
Install PostgreSQL 14 on Debian 11

Verifying PostgreSQL 14 Installation and Checking the Service Status

After you’re done with the entire installation process, it is crucial to verify if everything went well. For that, launch the Terminal and invoke the following command:

Input:

$ sudo -u postgres psql -c "SELECT version();"
verifying postgreSQL 14 installation

Probable Output: 

PostgreSQL [Version Number] (Debian 14.0-1.pgdg110+1) on [system-information], compiled by [service provider](Debian 10.2.1-6) 10.2.1 20210110, 64-bit

With you’ve successfully installed PostgreSQL 14 on Debian 11. The PostgreSQL 14 will the initiate, and you can verify it by checking the status of the service using the following command:

$ systemctl status postgresql

Output:

postgresql.service - PostgreSQL RDBMS
Loaded: loaded ([directory]; enabled; vendor preset: enabled)
     Active: active (exited) since [Date Time], [Duration] ago
    Process: 3811 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 3811 (code=exited, status=0/SUCCESS)
        CPU: [FPS]

[Date Time] debian systemd[1]: Starting PostgreSQL RDBMS...
[Date Time] debian systemd[1]: Finished PostgreSQL RDBMS.

Securing PostgreSQL Database

As you’re about to start utilizing PostgreSQL, it is crucial to be aware of a concept called roles. It is used to carry out client authentication. The PostgreSQL, by default, is set to use the ident authentication, which operates in a manner that associates the roles with that of Linux system accounts that seems similar.

After installing PostgreSQL 14 on Debian 11, it creates a user account that goes by the name postgres. The same is associated with the role of postgres, which I’ve already discussed. This is the user who enables one to log in inside the PostgreSQL shell.

Securing PostgreSQL corresponds to multiple methods. Let’s quickly have a look at each of them:

Password – It allows users to connect by simply providing the system with a password.

Ident – Indent works entirely over the TCP/IP connections. It operates by obtaining the concerned client’s username (associated with the operating system) alongside another username, which is optional for mapping.

Peer– Almost similar to how the ident works, but the only difference is that it only supports local connections.

Trust – Trust is a conditional method. Here a user can seamlessly connect without any password, provided the conditions within the conf file are met. The conditions are defined on the pg_hba.conf.

Connecting PostgreSQL Database

You can connect to the PostgreSQL database in two ways.

Method 1: Switching to the postgres User

For switching to the postgres user and start connecting to the PostgreSQL database, run the following command with -i and -u flag:

$ sudo -i -u postgres
switching to postgres user

After that, you can access the PostgreSQL shell with the psql command.

$ psql
access postgresql

Method 2: Accessing the PostgreSQL Shell Directly

Those who don’t want to switch to the concerned postgres user can use a command and access the PostgreSQL shell directly. 

Launch the Terminal

Run the following command with the -u flag:

$ sudo -u postgres psql
accessing postgreSQL directly

Configuring PostgreSQL 14 for Remote Access

In order to configure the PostgreSQL 14 in a manner that provides remote access, we’ll need to modify the file located at /etc/postgresql/14/main/pg_hba.conf

Talking about modifications, the first thing to do is alter the peer identification to the trust. You can do that by running the following command:

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

Once done, you can allow the password login as:

$ 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

Add the following lines to the file:

Install PostgreSQL 14 on Debian 11

Finally, allow PostgreSQL to listen to * when it is invoked by running the following command:

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

Required Edits:

# CONNECTIONS AND AUTHENTICATION
listen_addresses='*'

Restart PostgreSQL and ensure all the changes are successfully applied.

$ sudo systemctl restart postgresql
$ sudo systemctl enable postgresql

That’s pretty much it. In this guide, you’ve learned how to install PostgreSQL 14 on Debian 11 and configure it the proper way. I’ve walked you through easy-to-understand steps that will help get the job done with minimal effort.

If this guide helped you, please share it.

Leave a Reply
Related Posts