How To Install PostgreSQL on Debian 12

In this tutorial, we will show you how to install PostgreSQL on Debian 12. For those of you who didn’t know, PostgreSQL is an open-source relational database management system, that is widely used in modern web development due to its scalability, extensibility, and robustness. When it comes to choosing an operating system for PostgreSQL, Debian 12 Bookworm stands out as a stable and reliable option

This article assumes you have at least basic knowledge of Linux, know how to use the shell, and most importantly, you host your site on your own VPS. The installation is quite simple and assumes you are running in the root account, if not you may need to add ‘sudo‘ to the commands to get root privileges. I will show you the step-by-step installation of the PostgreSQL database on a Debian 12 (Bookworm).

Prerequisites

• A server running one of the following operating systems: Debian 12 (Bookworm).
• It’s recommended that you use a fresh OS install to prevent any potential issues.
• SSH access to the server (or just open Terminal if you’re on a desktop).
• An active internet connection. You’ll need an internet connection to download the necessary packages and dependencies for PostgreSQL.
• A non-root sudo user or access to the root user. We recommend acting as a non-root sudo user, however, as you can harm your system if you’re not careful when acting as the root.

Install PostgreSQL on Debian 12 Bookworm

Step 1. Before we install any software, it’s important to make sure your system is up to date by running the following apt commands in the terminal:

sudo apt update
sudo apt install apt-transport-https lsb-release ca-certificates curl dirmngr gnupg

This command will refresh the repository, allowing you to install the latest versions of software packages.

Step 2. Installing PostgreSQL on Debian 12.

With the environment prepared, let’s move on to the installation process. We’ll cover adding the PostgreSQL repository, importing the repository signing key, and finally installing PostgreSQL. To install the latest version of PostgreSQL, we need to add the official PostgreSQL repository to our Debian system. Execute the following commands:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Now that the repository is added and the signing key is imported, we can proceed with the installation of PostgreSQL. Execute the following command:

sudo apt update
sudo apt install postgresql

Step 3. Configuring PostgreSQL.

With PostgreSQL installed, we need to configure it properly to ensure optimal security and functionality. Let’s cover starting and stopping the PostgreSQL service, creating a PostgreSQL user and database, and configuring PostgreSQL security.

• Starting and Stopping the PostgreSQL Service:

To manage the PostgreSQL service, we need to know how to start, stop, and restart it. Use the following commands:

sudo systemctl start postgresql
sudo systemctl enable postgresql

• Creating a PostgreSQL User and Database:

Before utilizing PostgreSQL, we need to create a user and a database for our applications. Follow these commands to achieve that:

To access the PostgreSQL shell:

sudo -u postgres psql

To create a new user:

CREATE USER your_username WITH PASSWORD 'your_strong_password';

To create a new database:

CREATE DATABASE your_database_name;

• Configuring PostgreSQL Security:

Securing your PostgreSQL installation is of utmost importance. By configuring the authentication method and access control, you can ensure that only authorized users have access. The configuration file you need to modify is called pg_hba.conf. Use the following command to edit the file:

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

Replace <version> with the PostgreSQL version, you have installed (e.g., 12).

Within the pg_hba.conf file, you can specify the authentication method for different types of connections and define the access control rules.

After completing the installation and configuration, it’s crucial to verify that everything is working as expected. Let’s cover how to access the PostgreSQL shell and perform a basic connectivity test.

To access the PostgreSQL shell, use the following command:

sudo -u postgres psql

Congratulations! You have successfully installed PostgreSQL. Thanks for using this tutorial for installing the latest version of the PostgreSQL database on Debian 12 Bookworm. For additional help or useful information, we recommend you check the official PostgreSQL website.