Share your love
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: