How to Install PostgreSQL on Debian 11 / Debian 10

0

PostgreSQL (often abbreviated as Postgres) is an object-relational database management system (ORDBMS) emphasizing extensibility and standards compliance.

It has more than 30 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and performance.

PostgreSQL is released under the PostgreSQL License and is available for Linux, Microsoft Windows, FreeBSD, OpenBSD, and macOS.

In this post, we will see how to install PostgreSQL on Debian 11 / Debian 10.

Add PostgreSQL repository

PostgreSQL publishes packages for Debian operating system through their dedicated repository, and the packages in their repository are fresher than those available in the OS repository.

First, update the repository index using the apt command

sudo apt update

Then, install the HTTPS support for apt to get packages from the internet.

sudo apt install -y curl apt-transport-https

Now, import the PostgreSQL signing key.

# Debian 11

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg

# Debian 10

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Finally, add the PostgreSQL repository information to the system with the below command.

# Debian 11

echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

# Debian 10

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

Install PostgreSQL on Debian

Once you have added the PostgreSQL repository, run the following command to update the repository index.

sudo apt update

Then, install PostgreSQL using the below command based on the PostgreSQL version you want to install.

# PostgreSQL 13

sudo apt install -y postgresql-13

# PostgreSQL 12

sudo apt install -y postgresql-12

By now, the PostgreSQL service will be up and running. You can check the status of the service with the below command.

sudo systemctl status postgresql

Output:

 postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Wed 2021-09-15 14:19:30 CDT; 42s ago
 Main PID: 3759 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 2343)
   Memory: 0B
   CGroup: /system.slice/postgresql.service

Sep 15 14:19:30 debian.itzgeek.local systemd[1]: Starting PostgreSQL RDBMS...
Sep 15 14:19:30 debian.itzgeek.local systemd[1]: Started PostgreSQL RDBMS.

Configure PostgreSQL Server

By default, PostgreSQL listens on the localhost (127.0.0.1), which you may not always want as external applications may need to connect to the database. So, we will configure PostgreSQL to listen to the system IP address.

# PostgreSQL 13

sudo nano /etc/postgresql/13/main/postgresql.conf

# PostgreSQL 12

sudo nano /etc/postgresql/12/main/postgresql.conf

Set the listen_addresses to * or <IPAddress>.

listen_addresses = '192.168.0.10'

Restart PostgreSQL service.

sudo systemctl restart postgresql

You can now use the netstat command to confirm if PostgreSQL is listening on port 5432 or not.

sudo netstat -antup | grep 5432

Output:

tcp        0      0 192.168.0.10:5432       0.0.0.0:*               LISTEN      5265/postgres

READ: netstat command not found on Debian – Quick Fix

Access PostgreSQL

To manage the PostgreSQL database, you will need to log in as a postgres (Linux user) and then have to access the database shell using the psql command.

sudo -u postgres psql

Output:

postgres@server:~$ psql
psql (13.4 (Debian 13.4-1.pgdg100+1))
Type "help" for help.

postgres=#

On psql shell, run the below command to change the postgres user (Database admin password.

postgres=# \password

OR

postgres=# \password postgres

Create PostgreSQL Database and User

First, create a database user with the below command.

CREATE USER mydb_user WITH ENCRYPTED PASSWORD 'password';

Next, create a database with the below command.

CREATE DATABASE mydb WITH OWNER mydb_user;

Then, you can grant privileges to mydb_user on the newly created database.

GRANT ALL PRIVILEGES ON DATABASE mydb TO mydb_user;

Now, check whether the user has been created or not.

postgres=# \du

Output:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 mydb_user |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

Also, you can list databases to see if your database mydb has been created.

postgres=# \l

Output:

postgres=# \l
                                   List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------+-----------+----------+-------------+-------------+-------------------------
 mydb      | mydb_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/mydb_user          +
           |           |          |             |             | mydb_user=CTc/mydb_user
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres            +
           |           |          |             |             | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres            +
           |           |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

Finally, log in to the mydb with the database user mydb_user.

psql -h localhost -d mydb -U mydb_user

Output:

Password for user mydb_user:
psql (13.4 (Debian 13.4-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydb=>

Conclusion

That’s All. I hope you have learned how to install PostgreSQL on Debian 11 / Debian 10.

You might also like