Installing PostgreSQL on Debian 13 Trixie: The Complete Guide

Are you ready to find out how to install PostgreSQL on Debian 13? You're in the right place! PostgreSQL, often referred to simply as Postgres, is a powerful open source relational database management system (RDBMS) known for its reliability, robustness, and rich functionality. Whether you are developing a new web application, managing large amounts of data, or simply exploring the world of databases, this guide will walk you through it step by step.

In this article, we will look together at how to install the latest version of PostgreSQL on Debian 13 (Trixie), configure basic access, and take your first steps with this amazing database.

Why Choose PostgreSQL?

PostgreSQL stands out for several reasons:

• Open Source and Free: No license fees.
• Extensibility: Supports custom data types, functions, operators and aggregates.
• SQL Standard Compliance: High adherence to SQL standards.
• Reliability and Stability: Ideal for mission-critical applications due to its robust architecture.
• Active Community: Extensive documentation and support from the community.
• Advanced Features: Support for JSON, ACID transactions, replication, and more.

If you are looking for a powerful and flexible database solution for your Debian 13 server, PostgreSQL is an excellent choice.

Prerequisites

Before you get started, make sure you have:Noble Numbat

• A system with Debian 13 LTS (Trixie) installed.
• Access to a user with privileges on -.
• An active Internet connection to download packages.

Step 1: Update the Debian System

It is always a good practice to update the list of packages and installed packages before adding new software. Open a terminal and type:

apt update
apt upgrade -y

This command will update the list of available packages and then update all installed packages to the latest version.

Step 2: Install PostgreSQL on Debian 13

Debian 13 includes a recent version of PostgreSQL in its official repositories. At the time of writing this article, the default version is PostgreSQL 17. To install it, along with the postgresql-contrib package that provides additional utilities and functionality, run:

apt install postgresql postgresql-contrib -y

Verify Installation

Once the installation is complete, the PostgreSQL service should start automatically. You can check its status with the following command:

systemctl status postgresql.service

If everything was successful, you should see output indicating that the service is active (running). Press q to exit the status view.

You can also check the installed version with:

psql --version

It should return something like psql (PostgreSQL) 17.x (Debian 17.x).

Step 3: Access PostgreSQL and Configure User postgres

During installation, PostgreSQL creates an operating system-level user called postgres. This user has the default superuser role within the database.

Connect via the postgres

To connect to the PostgreSQL prompt (called psql), you must first switch to the postgres user in the operating system:

su postgres

Once you have become the postgres user, you can access the psql prompt:

psql

You should now be in the PostgreSQL prompt, indicated by postgres=#. To exit the psql prompt, type \q and press Enter. To return to your normal user, type exit and press Enter.

Set-a-Password-for-Postgres User postgres

For security reasons, it is critical to set a password for the postgres user of the database. Within the psql prompt (after logging in as described above), type:

ALTER USER postgres PASSWORD 'yourPasswordSuperSecure';

Replace 'yourPasswordSuperSecure' with a strong password. Remember that this password is for the database user postgres, not for the operating system user.

Step 4: Create a New User and a New Database (Recommended)

For most applications, it is not recommended to use the postgres user directly. It is a good practice to create dedicated users with specific privileges for your databases.

Create a New User Role

Always from the psql prompt (logged in as user postgres), you can create a new role (user). For example, to create a user named myuser:

CREATE USER myuser WITH PASSWORD 'aPasswordForMyuser';

Create a New Database

Now, let's create a database. For example, a database called mydatabase owned by the my user we just created:

CREATE DATABASE mydatabase OWNER myuser;

Grant-Privileges-to-New-User-on-Database

Even though myuser is the owner of the database, you may want to explicitly grant all privileges:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Exit the psql prompt (\q) and the postgres user (exit).

Verify Connection with New User

Now you can try connecting to your new database with your new user directly from your regular Linux user (without on - postgres -s /bin/bash), if you have configured md5 or scram-sha-256 authentication (usually default for local connections after setting a password).

psql -U myuser -d mydatabase -h localhost

You will be prompted for the password you set for myuser. If the connection is successful, you will see the mydatabase=> prompt.

Note:The default authentication method for local connections (host or local) is often peer for user postgres and md5 or scram-sha-256 for other users after they have been assigned a password. If you encounter authentication problems, you may need to edit the pg_hba.conf file. For Debian 13 with PostgreSQL 17, it is usually located in /etc/postgresql/17/main/pg_hba.conf.

Useful Commands for Managing PostgreSQL

Here are some systemctl commands useful for managing the PostgreSQL service:

• Starting PostgreSQL:

  systemctl start postgresql

• Stop PostgreSQL:

  systemctl stop postgresql

• Restart PostgreSQL:

  systemctl restart postgresql

• Enable PostgreSQL auto-start at boot:

  systemctl enable postgresql

• Disable PostgreSQL auto-start at boot:

  systemctl disable postgresql

Frequently Asked Questions (FAQs) on PostgreSQL Debian 13

• D: Which version of PostgreSQL is installed on Debian 13 with apt?
  R: Debian 13 (Trixie) includes PostgreSQL 17 in its default repositories.
• D: Where are the PostgreSQL configuration files located?
  R: The main configuration files (postgresql.conf and pg_hba.conf) are usually located in /etc/postgresql/16/main/ (the version number may vary if you install a different version).

• D: How can I enable remote connections to PostgreSQL?
  R: You have to edit two files:

  1. postgresql.conf: set listen_addresses = '*'.
  2. pg_hba.conf: add a line to allow connections from the desired network, e.g. host all 0.0.0/0 md5 (for IPv4) or host all ::/0 md5 (for IPv6). Caution: 0.0.0.0/0 allows connections from any IP, carefully consider the security implications.

  Remember to restart the PostgreSQL service after changes.

• D: What is a popular GUI tool for managing PostgreSQL?
  R: pgAdmin is the most popular open source administration and development tool for PostgreSQL. It is available as a web or desktop application.
• D: How do you backup a PostgreSQL database?
  R: You can use the pg_dump utility. For example: pg_dump -U username -d nomedatabase -f backup.sql.

Conclusions

Congratulations! You now have a fully functioning PostgreSQL Debian server on your Debian 13 installation. You have learned how to install PostgreSQL, protect the administrator user, create new users and databases, and manage the service.

PostgreSQL offers a world of possibilities. I encourage you to explore the Official PostgreSQL Documentation to learn more about its many features.

If you found this article helpful, please share it! And remember, for any consulting or support needs on Linux and PostgreSQL, we are at your disposal.