Install PostgreSQL on Debian 12 Bookworm

In this guide, we want to teach you to Install PostgreSQL on Debian 12 Bookworm.

As you know, PostgreSQL is a popular, open-source, and object-oriented database management system. Here we want to show you how to install and use PostgreSQL on the newly released Debian 12 Bookworm distribution.

What is the Default PostgreSQL Version on Debian 12?

Debian 12 Bookworm ships with the new packages and software. The default version of PostgreSQL that is available in the default Debian 12 repository is PostgreSQL 15 which is the current stable version.

Learn To Install and Use PostgreSQL on Debian 12 Bookworm

To complete this guide, you must have access to your server as a non-root user with sudo privileges. To do this, you can follow this guide on Initial Server Setup with Debian 12 Bookworm.

Now follow the steps below to complete your PostgreSQL installation on Debian 12.

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

First, you must run the system update by using the following command:

sudo apt update

As Debian 12 ships with the current stable version of PostgreSQL, you can easily run the command below to install it on your server:

sudo apt -y install postgresql

By default, PostgreSQL will be activated during the installation. To confirm this, run the following command:

sudo systemctl status postgresql

In your output, you should see:

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; preset: e>
     Active: active (exited) since Thu 2023-06-15 05:20:15 EDT; 13s ago
   Main PID: 2202 (code=exited, status=0/SUCCESS)
        CPU: 2ms
...

Note: If your PostgreSQL service is not activated on Debian 12, run the command below to enable it:

sudo systemctl enable postgresql --now

Step 2 – Manage PostgreSQL Service from CLI

As you know, the PostgreSQL database server runs as a service under the name “PostgreSQL,” which can be managed with the systemd with the following command examples.

To start the PostgreSQL server, you can use the command below:

sudo systemctl start postgresql

You can stop the PostgreSQL server by running the command below:

sudo systemctl stop postgresql

To restart the PostgreSQL server, you can use the following command:

sudo systemctl restart postgresql

Reload the PostgreSQL server by running the command below:

sudo systemctl reload postgresql

Also, you can check your PostgreSQL status on Debian 12 by using the following command:

sudo systemctl status postgresql

Step 3 – Configure and Use PostgreSQL on Debian 12

During the PostgreSQL installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account.

One way is to switch over to the Postgres account on your Debian 12 server by running the following command:

su postgres

Output
postgres@olivia:~$

You can access the Postgres shell on Debian 12 by running the following command:

postgres@olivia:~$ psql

With this command, you will see that your shell prompt changes to:

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

postgres=#

To exit from the PostgreSQL shell on Debian 12, run the following command:

postgres=# \q

With this command, you will be back to the Postgres Linux command prompt:

postgres@olivia:~$

You can type exit to return to the regular system user.

postgres@olivia:~$ exit

Create a new PostgreSQL Role

At this point, you can create a new Postgres role in two ways.

First, if you are logged in as a Postgres account, you can use the following command:

postgres@olivia:~$ createuser --interactive

You will be asked to enter your role name and whether the new role is a superuser or not.

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

Create a new PostgreSQL Database

The Postgres authentication system for any role used to log in, the role should have a database with the same name that it can access.

If you logged in as the Postgres account, run the following command to create the database with the name of the role you have created in the previous step:

postgres@olivia:~$ createdb orca

Open the Postgres Shell with the new Role

At this point, you need to create a Linux user with the same name as your Postgres role and database on Debian 12 Bookworm. To do this, run the following command:

sudo adduser orca

Now you can connect to the Postgres database with the following commands:

# su - orca
# orca@:~$ psql

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

orca=#

When you have logged in to your database, you can check your current connection information by running the following command:

orca=# \conninfo

In your output you will see:

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

For more information, you can visit the PostgreSQL Documentation page.

Conclusion

As you saw, the default version of PostgreSQL that is available in the default Debian 12 Bookworm repository is PostgreSQL 15 LTS. You have learned to install, configure, and use PostgreSQL on Debian 12.

Hope you enjoy it. You may be interested in these articles on the Orcacore website:

Install LAMP Stack on Debian 12

Check HTTPS Port 443 is Open on Linux