How to Install PostgreSQL on Debian 12

Updated on November 21, 2023
How to Install PostgreSQL on Debian 12 header image

Introduction

PostgreSQL is an open-source relational database management system (RDBMS) that supports Structured Query Language (SQL). The database server is suitable for storing data and running queries in a wide variety of information systems. PostgreSQL's most notable performance features include multi-version concurrency control, transaction support, foreign keys, user-defined data types, and custom views.

This article explains how to install and enable access control for the PostgreSQL database server on Debian 12.

Prerequisites

Before you begin:

Install PostgreSQL

PostgreSQL is available in the default package repositories on Debian 12. Follow the steps below to install PostgreSQL using APT.

  1. Install postgresql-common package to create the latest PostgreSQL version information.

    console
    $ sudo apt install -y postgresql-common
    

    The above package creates a new repository information script (/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh) containing the latest PostgreSQL version details.

  2. Run the PostgreSQL repository setup script.

    console
    $ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
    

    Output:

    This script will enable the PostgreSQL APT repository on apt.postgresql.org on
    your system. The distribution codename used will be bookworm-pgdg.
    
    Press Enter to continue, or Ctrl-C to abort.

    Press Enter to add the repository when prompted.

  3. Update the package information index.

    console
    $ sudo apt update
    
  4. Display all the available PostgreSQL versions that you can install.

    console
    $ sudo apt-cache  policy postgresql
    

    Output:

    postgresql:
      Installed: (none)
      Candidate: 16+262.pgdg120+1
      Version table:
         16+262.pgdg120+1 500
            500 https://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages
         15+248 500
            500 https://deb.debian.org/debian bookworm/main amd64 Packages
            500 https://debian.mirror.constant.com bookworm/main amd64 Packages

    Based on the above output:

    • No PostgreSQL version is installed on the server.
    • PostgreSQL version 16 is available in the PostgreSQL repository.
    • PostgreSQL version 15 is available in the default Debian 12 repository.
  5. Install the PostgreSQL database server package.

    console
    $ sudo apt install postgresql -y
    

Manage the PostgreSQL System Service

The PostgreSQL database server runs under the postgresql system service. Follow the steps below to manage the PostgreSQL service using systemctl.

  1. Enable the PostgreSQL service to automatically start at boot.

    console
    $ sudo systemctl enable postgresql
    
  2. Start the PostgreSQL service.

    console
    $ sudo systemctl start postgresql
    
  3. View the PostgreSQL service status and verify that it's running.

    console
    $ sudo systemctl status postgresql
    

    Output:

    ā— postgresql.service - PostgreSQL RDBMS
         Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; preset: enabled)
         Active: active (exited) since Tue 2024-08-20 02:04:21 UTC; 12min ago
       Main PID: 1971 (code=exited, status=0/SUCCESS)
            CPU: 1ms
    
    Aug 20 02:04:21 postgresql-demo systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
    Aug 20 02:04:21 postgresql-demo systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.
  4. Stop the PostgreSQL service.

    console
    $ sudo systemctl stop postgresql
    
  5. Restart the PostgreSQL service.

    console
    $ sudo systemctl restart postgresql
    

Secure the PostgreSQL Server

By default, PostgreSQL creates an administrative postgres user. The user authenticates without a password using the Unix domain socket. Follow the steps below to enable password authentication to secure PostgreSQL:

  1. Access the PostgreSQL database console as the user postgres.

    console
    $ sudo -u postgres psql
    
  2. Modify the user to use password authentication. Replace Secur3Passw0rd with a strong password.

    sql
    postgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'Secur3Passw0rd';
    
  3. Exit the database console.

    sql
    postgres=# \q
    
  4. Open the /etc/postgresql/16/main/pg_hba.conf main PostgreSQL configuration file using a text editor like nano.

    console
    $ sudo nano /etc/postgresql/16/main/pg_hba.conf
    
  5. Find the following configuration directive.

    ini
    local    all    postgres    peer
    
  6. Edit the last column and change it from peer to scram-sha-256 to enable password authentication for the local postgres user.

    ini
    local    all    postgres    scram-sha-256
    
  7. Find the following directive for all PostgreSQL users.

    ini
    local    all    all    peer
    
  8. Replace peer with scram-sha-256 to enable password authentication.

    ini
    local    all    all    scram-sha-256
    

    Save and close the file.

  9. Restart PostgreSQL to apply the configuration changes.

    console
    $ sudo systemctl restart postgresql
    
  10. Access the PostgreSQL console as postgres.

    console
    $ psql -U postgres -W
    

    Enter the password you set earlier and press :key_enter when prompted.

  11. Create a new db_admin database role and grant LOGIN and CREATEDB privileges to allow the user to log in to the database server and create databases.

    sql
    postgres=# CREATE ROLE db_admin WITH LOGIN CREATEDB ENCRYPTED PASSWORD 'MyS3curePassWD!';
    

Access PostgreSQL

PostgreSQL includes the psql CLI utility you can use to access the database console by default. You can also use graphical tools such as pgAdmin to manage the PostgreSQL databases. Follow the steps below to access the PostgreSQL database server using the psql utility:

  1. Access the PostgreSQL database server as db_admin and enter the password you created earlier.

    console
    $ psql -U db_admin -d postgres -W
    
  2. Create a sample school database.

    sql
    postgres=> CREATE DATABASE school;
    
  3. Switch to the new database.

    sql
    postgres=> \c school;
    

    Enter db_admin role and press Enter when prompted.

    Output:

    You are now connected to database "school" as user "db_admin".
  4. Create a sample teachers table with 4 columns.

    sql
    school=> CREATE TABLE teachers (
                 staff_id SERIAL PRIMARY KEY,
                 first_name CHARACTER VARYING (100) NOT NULL,
                 last_name CHARACTER VARYING (100) NOT NULL,
                 joining_date DATE NOT NULL
             );
    

    The above command creates a new table with the following column specifications:

    • staff_id: Uses the SERIAL data type and a PRIMARY KEY constraint.
    • first_name and last_name: Use the CHARACTER VARYING data type to store variable length data with a limit of 100 characters and a NOT NULL constraint to restrict empty values.
    • joining_date: Uses the DATE data type to store date data.
  5. Add sample data to the teachers table.

    sql
    school=> INSERT INTO teachers (first_name, last_name, jioning_date)
                       VALUES
                       ('Emily', 'Pritt', '08-22-2024'),
                       ('Dennis', 'Jacobs', '08-13-2024');
    
  6. Query the teachers table to view the available records.

    sql
    school=> SELECT * FROM teachers;
    

    Output:

     staff_id | first_name | last_name | joining_date
    ----------+------------+-----------+--------------
            1 | Emily      | Pritt     | 2024-08-22
            2 | Dennis     | Jacobs    | 2024-08-13
    (2 rows)
  7. Exit the PostgreSQL console.

    sql
    school=> \q
    

Conclusion

You installed PostgreSQL on Debian 12, created a sample database, and used psql to access the database server. You can deploy PostgreSQL in your web applications to store data, set up a data warehouse for data analytics, or handle geographical data. For more information, visit the PostgreSQL documentation.