How to Install PostgreSQL on Debian 12

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:

• Deploy a Debian 12 Cloud Compute instance on Vultr.
• Access the instance using SSH.
• Create a non-root user with sudo privileges and switch to the user.
• Update the instance.

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

   Copy

   $ sudo apt install -y postgresql-common

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   $ sudo apt update

   Explain Code

   Powered by Vultr AgentBeta

4. Display all the available PostgreSQL versions that you can install.

   CONSOLE

   Copy

   $ sudo apt-cache  policy postgresql

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   $ sudo apt install postgresql -y

   Explain Code

   Powered by Vultr AgentBeta

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

   Copy

   $ sudo systemctl enable postgresql

   Explain Code

   Powered by Vultr AgentBeta

2. Start the PostgreSQL service.

   CONSOLE

   Copy

   $ sudo systemctl start postgresql

   Explain Code

   Powered by Vultr AgentBeta

3. View the PostgreSQL service status and verify that it's running.

   CONSOLE

   Copy

   $ sudo systemctl status postgresql

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   $ sudo systemctl stop postgresql

   Explain Code

   Powered by Vultr AgentBeta

5. Restart the PostgreSQL service.

   CONSOLE

   Copy

   $ sudo systemctl restart postgresql

   Explain Code

   Powered by Vultr AgentBeta

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

   Copy

   $ sudo -u postgres psql

   Explain Code

   Powered by Vultr AgentBeta

2. Modify the user to use password authentication. Replace Secur3Passw0rd with a strong password.

   SQL

   Copy

   postgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'Secur3Passw0rd';

   Explain Code

   Powered by Vultr AgentBeta

3. Exit the database console.

   SQL

   Copy

   postgres=# \q

   Explain Code

   Powered by Vultr AgentBeta

4. Open the /etc/postgresql/16/main/pg_hba.conf main PostgreSQL configuration file using a text editor like nano.

   CONSOLE

   Copy

   $ sudo nano /etc/postgresql/16/main/pg_hba.conf

   Explain Code

   Powered by Vultr AgentBeta

5. Find the following configuration directive.

   INI

   Copy

   local    all    postgres    peer

   Explain Code

   Powered by Vultr AgentBeta

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

   INI

   Copy

   local    all    postgres    scram-sha-256

   Explain Code

   Powered by Vultr AgentBeta

7. Find the following directive for all PostgreSQL users.

   INI

   Copy

   local    all    all    peer

   Explain Code

   Powered by Vultr AgentBeta

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

   INI

   Copy

   local    all    all    scram-sha-256

   Explain Code

   Powered by Vultr AgentBeta

   Save and close the file.

9. Restart PostgreSQL to apply the configuration changes.

   CONSOLE

   Copy

   $ sudo systemctl restart postgresql

   Explain Code

   Powered by Vultr AgentBeta

10. Access the PostgreSQL console as postgres.

    CONSOLE

    Copy

    $ psql -U postgres -W

    Explain Code

    Powered by Vultr AgentBeta

    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

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

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

   Copy

   $ psql -U db_admin -d postgres -W

   Explain Code

   Powered by Vultr AgentBeta

2. Create a sample school database.

   SQL

   Copy

   postgres=> CREATE DATABASE school;

   Explain Code

   Powered by Vultr AgentBeta

3. Switch to the new database.

   SQL

   Copy

   postgres=> \c school;

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   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
            );

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   school=> INSERT INTO teachers (first_name, last_name, jioning_date)
                      VALUES
                      ('Emily', 'Pritt', '08-22-2024'),
                      ('Dennis', 'Jacobs', '08-13-2024');

   Explain Code

   Powered by Vultr AgentBeta

6. Query the teachers table to view the available records.

   SQL

   Copy

   school=> SELECT * FROM teachers;

   Explain Code

   Powered by Vultr AgentBeta

   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

   Copy

   school=> \q

   Explain Code

   Powered by Vultr AgentBeta

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.