How to Install PostgreSQL 15 on Debian 12/11/10

PostgreSQL 15 is an advanced iteration of the world’s most highly-regarded open-source database system, which builds upon the performance improvements of previous releases and introduces significant gains for managing workloads in both local and distributed deployments. The PostgreSQL community has dedicated their efforts to continuously enhancing the experience for developers while ensuring high-performance data workloads, making PostgreSQL 15 an excellent choice for application development and data safety.

Major Enhancements in PostgreSQL 15

• Improved Sort Performance and Compression: PostgreSQL 15 introduces enhancements to in-memory and on-disk sorting algorithms, showing considerable speedups based on the types of data sorted. Queries using SELECT DISTINCT can now be executed in parallel. This release also extends its performance improvements to its archiving and backup facilities, providing support for LZ4 and Zstandard (zstd) compression to write-ahead log (WAL) files and introducing custom modules for archiving.
• Expressive Developer Features: PostgreSQL 15 includes the SQL standard MERGE command, enabling developers to write conditional SQL statements that encompass INSERT, UPDATE, and DELETE actions within a single statement. Additionally, new functions for using regular expressions to inspect strings have been added. This version also provides a feature called security_invoker, allowing users to create views that query data using the permissions of the caller, not the view creator, enhancing data security.
• More Options with Logical Replication: The release introduces more flexibility for managing logical replication, including row filtering and column lists for publishers and features to simplify conflict management. Furthermore, PostgreSQL 15 supports the use of two-phase commit (2PC) with logical replication, enhancing data consistency and reliability.
• Logging and Configuration Enhancements: PostgreSQL 15 introduces a new logging format – jsonlog, facilitating the processing of PostgreSQL logs in structured logging systems. The release also provides more flexibility in managing PostgreSQL configuration, including the ability to grant users permission to alter server-level configuration parameters.
• Other Notable Changes: PostgreSQL 15 has several other improvements such as server-level statistics now being collected in shared memory, the ability to make an ICU collation the default collation for a cluster or an individual database, and the addition of a new built-in extension – pg_walinspect that allows users to inspect the contents of write-ahead log files directly from a SQL interface. Moreover, this release revokes the CREATE permission from all users except a database owner from the public (or default) schema and removes the long-deprecated “exclusive backup” mode and support for Python 2 from PL/Python.

In the upcoming sections, the guide will demonstrate how to install PostgreSQL 15 on Debian 12 Bookworm, Debian 11 Bullseye, or Debian 10 Buster utilizing the PostgreSQL official APT Debian repository for the latest version built for your Debian distribution version, ensuring you will never lag behind in receiving a performance or critical security update.

Section 1: Incorporating PostgreSQL APT Repository

Step 1: Refreshing Debian System

Before we commence, it’s important to ensure your system is current, minimizing the possibility of package conflicts. This is achieved by executing the following commands that update and upgrade your Debian packages.

sudo apt update
sudo apt upgrade

Step 2: Incorporating Necessary Packages

To facilitate the installation of the database software, certain auxiliary packages are required. Install these packages by running the following command:

sudo apt install software-properties-common apt-transport-https curl -y

Step 3: Integrating PostgreSQL Repository

To ensure a secure installation, we first need to authenticate the PostgreSQL GPG key. This process helps verify the installation package’s legitimacy, safeguarding your system. Obtain and authenticate the PostgreSQL GPG key with the following command:

curl -fsSl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg > /dev/null

With the key securely authenticated, we can now import the PostgreSQL repository into your system:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main | sudo tee /etc/apt/sources.list.d/postgresql.list

Step 4: Refreshing APT Packages List

With the PostgreSQL repository imported successfully, the next task is to refresh your system’s repository sources list. This update ensures the latest additions, including the PostgreSQL repository, are recognized. To achieve this, execute the following command:

sudo apt update

Step 5: Commencing PostgreSQL 15 Installation

With the system updated and all prerequisites in place, we can now initiate the installation of PostgreSQL 15. Install the database and the client software using the following command:

sudo apt install postgresql-client-15 postgresql-15

For a more comprehensive setup, consider installing the following packages as well:

sudo apt install postgresql-server-dev-15 libpq-dev

These additional packages provide various functionalities. Here is a brief rundown:

• postgresql-client-15: Contains client libraries and client binaries.
• postgresql-15: Houses the core database server.
• libpq-dev: Offers libraries and headers for C language frontend development.
• postgresql-server-dev-15: Provides libraries and headers for C language backend development.

Step 6: Verifying Successful PostgreSQL 15 Installation

Following the installation, it’s crucial to verify the PostgreSQL 15 service status to ensure a successful installation and active service. Use the following command to do so:

systemctl status postgresql

Example output:

If the service is inactive for any reason, activate the service and set PostgreSQL to start automatically on system startup with the following command:

sudo systemctl enable postgresql --now

