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:
- Create privilege on public schema removed
- Extended pg_basebackup compression
- New role: pg_checkpointer
- 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:
Output [email protected]:~$
You can access the Postgres shell on Debian 11 by running the following command:
[email protected]:~$ 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:
With this command, you will be back to the Postgres Linux command prompt.
You can type exit to return to the regular system user.
[email protected]:~$ 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:
[email protected]:~$ 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:
[email protected]:~$ 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:
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.
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