How to install and Configure PostgreSQL 15 on Debian 11.

In this article, we are going to learn how to install and get PostgreSQL 15 up and running on a Debian 11 server. PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Notable Features introduced for PostgreSQL 15.

• It removes both the long deprecated exclusive backup and support from Python 2 from PL/Python.
• It revokes the CREATE permission from all users except for the database owner from the default.
• It adds the new built-in extension, the pg_walinspect, that lets users inspect the contents of write-ahead log files directly from an SQL interface.
• Server-level statistics are now collected in shared memory, eliminating the statistics collector process and periodically writing this data to disk.
• It makes it possible to make an ICU Collation the default collation for a cluster or an individual database.
• It introduces the new logging format jsonlog which outputs log data using a defined json structure and allows PostgreSQL logs to be processed in structured logging systems. This gives database administrators more flexibility on how users can manage PostgreSQL configurations.
• It provides more flexibility for managing logical replication. It introduces row filtering and column lists for publishers, letting users choose to replicate a subset of data from a table.
• It added a feature to simplify conflict management, including the ability to skip replaying a conflicting transaction and to automatically disable a subscription if an error is detected.

1. Update your system packages

Let’s begin by refreshing our system’s local index files using the following command.

sudo apt update

2. Install PostgreSQL 15 on Debian 11

In order to run PostgreSQL 15 on Debian 11, we are supposed to create the file repository configuration on /etc/apt/sources.list.d/pgdg.list entry so that PostgreSQL will be available for installation.

In order to add PostgreSQL to sources.list file, use the following command.

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

Next, we need to import the repository signing key.

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

The next thing is to update the system again for the changes to take effect.

sudo apt update 

Lastly, install PostgreSQL with the following command.

sudo apt-get -y install postgresql-15

You will see the output that resembles the one shown below.

The following additional packages will be installed:
  postgresql-client-15
Suggested packages:
  postgresql-doc-15
The following NEW packages will be installed:
  postgresql-15 postgresql-client-15
0 upgraded, 2 newly installed, 0 to remove and 0 not upgraded.
Need to get 17.8 MB of archives.
After this operation, 59.8 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg/15 amd64 postgresql-client-15 amd64 15~rc2-1.pgdg110+1 [1652 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg/15 amd64 postgresql-15 amd64 15~rc2-1.pgdg110+1 [16.2 MB]

We now need to check if the PostgreSQL server is running with the following command.

$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Fri 2022-10-14 08:02:34 UTC; 10min ago
   Main PID: 12440 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 1149)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

Oct 14 08:02:34 ip-172-31-81-202 systemd[1]: Starting PostgreSQL RDBMS...
Oct 14 08:02:34 ip-172-31-81-202 systemd[1]: Finished PostgreSQL RDBMS.

3. Configuring PostgreSQL

Now we are good to go experiment with PostgreSQL. To access Postgres use the following command.

$ sudo -u postgres psql
could not change directory to "/root": Permission denied
psql (15rc2 (Debian 15~rc2-1.pgdg110+1))
Type "help" for help.

postgres=# 

4. Uninstalling PostgreSQL 15

Whenever you want to remove PostgreSQL from your system for any reason known to you, uninstall using the following procedure.

First, you need to list the PostgreSQL packages to enable you to determine what you need to remove.

$ dpkg -l | grep postgres
ii  pgdg-keyring                      2018.2                         all          keyring for apt.postgresql.org
ii  postgresql-15                     15~rc2-1.pgdg110+1             amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-client-15              15~rc2-1.pgdg110+1             amd64        front-end programs for PostgreSQL 15
ii  postgresql-client-common          243.pgdg110+1                  all          manager for multiple PostgreSQL client versions
ii  postgresql-common 

Then after you have identified what package to remove use the following command to accomplish that.

sudo apt-get --purge remove postgresql-15

It will remove PostgreSQL 15 with related packages.

Conclusion

Congratulations, we have successfully installed PostgreSQL 15 on Debian 11. For more information get the latest from PostgreSQL documentation.