Ensure you replace –now with --now to avoid errors. This check confirms the successful installation and activation of PostgreSQL 15, paving the way for a secure and efficient database management experience.

Section 2: Navigating Service Commands for PostgreSQL

In this section, we will discuss the essential service commands for managing the PostgreSQL server. PostgreSQL operates as a service aptly named “PostgreSQL,” and we can manage this service using the systemd system and service manager.

Halting PostgreSQL Server

At times, you might need to halt the PostgreSQL server, possibly for maintenance or to manage system resources. To accomplish this, execute the following command:

sudo systemctl stop postgresql

This command instructs the PostgreSQL service to cease operations, freeing up any resources it was consuming.

Initiating PostgreSQL Server

To get the PostgreSQL server running again after halting it, or to simply start it if it isn’t currently running, use the following command:

sudo systemctl start postgresql

This command propels the PostgreSQL service into action, making it ready for database operations.

Rebooting PostgreSQL Server

There might be occasions when you need to reboot the PostgreSQL service. This might be necessary after making configuration changes or when troubleshooting. You can achieve this using the following command:

sudo systemctl restart postgresql

This command will effectively halt the PostgreSQL service and then restart it.

Refreshing PostgreSQL Server

If you’ve made configuration changes that need to be applied without disrupting the current operations of the PostgreSQL service, you can use the following command:

sudo systemctl reload postgresql

This command requests the PostgreSQL service to apply the new changes while continuing its operations.

Evaluating PostgreSQL Service Status

To examine the current status of the PostgreSQL service, whether it’s active, stopped, or facing any issues, you can employ the following command:

systemctl status postgresql

This command provides a snapshot of the PostgreSQL service’s current state, which is valuable for monitoring and troubleshooting purposes.

Section 3: Configuring the PostgreSQL Server

In this section, we will delve into the configuration of the PostgreSQL server. This entails interacting with the PostgreSQL account, establishing new roles, and setting up databases. Let’s walk through the process step-by-step.

Step 1: Interacting with the PostgreSQL Account

During the PostgreSQL installation process, a user account named ‘postgres’ was created. This account is associated with the default Postgres role and has the ‘create role’ privilege. Thus, it can create new roles within PostgreSQL.

To interact with this account, use the following command:

sudo -i -u postgres

Upon running this command, you can directly access the PostgreSQL prompt by typing:

psql

If the terminal now displays “postgres=#”, it signifies a successful connection to the database. When you’re finished with your PostgreSQL operations, you can exit the database by entering the command:

exit

Step 2: Alternative Method for PostgreSQL Interaction

For those who prefer to interact with the PostgreSQL database without switching user accounts, there’s an alternative. You can use the sudo command to connect directly to the database with the following command:

sudo -u postgres psql

Just as with the first method, you can exit this terminal session by entering exit.

Step 3: Creation of User Roles and Database

The ‘postgres’ account, a superuser account, has the ability to create new user roles and databases. To create a new user, use the following command:

sudo su - postgres -c "createuser <username>"

In the command above, replace <username> with the username for the new account.

Next, create a PostgreSQL database for the new user:

sudo su - postgres -c "createdb <dbname>"

In this command, replace <dbname> with the name of the new database.

Step 4: Granting Database Access to New User

The newly created user needs to have access rights to the database. Connect to the ‘postgres’ superuser account with the command:

sudo -u postgres psql

Next, grant all privileges to the new user on the new database:

GRANT ALL PRIVILEGES ON DATABASE <usernamedb> TO <name>;

Ensure you replace <dbname> with your database name and <username> with your new username.

After this operation, type exit to leave the terminal session.

exit

Section 4: Setting up UFW Firewall for PostgreSQL 15 on Debian

In this section, we’ll discuss how to configure the Uncomplicated Firewall (UFW) to allow PostgreSQL to communicate over its default port, 5432, which is a necessary step if you plan on accessing your database beyond localhost. Let’s proceed step by step.

Step 1: Enabling UFW

Firstly, it is important to confirm that UFW is enabled on your system. To verify this, use the following command:

sudo ufw enable

If UFW is not installed on your system, it can be easily added using the apt package manager that comes bundled with Debian. To install UFW, use the command:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Step 2: Creating UFW Firewall Rules

With UFW enabled and installed, the next step involves creating a rule in UFW that allows traffic on PostgreSQL’s default port, 5432. You can set up rules for a subnet range or an individual IP address.

To set a rule for a subnet range, use the following command:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

In the above command, replace 192.168.1.0/24 with your subnet range.

To set a rule for an individual IP address, use the command:

sudo ufw allow proto tcp from 192.168.1.0 to any port 5432

Again, replace 192.168.1.0 with your IP address.

Section 5: Enabling Remote Access to PostgreSQL

