How to Install PostgreSQL 15 on Debian 12, 11 or 10

In the realm of open-source database systems, PostgreSQL 15 emerges as a frontrunner, building on the legacy of its predecessors. For those keen on integrating this advanced database system, this guide will elucidate how to install PostgreSQL 15 on Debian 12 Bookworm or the older stable releases of Debian 11 Bullseye or Debian 10 Buster. With a focus on performance, developer experience, and data safety, PostgreSQL 15 is tailored to meet the evolving needs of modern applications.

Diving into PostgreSQL 15’s Key Features:

• Optimized Sorting and Compression: This release boasts in-memory and on-disk sorting improvements, with notable speed enhancements for varied data types. Adding LZ4 and Zstandard (zstd) compression for write-ahead log (WAL) files further optimizes performance.
• Developer-Centric Additions: Integrating the SQL standard MERGE command streamlines the writing of conditional SQL statements. New regular expression functions and the security_invoker feature bolster functionality and data security.
• Enhanced Logical Replication: PostgreSQL 15 offers greater control over logical replication, from row filtering to conflict management. The support for two-phase commit (2PC) ensures data consistency.
• Refined Logging and Configuration: The introduction of the jsonlog format simplifies log processing. Additionally, users gain more autonomy in managing server-level configurations.
• Additional Improvements: This version introduces server-level statistics in shared memory, the ability to set an ICU collation as the default, and the pg_walinspect extension for direct SQL-based inspection of write-ahead log files. It also enhances security by restricting the CREATE permission and removing outdated features like the “exclusive backup” mode.

With these advancements, PostgreSQL 15 is a testament to the community’s commitment to delivering a robust, efficient, and developer-friendly database system.

Import PostgreSQL 15 APT Repository on Debian 12, 11 or 10

Step 1: Refresh Debian System Packages Before PostgreSQL 15 Install

Before we commence, ensuring your system is current is essential, minimizing the possibility of package conflicts. This is achieved by executing the following commands:

sudo apt update
sudo apt upgrade

Step 2: Install Initial Required Packages For PostgreSQL 15

Specific auxiliary packages are required to facilitate the installation of the database software. Install these packages by running the following command:

sudo apt install dirmngr ca-certificates software-properties-common apt-transport-https lsb-release curl -y

Step 3: Integrating PostgreSQL Repository

We must first authenticate the PostgreSQL GPG key to ensure a secure installation. 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: Update APT Packages Cache After PostgreSQL 15 Import

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: Install PostgreSQL 15 via APT Command on Debian

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, verifying the PostgreSQL 15 service status is crucial to ensure a successful installation and active service. Use the following command to do so:

systemctl status postgresql

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.

Service Commands for PostgreSQL 15 on Debian 12, 11 or 10

This section 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 15 Server

Sometimes, you might need to halt the PostgreSQL server 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 consumes.

Initiating PostgreSQL 15 Server

To get the PostgreSQL server running again after halting it or to 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 15 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 15 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 15 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.

Configure PostgreSQL 15 on Debian 12, 11 or 10

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

There’s an alternative for those who prefer interacting with the PostgreSQL database without switching user accounts. 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, can 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 PostgreSQL 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

Configure UFW Firewall for PostgreSQL 15 on Debian 12, 11 or 10

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 necessary if you plan to access your database beyond localhost. Let’s proceed step by step.

Step 1: Enabling UFW on Debian

Firstly, confirming that UFW is enabled on your system is essential. 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 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.

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.

For individual IP addresses, 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.

Enable Remote Access to PostgreSQL 15 on Debian 12, 11 or 10

In this section, you’ll learn how to permit remote access to your PostgreSQL server. You can achieve this by changing PostgreSQL’s default listening interface from local (127.0.0.1) to a designated IP, subnet, or all server interfaces in the configuration file.

Ensure the UFW firewall settings in the last section do not block this remote access and, even more importantly, secure remote access to the PostgreSQL 15 database.

Step 1: Identify Your PostgreSQL Version

Begin by determining the PostgreSQL version running on your system. Execute the command below:

ls /etc/postgresql/

Step 2: Modify Connection Settings in postgresql.conf

After pinpointing your PostgreSQL version, you need to adjust the postgresql.conf file, the central file that controls your PostgreSQL server’s operations and settings. Use the nano text editor to edit this file:

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

Scroll through the file to find the ‘Connection Settings’ section. This section is vital as it determines how and where your PostgreSQL server waits for inbound connections. Locate the line: listen_addresses = ‘localhost’.

Here, understanding the line’s meaning is essential. By default, your PostgreSQL server waits for connections on the localhost (127.0.0.1), which means it expects connections only from the same device. This default setup might not meet your requirements if you intend to allow outside connections to your PostgreSQL server.

To change this, replace ‘localhost’ with the specific IP address where you want PostgreSQL to listen. If you aim for your PostgreSQL server to be accessible from every interface and not just one IP, change ‘localhost’ to ‘*’. Thus, PostgreSQL will listen on all network interfaces:

listen_addresses = '*'

After making the required adjustments, save the edits and close the editor. To save your edits in the nano editor, press CTRL+O, confirm the filename, then enter. To close 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, you will appreciate its flexibility once you understand its structure and function. In essence, the pg_hba.conf file is a set of rules defining 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 15 on Debian

Throughout this comprehensive guide, we have walked you through installing PostgreSQL 15 on various Debian Linux distributions, including Debian 12 Bookworm, Debian 11 Bullseye, and Debian 10 Buster. We have discussed configuring 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.

Completing these steps provides you with a robust PostgreSQL setup ready for your needs, whether 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 administer your PostgreSQL database system successfully.