How to Install and Configure PostgreSQL on Debian 12 Bookworm

PostgreSQL, often referred to as “Postgres”, is a powerful, open-source object-relational database system known for its robustness, extensibility, and SQL compliance. Whether you are building small applications or complex, data-intensive enterprise systems, PostgreSQL provides the reliability and performance needed.

In this article, we’ll walk through the steps to install, configure, and perform some basic setup tasks for PostgreSQL on a Debian 12 Bookworm system. This guide is suitable for beginners and intermediate users who are new to PostgreSQL or the Debian ecosystem.

1. Prerequisites

Before we begin, make sure your system meets the following requirements:

• A running Debian 12 “Bookworm” installation
• A user with sudo privileges
• Internet access to install packages
• A basic understanding of Linux command-line operations

2. Installing PostgreSQL on Debian 12

PostgreSQL is available in the default Debian 12 repositories. To install it, follow these steps:

Step 1: Update the System

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y

The postgresql-contrib package includes additional utilities and tools that are commonly used.

Step 3: Verify Installation

After installation, PostgreSQL should start automatically. You can check the status with:

sudo systemctl status postgresql

3. PostgreSQL Service Management

Here are common commands for managing the PostgreSQL service:

• Start PostgreSQL:

  sudo systemctl start postgresql
  

• Enable on boot:

  sudo systemctl enable postgresql
  

• Restart the service:

  sudo systemctl restart postgresql
  

• Check status:

  sudo systemctl status postgresql
  

4. Understanding PostgreSQL Roles and Databases

PostgreSQL uses a role-based access control system. A role can act as a user, a group, or both.

By default, PostgreSQL creates a postgres Linux user and a corresponding PostgreSQL role. To interact with the PostgreSQL shell:

sudo -i -u postgres
psql

You will be inside the psql shell, denoted by:

postgres=#

Type \q to quit.

5. Creating a New Role and Database

It’s a best practice not to use the postgres user for application operations. Instead, create a dedicated role and database.

Step 1: Create a Role

sudo -u postgres createuser --interactive

You’ll be prompted to enter a name and specify if the role should be a superuser.

Alternatively:

sudo -u postgres createuser myuser --pwprompt

Step 2: Create a Database

sudo -u postgres createdb mydb -O myuser

This creates a database named mydb owned by myuser.

6. Configuring PostgreSQL for Remote Access

By default, PostgreSQL only listens on the localhost interface for security.

Step 1: Edit postgresql.conf

sudo nano /etc/postgresql/15/main/postgresql.conf

Find and modify the following line:

listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

Step 2: Edit pg_hba.conf

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add a line like:

host    all             all             192.168.1.0/24         md5

Adjust the IP range according to your network.

Step 3: Restart PostgreSQL

sudo systemctl restart postgresql

Now PostgreSQL will accept connections from allowed IP addresses.

7. Basic Security Configuration

To enhance PostgreSQL security:

Enforce Password Authentication

Ensure your users are created with passwords and that md5 authentication is used in pg_hba.conf.

Disable Remote Root Access

Never allow the postgres superuser to connect remotely. Use roles with limited permissions for applications.

Use Firewalls

Use ufw or iptables to limit access to port 5432 (PostgreSQL’s default port).

Example using ufw:

sudo ufw allow from 192.168.1.0/24 to any port 5432

8. PostgreSQL Configuration Files Overview

All major config files are located in:

/etc/postgresql/15/main/

• postgresql.conf – Main configuration file (logging, memory, networking)
• pg_hba.conf – Client authentication rules
• pg_ident.conf – User mapping between system users and PostgreSQL roles

Changes to these files typically require a restart.

9. Enabling Logging for PostgreSQL

Logging is important for diagnosing issues and auditing access.

Edit postgresql.conf:

sudo nano /etc/postgresql/15/main/postgresql.conf

Enable and set:

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'all'

Then restart:

sudo systemctl restart postgresql

Logs will appear under:

/var/log/postgresql/

10. Backing Up and Restoring PostgreSQL Databases

PostgreSQL provides built-in tools for backup and restoration.

Backup with pg_dump

pg_dump -U myuser -W -F t mydb > mydb.tar

Restore with pg_restore

pg_restore -U myuser -W -d mydb mydb.tar

Alternatively, use:

pg_dumpall -U postgres > all_databases.sql
psql -U postgres -f all_databases.sql

11. Conclusion

PostgreSQL is a mature and feature-rich database management system suitable for a wide variety of applications. On Debian 12 Bookworm, the installation and configuration process is straightforward, but it’s important to apply best practices—especially in the areas of role management, remote access control, and logging.

Once installed and secured, PostgreSQL provides a stable and scalable backend for your web applications, data analysis tools, or enterprise workloads.

You can explore more advanced features such as:

• Table partitioning
• Logical replication
• Full-text search
• PostgreSQL extensions (e.g., PostGIS, pg_stat_statements)

For more information, consult the official PostgreSQL documentation at https://www.postgresql.org/docs/.