This section will walk you through the process of allowing remote access to your PostgreSQL server. This can be achieved by modifying PostgreSQL’s default listening interface from local (127.0.0.1) to a specified IP, subnet, or all interfaces in the server’s configuration file. We must ensure that the UFW firewall settings do not block this remote access, as we have previously set up in Section 4.

Step 1: Identifying PostgreSQL Version

The first step is to identify which version of PostgreSQL is installed on your system. Use the following command to do so:

ls /etc/postgresql/

This tutorial is based on PostgreSQL 15, one of the latest versions at the time of writing.

Step 2: Modifying Connection Settings in postgresql.conf

Once you’ve identified your PostgreSQL version, your next task involves tweaking the postgresql.conf file. This file serves as the primary configuration file governing your PostgreSQL server’s behavior and settings. Let’s proceed to open this file for editing. We’ll employ the nano text editor for this task:

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

Navigate through the file until you land upon the ‘Connection Settings’ section. This part is of particular importance as it governs how and where your PostgreSQL server listens for incoming connections. Look for the line that says listen_addresses = 'localhost'.

At this stage, it’s crucial to understand what this line signifies. By default, your PostgreSQL server listens for connections on the localhost (127.0.0.1), meaning it only anticipates connections from the same machine. Now, this setting might not cater to your needs, especially if you plan to permit external connections to your PostgreSQL server.

Therefore, to modify this setting, replace ‘localhost’ with the specific IP address on which you wish PostgreSQL to listen. However, if you want your PostgreSQL server to be reachable from any interface, not limiting to a single IP address, replace ‘localhost’ with ‘*’. This way, PostgreSQL is set to listen on all available network interfaces:

listen_addresses = '*'

Once you’ve made the necessary changes, it’s time to save the modifications and exit the editor. To save your changes in the nano editor, press CTRL+O, confirm the filename, and hit enter. Following that, to exit the nano editor, press CTRL+X.

Step 3: Restarting PostgreSQL Instance

The PostgreSQL server needs to be restarted for the changes to take effect. Use the following command to restart your PostgreSQL instance:

sudo systemctl restart postgresql

Step 4: Verifying the Changes

To confirm that the changes have been successfully implemented, use the ss utility built into Debian with the following command:

ss -nlt | grep 5432

Step 5: Configuring Remote Connections in pg_hba.conf

For an advanced level of customization and improved security during PostgreSQL access, the pg_hba.conf file is the perfect tool to fine-tune your PostgreSQL server’s behavior for accepting remote connections. To open this file using the nano text editor, use the following command:

sudo nano /etc/postgresql/15/main/pg_hba.conf

This file might seem intimidating initially due to its comprehensive nature. However, once you understand its structure and function, you will appreciate its flexibility. In essence, the pg_hba.conf file is a set of rules that define from where and how clients can connect to which databases inside your PostgreSQL server.

In each rule line within pg_hba.conf, you can specify:

1. The type of connection (local for unix domain sockets, host for TCP/IP connections)
2. The database(s) this rule applies to
3. The user(s) this rule applies to
4. The network address from which the connections are allowed
5. The authentication method

As an example, a typical line could look like this:

host    mydb    myuser    192.168.100.0/24    md5

Here is what this line defines:

• host: This rule applies to TCP/IP connections.
• mydb: The rule is for connections to the database ‘mydb’.
• myuser: The rule is for connections from the user ‘myuser’.
• 192.168.100.0/24: Connections are allowed from any IP address in the ‘192.168.100.0’ network.
• md5: Connections must be authenticated using md5 password hashing.

After modifying pg_hba.conf, always remember to save and close the file using CTRL+O to write out the changes and CTRL+X to close the nano editor.

Recap of Installing PostgreSQL on Debian Linux

Throughout this comprehensive guide, we have walked you through the process of installing PostgreSQL 15 on various Debian Linux distributions including Debian 12 Bookworm, Debian 11 Bullseye, and Debian 10 Buster. We have discussed the configuration of the PostgreSQL server, managing user roles and databases, setting up the UFW firewall, and enabling remote access. Each step was detailed with professional and informative explanations.

The completion of these steps provides you with a robust PostgreSQL setup ready for your needs, whether that be for development, data analytics, or production applications. We trust that this guide has provided a deep and clear understanding of the process and that you feel equipped with the knowledge to successfully administer your PostgreSQL database system.

Further Learning: Additional Links

To enhance your understanding and gain further insight into PostgreSQL and Linux system management, here are some authoritative sources:

• PostgreSQL Official Documentation: This is the main source of information for all things PostgreSQL. It offers comprehensive details about every feature of PostgreSQL.
• Debian Official Documentation: Provides extensive guidance on the Debian Linux distribution. It’s a must-read if you’re working extensively with Debian.
• PostgreSQL Wiki: An extensive user-editable guide to PostgreSQL, offering a range of resources from basic setup and configuration to advanced topics.