Learn how to – How To Install PostgreSQL 11 on Debian 11 / Debian 10

Learn how to – How To Install PostgreSQL 11 on Debian 11 / Debian 10.

This guide will walk you through the steps required to Install PostgreSQL 11 on Debian 11 / Debian 10. PostgreSQL is a powerful, highly-extensible database server written in C. It is the World’s most advanced relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

The first release of PostgreSQL 11 was on 2018-10-18 and you can check more details on the release page. Follow the few steps below to install PostgreSQL 11 on Debian 11 / Debian 10.

Step 1: Add PostgreSQL Repository

Start by ensuring everything is updated on your Debian 11 / Debian 10 system.

sudo apt update
[ -f /var/run/reboot-required ] && sudo reboot -f

Then Import the repository signing key:

sudo apt update
sudo apt install vim curl gpg gnupg2 software-properties-common apt-transport-https lsb-release ca-certificates
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

Add the repository:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Step 2: Install PostgreSQL 11 on Debian

Now install PostgreSQL 11 on Debian 11 / Debian 10 by running the command below.

sudo apt update
sudo apt install postgresql-11

The service is usually started after installation.

$ systemctl status postgresql@11-main.service
● postgresql@11-main.service - PostgreSQL Cluster 11-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Wed 2022-05-18 20:12:39 UTC; 35s ago
    Process: 1070613 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 11-main start (code=exited, status=0/SUCCESS)
   Main PID: 1070618 (postgres)
      Tasks: 7 (limit: 2340)
     Memory: 16.8M
        CPU: 124ms
     CGroup: /system.slice/system-postgresql.slice/postgresql@11-main.service
             ├─1070618 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
             ├─1070620 postgres: 11/main: checkpointer
             ├─1070621 postgres: 11/main: background writer
             ├─1070622 postgres: 11/main: walwriter
             ├─1070623 postgres: 11/main: autovacuum launcher
             ├─1070624 postgres: 11/main: stats collector
             └─1070625 postgres: 11/main: logical replication launcher

May 18 20:12:36 postgresql.cloudlabske.io systemd[1]: Starting PostgreSQL Cluster 11-main...
May 18 20:12:39 postgresql.cloudlabske.io systemd[1]: Started PostgreSQL Cluster 11-main.

We need to set PostgreSQL admin user Password:

$ sudo su - postgres 
postgres@deb10:~$ psql -c "alter user postgres with password 'StrongDBPassword'" 
ALTER ROLE
postgres@deb10:~$ exit

Step 3: Enable remote access (Optional)

By default, access to PostgreSQL database server is only from localhost.

$ ss -tunelp | grep 5432
tcp   LISTEN  0  128  127.0.0.1:5432         0.0.0.0:*      users:(("postgres",pid=15785,fd=3)) uid:111 ino:42331 sk:6 <->

Edit PostgreSQL 11 configuration file if you want to change listening address:

sudo vim /etc/postgresql/11/main/postgresql.conf

Add below line under CONNECTIONS AND AUTHENTICATION section.

listen_addresses = '*' # Don't do this if your server is on public network

You can also specify server IP Address

listen_addresses = '10.10.0.2' # Recommended for LAN connections to DB Server

Restart postgresql after making a change

sudo systemctl restart postgresql

Confirm

$ ss -tunelp | grep 5432
tcp     LISTEN   0        128              0.0.0.0:5432          0.0.0.0:*       uid:108 ino:74999 sk:a <->                                                     
tcp     LISTEN   0        128                 [::]:5432             [::]:*       uid:108 ino:75000 sk:b v6only:1 <->                                            

If you have an active UFW firewall, allow port 5432 for network connections.

sudo ufw allow 5432/tcp

Step 4: Test PostgreSQL Installation

Add a test database user:

$ sudo su - postgres
postgres@deb10:~$ createuser test_user1

Add the test database and grant ownership to test_user1:

postgres@deb10:~$ createdb test_db -O test_user1

Set user password:

postgres@deb10:~$ psql 
psql (11.16 (Debian 11.16-1.pgdg110+1))
Type "help" for help.
postgres=# alter user test_user1 with password 'DBUserPassword';
ALTER ROLE
postgres=# exit

Login to test_db database:

postgres@deb10:~$ psql -l  | grep test_db
test_db   | test_user1 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

postgres@deb10:~$ psql test_db
psql (11.16 (Debian 11.16-1.pgdg110+1))
Type "help" for help.
test_db=# 

Create a table and add some dummy data:

test_db=# create table test_table ( id int,first_name text, last_name text ); 
CREATE TABLE
test_db=# insert into test_table (id,first_name,last_name) values (1,'John','Doe'); 
INSERT 0 1

Show table data:

test_db=# select * from test_table;
  id | first_name | last_name 
 ----+------------+-----------
   1 | John       | Doe
 (1 row)
 test_db=# exit

Let’s drop our test database to retain clean installation.

postgres@deb10:~$ dropdb test_db

That’s all. You have successfully installed PostgreSQL 11 on Debian 11 / Debian 10. Visit PostgreSQL official Documentation page for further reading.

Recommended books:

• Best Books To Learn PostgreSQL Database

Related:

• How to Install PostgreSQL 11 on Debian
• How to Install PostgreSQL 11 on FreeBSD 12
• Install PostgreSQL 11 on Ubuntu
• How to install PostgreSQL 11 on Fedora
• How to install PostgreSQL 11 on CentOS 7