A database is a collection of structured data that is organized in such a way that can be easily accessed, managed, or updated. Database Management Software (DBMS) is software that handles the storage, retrieval, and updating of data which ensures data remains consistent and secure. There are 2 main types of databases; Relational databases that organize data in tables of rows and columns and Non-relational databases that do not follow a tabular form of organizing data. Common DBMS include; MySQL server, MariaDB, and PostgreSQL servers.

PostgreSQL is an open-source database management system that extends the SQL language combined with many features to safely store and scale the most complicated data workloads. With over 35 years of active development, PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, and extensibility. PostgreSQL is an open-source relational database with an active community behind the software to consistently deliver performant and innovative solutions. PostgreSQL extensibility is diverse as it allows you to define your own data types, build out custom functions, and even write code from different programming languages without recompiling your database.

The latest version release of PostgreSQL at the time of writing this article is version 17. The current version has new features, enhancements, and bug fixes reported over several months. They include

• Support for the SQL MERGE command to adjust one table to match another.
• Add multirange input to range_agg().
• Table contents can be published selectively within logical replication publications by specifying column lists and row filter conditions.
• Improved performance for sorts that exceed work_mem.
• Provide more options for compression, including support for Zstandard (zstd) compression.
• It allows extended statistics to record statistics for a parent with all its children.
• It supports structured server log output using the JSON format.
• Performance improvements, especially for in-memory and on-disk sorting.
• Improved planning time for queries referencing partitioned tables
• Allows btree indexes on system and TOAST tables to efficiently store duplicates.
• Enable default logging of checkpoints and slow auto vacuum operations
• It allows the scale of a numeric value to be negative, or greater than its precision.

This guide will look at how to Install PostgreSQL 17 on Debian 12 / Debian 11.

1. Install PostgreSQL 17 on Debian 12 | Debian 11

Update your system packages to the latest versions.

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

The latest version PostgreSQL 17, is not yet included in the Debian repository. Hence we have to enable the PostgreSQL Apt Repository to integrate with your normal systems and patch management and provide automatic updates for all supported versions of PostgreSQL.

Create the file repository configuration:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Import the repository signing key:

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

Update the package lists and install the latest version of PostgreSQL.

sudo apt update
sudo apt -y install postgresql postgresql-client

Once the installation is complete, PostgreSQL will start. Enable the service to start at boot with the following command

sudo systemctl enable postgresql

Then check the status of the service with the following command.

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Tue 2025-01-07 02:32:39 EAT; 2min 9s ago
   Main PID: 1101 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Jan 07 02:32:39 debian-12 systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
Jan 07 02:32:39 debian-12 systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.

To check the installed version of the installed package, use the psql tool to print the server version:

sudo -u postgres psql -c "SELECT version();"

The output should look something like the one below:

                                                       version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

2. Secure your PostgreSQL Database

It is recommended to secure your database to prevent it from being exposed. To do so, we need to connect to the PostgreSQL instance of the database with the following command.

$ sudo -u postgres psql
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

postgres=#

Then set the password for the instance with the following command syntax. Ensure you set a Strong and secure Password for your instance.

postgres=# ALTER USER postgres PASSWORD 'YourPassword';
ALTER ROLE
postgres=#

Then exit the shell with the following command.

postgres=# QUIT;

3. Change Default Port

By default, PostgreSQL listens from the local host connection. To allow remote connection, edit the configuration file.

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

Set to listen_addresses = ‘*’ in the CONNECTIONS AND AUTHENTICATION section to instruct the server to listen on all network interfaces. Also, remove the # to comment it out for it to function.

#------------------------------------------------------------------>
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------>

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of a>
                                        # defaults to 'localhost'; >
                                        # (change requires restart

Save and exit the file.

Allow password authentication on your PostgreSQL server by running the following command.

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/17/main/pg_hba.conf

Then change the identification method from peer to trust, which accepts the connection without further requirements with the following command.

sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/17/main/pg_hba.conf

Specify the remote network range or IP address allowed to connect by creating an entry to the pg_hba.conf file.

sudo vim /etc/postgresql/17/main/pg_hba.conf

Add the following entry to the file to accept all remote connections.

