Install PostgreSQL on Debian 12 Bookworm: Powerful Database Engine

This guide will walk you through the process of installing PostgreSQL on Debian 12 Bookworm. PostgreSQL is a robust, open-source, and object-relational database management system (ORDBMS) known for its reliability and feature set. Orcacore is dedicated to providing clear and concise instructions for server administration, and this article will guide you through the steps to install and configure PostgreSQL on Debian 12 Bookworm.

What is the Default PostgreSQL Version on Debian 12?

Debian 12 Bookworm comes with updated packages. The default version of PostgreSQL available in the standard Debian 12 repository is PostgreSQL 15, which is currently the stable release. Let’s dive into the installation process to get PostgreSQL on Debian 12 Bookworm.

To successfully install PostgreSQL on Debian 12 Bookworm, you’ll need a user account with sudo privileges on your server. If you haven’t already configured this, you can refer to our guide on Initial Server Setup with Debian 12 Bookworm.

Now, let’s proceed with the following steps to install PostgreSQL on your Debian 12 Bookworm system.

Step 1 – Installing PostgreSQL 15 on Debian 12 from the Command Line

First, ensure your system’s package lists are up-to-date by running the following command:

sudo apt update

Since Debian 12 includes the current stable version of PostgreSQL, you can install it directly using the following command:

sudo apt -y install postgresql

PostgreSQL is automatically enabled during the installation process. Verify its status with the following command:

sudo systemctl status postgresql

The output should resemble this:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2023-10-27 14:30:00 UTC; 10s ago
       Docs: man:postgres(1)
   Main PID: 1234 (postgres)
      Tasks: 6 (limit: 4915)
     Memory: 15.5M
        CPU: 100ms
     CGroup: /system.slice/postgresql.service
             └─1234 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf

Note: If the PostgreSQL service isn’t active, enable it using:

sudo systemctl enable postgresql --now

Step 2 – Manage PostgreSQL Service from CLI

The PostgreSQL database server operates as a service named "postgresql," managed via systemd. Here are some common commands:

To start the PostgreSQL server:

sudo systemctl start postgresql

To stop the PostgreSQL server:

sudo systemctl stop postgresql

To restart the PostgreSQL server:

sudo systemctl restart postgresql

To reload the PostgreSQL server:

sudo systemctl reload postgresql

To check the PostgreSQL status:

sudo systemctl status postgresql

Step 3 – Configure and Use PostgreSQL on Debian 12

By default, PostgreSQL uses "ident" authentication, associating Postgres roles with matching Unix/Linux system accounts.

Switch to the "postgres" account:

su postgres

Output
postgres@olivia:~$

Access the Postgres shell:

postgres@olivia:~$ psql

Output
psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.

postgres=#

Exit the PostgreSQL shell:

postgres=# q

Output
postgres@olivia:~$

Return to your regular system user:

postgres@olivia:~$ exit

Create a new PostgreSQL Role

You can create a new Postgres role in two ways.

If logged in as the Postgres account:

postgres@olivia:~$ createuser --interactive

You’ll be prompted for the role name and superuser status:

Output
Enter name of role to add: orca
Shall the new role be a superuser? (y/n) y

Create a new PostgreSQL Database

For any role to log in, it needs a database with the same name.

If logged in as the Postgres account, create the database:

postgres@olivia:~$ createdb orca

Open the Postgres Shell with the new Role

Create a Linux user with the same name as your Postgres role and database:

sudo adduser orca

Connect to the Postgres database:

# su - orca
# orca@:~$ psql

Output
psql (15.3 (Debian 15.3-0+deb12u1))
Type "help" for help.

orca=#

Check your connection information:

orca=# conninfo

Output
You are connected to database "orca" as user "orca" via socket in "/var/run/postgresql" at port "5432".

For further information, consult the PostgreSQL Documentation.

Conclusion

This guide has demonstrated how to install, configure, and use PostgreSQL 15 on Debian 12 Bookworm. With these steps, you can successfully deploy and manage PostgreSQL on Debian 12 Bookworm.

Here are some additional articles you might find useful:

Install LAMP Stack on Debian 12

Check HTTPS Port 443 is Open on Linux

Alternative Installation Methods for PostgreSQL on Debian 12

While the above method utilizes the standard Debian repositories, there are alternative approaches for installing and managing PostgreSQL. Two such methods are detailed below: using Docker and using a PostgreSQL APT repository directly from the PostgreSQL project.

1. Installing PostgreSQL using Docker

