How to Install PostgreSQL on Debian 12

Updated on September 11, 2024

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.

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.

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.

Update the package information index.
console
```
$ sudo apt update
```

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.

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.

Enable the PostgreSQL service to automatically start at boot.
console
```
$ sudo systemctl enable postgresql
```
Start the PostgreSQL service.
console
```
$ sudo systemctl start postgresql
```

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.

Stop the PostgreSQL service.
console
```
$ sudo systemctl stop postgresql
```
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:

Access the PostgreSQL database console as the user postgres.
console
```
$ sudo -u postgres psql
```
Modify the user to use password authentication. Replace Secur3Passw0rd with a strong password.
sql
```
postgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'Secur3Passw0rd';
```
Exit the database console.
sql
```
postgres=# \q
```
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
```
Find the following configuration directive.
ini
```
local    all    postgres    peer
```
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
```
Find the following directive for all PostgreSQL users.
ini
```
local    all    all    peer
```
Replace peer with scram-sha-256 to enable password authentication.
ini
```
local    all    all    scram-sha-256
```
Save and close the file.
Restart PostgreSQL to apply the configuration changes.
console
```
$ sudo systemctl restart postgresql
```
Access the PostgreSQL console as postgres.
console
```
$ psql -U postgres -W
```
Enter the password you set earlier and press :key_enter when prompted.
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:

Access the PostgreSQL database server as db_admin and enter the password you created earlier.
console
```
$ psql -U db_admin -d postgres -W
```
Create a sample school database.
sql
```
postgres=> CREATE DATABASE school;
```

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".

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.

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

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)

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.

How to Install PostgreSQL on Debian 12

Introduction

Prerequisites

Install PostgreSQL

Manage the PostgreSQL System Service

Secure the PostgreSQL Server

Access PostgreSQL

Conclusion

Products

Features

Marketplace

Resources

Company