host      all      all      0.0.0.0/0       md5

To allow IP from a local network, you could use the following:

host      all      all      192.168.200.0/24      md5

Save the file and restart the PostgreSQL service to apply changes.

sudo systemctl restart postgresql

4. Create a User and Database

To create a new user, Login to the PostgreSQL shell with the following command.

sudo -i -u postgres

Then create a user with the following command.

createuser cloudspinx

Create a password for the user. First, log in to the shell:

psql

Use a strong password:

ALTER USER cloudspinx PASSWORD 'myPassword';

Create a database with the following common:

CREATE DATABASE cloudspinxDB;

Assign the database to the new user:

GRANT ALL PRIVILEGES ON DATABASE cloudspinxDB to cloudspinx;

Quit the shell:

\q

5. Create a table, input data, and clean it

Login to the shell with the following command. This time note we are using the newly created database.

sudo -i -u postgres psql cloudspinxDB

To create a table. Use the CREATE statement to do so. I will be creating a simple table with the attributes as shown below.

cloudspinxDB=# CREATE TABLE colleagues (
        ID                       int,
        First_Name          varchar(50),
        Last_Name          varchar(50),
        Department         varchar(50)
);
CREATE TABLE

Then populate your table with data using the following command with the INSERT statement:

clouspinxDB=# INSERT INTO colleagues 
VALUES  ( 2019, 'Mark', 'Carlton', 'Accounts'),
             ( 2341, 'Jane', 'Doe', 'IT'),
             ( 2134, 'John', 'Doe', 'HR'),
             ( 3123, 'Sia', 'Nungari', 'Reception'),
             ( 6231, 'Karen', 'Karfield', 'Reception'),
             ( 6543, 'Amos', 'Wart', 'Accounts');
INSERT 0 6

To Query a table, use the SELECT statement.

cloudspinxDB=# SELECT * FROM colleagues;
  id  | first_name | last_name | department 
------+------------+-----------+------------
 2019 | Mark       | Carlton   | Accounts
 2341 | Jane       | Doe       | IT
 2134 | John       | Doe       | HR
 3123 | Sia        | Nungari   | Reception
 6231 | Karen      | Karfield  | Reception
 6543 | Amos       | Wart      | Accounts
(6 rows)

You can remove a row from a table using the DELETE command.

cloudspinxDB=# DELETE FROM colleagues WHERE Department = 'Accounts';
DELETE 2

Then check the table; the records from the Accounts department are removed.

cloudspinxDB=# SELECT * FROM colleagues;
  id  | first_name | last_name | department 
------+------------+-----------+------------
 2341 | Jane       | Doe       | IT
 2134 | John       | Doe       | HR
 3123 | Sia        | Nungari   | Reception
 6231 | Karen      | Karfield  | Reception
(4 rows)

To drop a table, use the DROP statement:

cloudspinxDB=# DROP TABLE colleagues;
DROP TABLE

6. Connect to PostgreSQL from a Remote Instance

We will now test our connection to the instance from a remote client. I will be using Ubuntu 22.04 which is in the same network as the server to test the connection. Use the following command syntax.

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

So in my case, to connect to the newly created user and database, the command would look like something below.

psql 'postgres://cloudspinx:[email protected]:5432/cloudspinxDB?sslmode=disable'

This will give an output like the one below:

cloudspinx@Ubuntu22:~$ psql 'postgres://cloudspinx:$Xcqt0689@192.168.1.181:5432/cloudspinxDB?sslmode=disable'
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
Type "help" for help.

cloudspinxDB=> 

You can now connect to an instance remotely.

Verdict

In this guide, we have installed PostgreSQL 17 on Debian 12 / Debian 11. PostgreSQL 17 contains many new features and enhancements that advance the Open Source Relational Database to higher heights. PostgreSQL aims to help administrators to protect data integrity, developers build applications in fault-tolerant environments and help users manage their data no matter how big or small the dataset. It is also highly scalable in the quantity of data it can handle and the number of concurrent users it can accommodate.

Similar guides:

• Run PostgreSQL 16,15,14,13 in Podman with pgAdmin
• How To Install PostgreSQL 16 on CentOS 9 / RHEL 9