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
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
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'
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 -
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
Finally, use the command below and install PostgreSQL 14 on Debian 11
$ sudo apt install postgresql-14
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();"
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
After that, you can access the PostgreSQL shell with the psql command.
$ psql
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
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:
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.
