How to Install PostgreSQL 15 on Debian 11

PostgreSQL also known as Postgres, is a free and open source object-relational database system that runs on Client-Server architecture. It is one of the leading database servers used for production servers. DevOps use it as an alternative to MariaDB.

In this guide we are going to install PostgreSQL 15 in Debian 11.

1. Update Operating System

Update your Debian 11 operating system to make sure all existing packages are up to date:

# apt update && sudo apt upgrade -y

2. Install PostgreSQL 15 using the official repository

PostgreSQL is available in the default Debian repositories but the available versions are not up to date.

To get the latest packages of PostgreSQL, you must add the official repo of PostgreSQL.

First, install all required dependencies by running the following command:

# apt-get install wget sudo curl gnupg2 -y

After installing all the dependencies, create the file repository configuration with the following command:

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

Now import the repository signing key by using the following command:

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

Update your local package index with the following command:

# apt -y update

Now you can run the following command to install the latest version of the PostgreSQL server:

# apt-get install postgresql-15 -y

After the successful installation, start the PostgreSQL service and enable it to start after the system reboot:

# systemctl start postgresql
# systemctl enable postgresql

Verify that is active and running on your server:

# systemctl status postgresql

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited)
   Main PID: 7543 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2300)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

By default, PostgreSQL listens on port 5432. You can check it with the following command:

# ss -antpl | grep 5432

You will get the PostgreSQL listening port in the following output:

LISTEN 0      244             127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=7525,fd=6))
LISTEN 0      244                 [::1]:5432          [::]:*    users:(("postgres",pid=7525,fd=5))

You can also check the version using the following command:

# sudo -u postgres psql -c "SELECT version();"

You will get the version in the following output:

                                                           version
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.0 (Debian 15.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

3. Logging into PostgreSQL

To access your PostgreSQL server, you must log in as a Postgres user. To do this run the following command:

# su postgres

Now you can access your PostgreSQL shell with the following command:

$ psql

In your output you will see:

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

postgres=#

Once you have accessed the command shell of Postgres, you can now use SQL queries to perform several database-related operations.

To set the Postgres pasword, run the following command:

ALTER USER postgres PASSWORD 'password';

To create a database named test, run the following command:

CREATE DATABASE test;

To list all databases, run the following command:

\l

You should see all databases in the following output:

To switch the database to test, run the following command:

\c test

To create a table (e.g accounts), run the following command:

CREATE TABLE accounts (
	user_id serial PRIMARY KEY,
	username VARCHAR ( 50 ) UNIQUE NOT NULL,
	password VARCHAR ( 50 ) NOT NULL,
	email VARCHAR ( 255 ) UNIQUE NOT NULL,
	created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);

To list all tables, run the following command:

\dt

You should see all tables in the following output:

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | accounts | table | postgres
(1 row)<

To exit from the shell, run the following command:

exit

4. Backup and Restore a Single Database

You can back up and restore a single database using the pg_dump utility.

For example, to back up a single database named test and generate a backup file named test_backup.sql, run the following command:

su - postgres
pg_dump -d test -f test_backup.sql

You can also restore a single database using psql command.

For example, to restore a single database named test from a backup file named test_backup.sql, run the following command:

su - postgres
psql -d test -f test_backup.sql

Comments and Conclusion

At this point, you learn to set up PostgreSQL 15 on Debian 11.  If you need more information, or have any questions, just comment below and we will be glad to assist you!

Check below for some of our other articles.