How To Install PostgreSQL 15 on Debian 11

In this article, we want to teach you How To Install and Configure PostgreSQL 15 on Debian 11.

PostgreSQL is a relational database system. It is available for free under an open-source BSD license. PostgreSQL version 15 is the newest release of PostgreSQL. It includes some new features such as:

1. Create privilege on public schema removed
2. Extended pg_basebackup compression
3. New role: pg_checkpointer
4. MERGE command

Steps To Install and Configure PostgreSQL 15 on Debian 11

To complete this guide, you must log in to your server as a non-root user with sudo privileges. To do this, you can follow our guide on Initial Server Setup with Debian 11.

Set up PostgreSQL 15 on Debian 11

First, you need to update your local package index with the command below:

sudo apt update

Install Required Packages

Then, you need to install the required packages by running the command below:

sudo apt install dirmngr ca-certificates software-properties-common gnupg gnupg2 apt-transport-https curl wget -y

Import PostgreSQL GPG Key

At this point, you need to impost the PostgreSQL GPG key on Debian 11. To do this, run the following command:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Import PostgreSQL 15 Repository on Debian 11

Next, you can import the stable or testing repository, depending on your desired installation. 

To import PostgreSQL 15 run the following command:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Install PostgreSQL 15 on Debian 11

Update your local package index with the command below:

sudo apt update

At this point, use the command below to install PostgreSQL 15:

sudo apt -y install postgresql

Note: If you want a specific version, use ‘postgresql-12’ or similar instead of ‘postgresql’.

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

sudo systemctl status postgresql

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Thu 2022-12-22 04:04:46 EST; 1min 41s ago
   Main PID: 4537 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4679)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service
...

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

sudo systemctl enable postgresql --now

Manage PostgreSQL Service

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 11 by using the following command:

systemctl status postgresql

Configure PostgreSQL 15 on Debian 11

PostgreSQL uses a concept called roles to handle authentication and authorization.

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.

If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

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

su postgres

Output
postgres@olivia:~$

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

postgres@olivia:~$ psql

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

Output
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=#

To exit from the PostgreSQL shell on Debian 11, 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 15 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 15 Database

The Postgres authentication system for any role used to log in, the role should have a database with the same name which 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 15 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. 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.1 (Debian 15.1-1.pgdg110+1))
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

At this point, you have learned to Install and Configure PostgreSQL 15 on Debian 11.

Hope you enjoy it.

You may be like these articles:

Enable and Configure SSH on Debian 11

Fix PuTTy couldn’t agree a host key algorithm Error

How To Install Erlang on Debian 11