How To Install PostgreSQL On Debian 11.4

To Install PostgreSQL on Debian 11.4

Introduction

PostgreSQL is an open-source, object-relational database that is highly reliable and performant. A variety of features make deploying data-driven applications easier for developers and administrators.

Installation Steps:

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

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

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

root@debian:~# 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:ea:5e:31 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.6.131/23 brd 192.168.7.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:feea:5e31/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

Step 3: Install PostgreSQL the by using the below command

root@debian:~# apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  dbconfig-common dbconfig-mysql default-mysql-client galera-4 icc-profiles-free
  libaio1 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl
  libdbi-perl libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-template-perl
  libjs-bootstrap4 libjs-codemirror libjs-jquery libjs-jquery-mousewheel
  libjs-jquery-timepicker libjs-jquery-ui libjs-openlayers libjs-popper.js
  libjs-sizzle libjs-sphinxdoc libjs-underscore libmariadb3 libonig5
  libterm-readkey-perl libzip4 mariadb-client-10.5 mariadb-client-core-10.5
  mariadb-common mariadb-server-10.5 mariadb-server-core-10.5 mysql-common
  node-jquery rsync socat
Use 'apt autoremove' to remove them.
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 208 not upgraded.
Need to get 17.9 MB of archives.
After this operation, 59.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://deb.debian.org/debian bullseye/main amd64 postgresql-client-common all 225 [89.3 kB]
Get:2 http://deb.debian.org/debian bullseye/main amd64 postgresql-common all 225 [237 kB]
Get:3 http://security.debian.org/debian-security bullseye-security/main amd64 libpq5 amd64 13.11-0+deb11u1 [180 kB]
Get:4 http://deb.debian.org/debian bullseye/main amd64 postgresql all 13+225 [64.7 kB]
Get:5 http://deb.debian.org/debian bullseye/main amd64 postgresql-contrib all 13+225 [64.8 kB]
Get:6 http://deb.debian.org/debian bullseye/main amd64 sysstat amd64 12.5.2-2 [603 kB]
Get:7 http://security.debian.org/debian-security bullseye-security/main amd64 postgresql-client-13 amd64 13.11-0+deb11u1 [1,512 kB]
Get:8 http://security.debian.org/debian-security bullseye-security/main amd64 postgresql-13 amd64 13.11-0+deb11u1 [15.1 MB]
Fetched 17.9 MB in 4s (4,607 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libpq5:amd64.
(Reading database ... 142102 files and directories currently installed.)
Preparing to unpack .../0-libpq5_13.11-0+deb11u1_amd64.deb ...
Unpacking libpq5:amd64 (13.11-0+deb11u1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../1-postgresql-client-common_225_all.deb ...
Unpacking postgresql-client-common (225) ...
Selecting previously unselected package postgresql-client-13.
Preparing to unpack .../2-postgresql-client-13_13.11-0+deb11u1_amd64.deb ...
Unpacking postgresql-client-13 (13.11-0+deb11u1) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../3-postgresql-common_225_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (225) ...
Selecting previously unselected package postgresql-13.
Preparing to unpack .../4-postgresql-13_13.11-0+deb11u1_amd64.deb ...
Unpacking postgresql-13 (13.11-0+deb11u1) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../5-postgresql_13+225_all.deb ...
Unpacking postgresql (13+225) ...
Selecting previously unselected package postgresql-contrib.
Preparing to unpack .../6-postgresql-contrib_13+225_all.deb ...
Unpacking postgresql-contrib (13+225) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../7-sysstat_12.5.2-2_amd64.deb ...
Unpacking sysstat (12.5.2-2) ...
Setting up postgresql-client-common (225) ...
Setting up libpq5:amd64 (13.11-0+deb11u1) ...
Setting up postgresql-client-13 (13.11-0+deb11u1) ...
update-alternatives: using /usr/share/postgresql/13/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (225) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-13 (13.11-0+deb11u1) ...
Creating new PostgreSQL cluster 13/main ...
/usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/13/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... US/Central
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 13 main start

Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
update-alternatives: using /usr/share/postgresql/13/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (13+225) ...
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+225) ...
Processing triggers for man-db (2.9.4-2) ...
Processing triggers for libc-bin (2.31-13+deb11u3) ...

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

root@debian:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: e>
     Active: active (exited) since Sat 2023-06-24 19:22:57 CDT; 17s ago
   Main PID: 14319 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4623)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

Jun 24 19:22:57 debian systemd[1]: Starting PostgreSQL RDBMS...
Jun 24 19:22:57 debian systemd[1]: Finished PostgreSQL RDBMS.

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

root@debian:~# su - postgres

Step 6: Open the PostgreSQL by using the below command

postgres@debian:/root$ psql
psql (13.11 (Debian 13.11-0+deb11u1))
Type "help" for help.

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

postgres=# CREATE DATABASE sample_db;
CREATE DATABASE

Step 8: 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 9: 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 10: Show the PostgreSQL database by using the below command

postgres=# \l

Step 11: 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.