You can support us by downloading this article as PDF from the Link below. Download the guide as PDF

This guide will walk you through the steps required to Install PostgreSQL 11 on Debian 10 (Buster). 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 10 (Buster).

Step 1: Add PostgreSQL Repository

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

sudo apt update
sudo apt -y upgrade

Then Import the repository signing key:

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

Add the repository:

RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list

Step 2: Install PostgreSQL 11 on Debian 10 (Buster)

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

sudo apt update
sudo apt -y install postgresql-11

The service is usually started after installation.

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2019-03-29 13:15:54 UTC; 3min 37s ago
Main PID: 1360 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 1148)
Memory: 0B
CGroup: /system.slice/postgresql.service

We need to set PostgreSQL admin user Password:

$ sudo su - postgres 
[email protected]:~$ psql -c "alter user postgres with password 'StrongDBPassword'"
ALTER ROLE

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
[email protected]:~$ createuser test_user1

Add the test database and grant ownership to test_user1:

[email protected]:~$ createdb test_db -O test_user1

Set user password:

[email protected]:~$ psql 
psql (11.2 (Debian 11.2-2))
Type "help" for help.
postgres=# alter user test_user1 with password 'DBUserPassword';
ALTER ROLE

Login to test_db database:

[email protected]:~$ psql -l  | grep test_db
test_db | test_user1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

[email protected]:~$ psql test_db
psql (11.2 (Debian 11.2-2))
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=#

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

[email protected]:~$ dropdb test_db

That’s all. You have successfully installed PostgreSQL 11 on Debian 10 (Buster).  visit PostgreSQL official Documentation page for further reading.

Related:

How to Install PostgreSQL 11 on Debian 9 / Debian 8

How to Install PostgreSQL 11 on FreeBSD 12

Install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04

How to install PostgreSQL 11 on Fedora 29 / Fedora 28

How to install PostgreSQL 11 on CentOS 7

You can support us by downloading this article as PDF from the Link below. Download the guide as PDF

LEAVE A REPLY

Please enter your comment!
Please enter your name here