To Install PostgreSQL on Debian 11.3

Introduction

Open-source PostgreSQL is a powerful, object-relational database, known for its reliability and performance. There are a variety of features that make it easier for developers and administrators to deploy data-driven applications.

Installation Procedure:

Step 1: Check the installed OS version by using the below command

linuxhelp@linuxhelp:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 11 (bullseye)
Release:        11
Codename:       bullseye
No LSB modules are available.

Step 2: Check the IP address by using the below command

root@linuxhelp:~# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:82:27:2e brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.6.127/23 brd 192.168.7.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20bb:a04d:5b24:61a6/64 scope link noprefixroute 

Step 3: Show PostgreSQL the by using the below command

root@linuxhelp:~# apt show postgresql
Package: postgresql
Version: 13+225
Priority: optional
Section: database
Source: postgresql-common (225)
Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Installed-Size: 69.6 kB
Depends: postgresql-13
Suggests: postgresql-doc
Tag: devel::lang:sql, interface::daemon, network::server, network::service,
 role::metapackage, role::program, suite::postgresql, works-with::db
Download-Size: 64.7 kB
APT-Manual-Installed: yes
APT-Sources: http://deb.debian.org/debian bullseye/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

Step 4: Install PostgreSQL the by using the below command

root@linuxhelp:~# apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libpq5 postgresql-13 postgresql-client-13 postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc postgresql-doc-13 libjson-perl isag
The following NEW packages will be installed:
  libpq5 postgresql postgresql-13 postgresql-client-13 postgresql-client-common postgresql-common postgresql-contrib
  sysstat
0 upgraded, 8 newly installed, 0 to remove and 263 not upgraded.
Need to get 16.0 MB of archives.
After this operation, 48.9 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://in.archive.ubuntu.com/ubuntu hirsute-updates/main amd64 libpq5 amd64 13.4-0ubuntu0.21.04.1 [116 kB]
Get:2 http://in.archive.ubuntu.com/ubuntu hirsute/main amd64 postgresql-client-common all 225ubuntu1 [28.7 kB]

Setting up sysstat (12.5.2-2) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-collect.timer → /lib/systemd/system/sysstat-collect.
timer.
Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-summary.timer → /lib/systemd/system/sysstat-summary.
timer.
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Setting up postgresql-contrib (13+225ubuntu1) ...
Processing triggers for man-db (2.9.4-2) ...
Processing triggers for libc-bin (2.33-0ubuntu5) ...

Step 5: Check the status PostgreSQL by using the below command

root@linuxhelp:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Thu 2022-06-23 05:31:32 IST; 2s ago
    Process: 37489 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 37489 (code=exited, status=0/SUCCESS)
        CPU: 6ms

Step 6: Start the PostgreSQL by using the below command

root@linuxhelp:~# systemctl start postgresql

Step 7: Logon to PostgreSQL user by using the below command

root@linuxhelp:~# su – postgres

Step 8: Open the PostgreSQL by using the below command

postgres@linuxhelp:~$ psql
psql (13.7 (Debian 13.7-0+deb11u1))
Type "help" for help.

Step 9: Create a database named “sample_db” by using the below command

postgres=# CREATE DATABASE sample_db;
CREATE DATABASE

Step 10: Create a user named “sample_ user” with password by using the below command

postgres=# create user sample_user with encrypted password 'Linuxc#7';
CREATE ROLE

Step 11: Grant all privileges for “sample_ user” user to “sample_db” database by using the below command

postgres=# grant all privileges on database sample_db to sample_user;
GRANT

Step 12: Show the PostgreSQL database by using the below command

postgres-# \l1
                              List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |    Access privileges     
-----------+----------+----------+---------+-------+--------------------------
 postgres  | postgres | UTF8     | en_IN   | en_IN | 
 sample_db | postgres | UTF8     | en_IN   | en_IN | =Tc/postgres            +
           |          |          |         |       | postgres=CTc/postgres   +
           |          |          |         |       | sample_user=CTc/postgres
 template0 | postgres | UTF8     | en_IN   | en_IN | =c/postgres             +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_IN   | en_IN | =c/postgres             +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

Step 13: Exit from the Database by using the below command

postgres=# exit 

Conclusion:

We have reached the end of this article. In this guide, we have walked you through the steps required to Install PostgreSQL on Debian 11.3.Your feedback is much welcome.