Docker provides a containerized environment, allowing you to run PostgreSQL in isolation from your host system. This can be particularly useful for development and testing environments, or when you need to manage multiple PostgreSQL versions concurrently.

Explanation:

Docker containers encapsulate an application and its dependencies, ensuring consistency across different environments. Using Docker to install PostgreSQL involves pulling a pre-built PostgreSQL image from Docker Hub, configuring a container, and running it. This method simplifies dependency management and offers increased portability.

Steps:

1. Install Docker: If you don’t have Docker installed, follow the official Docker documentation to install Docker Engine on your Debian 12 system.

   sudo apt update
   sudo apt install docker.io
   sudo systemctl start docker
   sudo systemctl enable docker

2. Pull the PostgreSQL Image: Pull the official PostgreSQL image from Docker Hub. Specify the version you desire (e.g., 15 for PostgreSQL 15).

   sudo docker pull postgres:15

3. Create a Docker Volume (Optional): Create a Docker volume to persist your database data across container restarts.

   sudo docker volume create postgres_data

4. Run the PostgreSQL Container: Run the PostgreSQL container with appropriate environment variables for configuration. Replace your_password with a strong password.

   sudo docker run --name postgresql_container -v postgres_data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=your_password -p 5432:5432 -d postgres:15

   • --name postgresql_container: Assigns a name to the container.
   • -v postgres_data:/var/lib/postgresql/data: Mounts the Docker volume to the PostgreSQL data directory.
   • -e POSTGRES_PASSWORD=your_password: Sets the PostgreSQL superuser password.
   • -p 5432:5432: Maps port 5432 on the host to port 5432 in the container.
   • -d: Runs the container in detached mode (background).

5. Access PostgreSQL: You can now access the PostgreSQL server running in the Docker container using psql or any other PostgreSQL client.

   sudo docker exec -it postgresql_container psql -U postgres

   This command executes psql within the container as the postgres user.

2. Installing PostgreSQL from the Official PostgreSQL APT Repository

The PostgreSQL project maintains its own APT repository, which provides access to the latest PostgreSQL releases and security updates, often sooner than the Debian repositories. This is a good option if you need a more recent version or want to ensure you receive updates directly from the source.

Explanation:

This method involves adding the PostgreSQL APT repository to your system’s package sources, importing the repository signing key, and then installing PostgreSQL using apt. This provides more direct access to PostgreSQL releases.

Steps:

1. Add the PostgreSQL APT Repository: Import the repository signing key and add the repository to your system’s sources.list.

   sudo apt install wget ca-certificates
   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   echo "deb http://apt.postgresql.org/pub/repos/apt/ bookworm-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

2. Update Package Lists: Update your system’s package lists to include the new repository.

   sudo apt update

3. Install PostgreSQL: Install the desired PostgreSQL version (e.g., postgresql-16 for PostgreSQL 16).

   sudo apt install postgresql-16 postgresql-client-16

   This command installs both the server and client components for PostgreSQL 16.

4. Verify Installation: Verify the installation by checking the PostgreSQL service status.

   sudo systemctl status postgresql

Code Example for Connecting to the Database:

Regardless of the installation method (APT repository or Docker), connecting to the PostgreSQL database programmatically requires using a PostgreSQL client library for your chosen programming language. Here’s a Python example using the psycopg2 library:

import psycopg2

try:
    # Connection parameters (adjust as needed)
    conn = psycopg2.connect(
        host="localhost",  # Or the Docker container's IP address
        database="your_database",
        user="your_user",
        password="your_password"
    )

    cur = conn.cursor()

    # Execute a query
    cur.execute("SELECT version();")

    # Fetch the result
    db_version = cur.fetchone()
    print(f"PostgreSQL version: {db_version[0]}")

    # Close the cursor and connection
    cur.close()
    conn.close()

except psycopg2.Error as e:
    print(f"Error connecting to PostgreSQL: {e}")

Explanation:

• This code imports the psycopg2 library, a popular PostgreSQL adapter for Python.
• It establishes a connection to the PostgreSQL database using connection parameters such as host, database name, username, and password.
• It creates a cursor object, which allows you to execute SQL queries.
• It executes a simple query to retrieve the PostgreSQL version.
• It fetches the result and prints it to the console.
• Finally, it closes the cursor and connection to release resources.

Remember to replace "your_database", "your_user", and "your_password" with your actual database credentials.

These alternative methods offer flexibility in how you install and manage PostgreSQL on your Debian 12 Bookworm system, catering to different needs and preferences.