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.

If you prefer Container option check out the following article.

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

2. Install PostgreSQL 11 on Debian

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

sudo apt update
sudo apt install postgresql-11

The service is usually started after installation.

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 11-main
     Loaded: loaded (/lib/systemd/system/[email protected]; 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/[email protected]
             ├─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

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

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:

Related:

RELATED ARTICLESMORE FROM AUTHOR

LEAVE A REPLY

Please enter your comment!
Please enter your name here