Install PostgreSQL 15 on Debian 11: Free Database Engine

In this article, we will guide you through the process of How To Install PostgreSQL 15 on Debian 11. PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its reliability, feature robustness, and adherence to standards. It’s available for free under an open-source BSD license. PostgreSQL version 15 is the latest major release of PostgreSQL. It introduces several new features and improvements that enhance performance, security, and developer experience.

You can follow the steps below to start the Install PostgreSQL 15 on Debian 11 setup on Debian 11.

Steps To Install and Configure PostgreSQL 15 on Debian 11

Before you begin, ensure you’re logged into your Debian 11 server as a non-root user with sudo privileges. You can achieve this by following our guide on Initial Server Setup with Debian 11. This ensures you have the necessary permissions to install and configure PostgreSQL.

1. PostgreSQL 15 Setup on Debian 11

First, update your local package index to ensure you have the latest package information.

sudo apt update

Install Required Packages

Next, install the packages required to manage repositories and download packages over HTTPS.

sudo apt install dirmngr ca-certificates software-properties-common gnupg gnupg2 apt-transport-https curl wget -y

Import PostgreSQL GPG Key

To ensure the integrity of the packages you’re installing, import the PostgreSQL GPG key.

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

Import PostgreSQL Repository

Now, import the PostgreSQL 15 repository. This tells your system where to find the PostgreSQL 15 packages.

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

Install PostgreSQL 15 on Debian 11

Update your local package index again to include the newly added repository.

sudo apt update

Finally, install PostgreSQL 15 using the following command:

sudo apt -y install postgresql

Note: If you want a specific version (e.g., PostgreSQL 12), use ‘postgresql-12’ instead of ‘postgresql’.

By default, PostgreSQL should be activated during installation. Verify this with:

sudo systemctl status postgresql

If PostgreSQL isn’t active, enable and start it:

sudo systemctl enable postgresql --now

2. Manage PostgreSQL Service

The PostgreSQL database server runs as a service named ‘PostgreSQL’. You can manage it using systemd commands.

To start the PostgreSQL server:

sudo systemctl start postgresql

To stop the PostgreSQL server:

sudo systemctl stop postgresql

To restart the PostgreSQL server:

sudo systemctl restart postgresql

To reload the PostgreSQL server:

sudo systemctl reload postgresql

To check the PostgreSQL status:

systemctl status postgresql

3. Configure PostgreSQL Database on Debian 11

PostgreSQL uses roles for authentication and authorization. By default, PostgreSQL uses ‘ident’ authentication, associating Postgres roles with matching Unix/Linux system accounts. A Unix/Linux username with the same name can sign in as that role if a role exists within Postgres.

One way to interact with PostgreSQL is to switch to the ‘postgres’ account:

su postgres

**Output**
postgres@olivia:~$

You can then access the Postgres shell:

postgres@olivia:~$ psql

The shell prompt will change to:

To exit the PostgreSQL shell:

postgres=# q

This returns you to the Postgres Linux command prompt:

postgres@olivia:~$

Type exit to return to the regular system user.

postgres@olivia:~$ exit

Create a new PostgreSQL 15 Role

You can create a new Postgres role in two ways. While logged in as the ‘postgres’ account, use the interactive createuser command:

postgres@olivia:~$ createuser --interactive

You’ll be prompted for the role name and whether it should be a superuser.

**Output**
Enter name of role to add: orca
Shall the new role be a superuser? (y/n) y

Create a new PostgreSQL 15 Database

For any role to log in, it needs a database with the same name. Logged in as the ‘postgres’ account, create the database:

postgres@olivia:~$ createdb orca

Open the Postgres 15 Shell with the new Role

Create a Linux user with the same name as the Postgres role and database:

sudo adduser orca

Now connect to the Postgres database:

# su - orca
# orca@:~$ psql

Once logged in, check your connection information:

orca=# conninfo

Your output will show:

PostgreSQL current connection information

For more information, visit the PostgreSQL Documentation page.

Conclusion

You have now learned how to Install PostgreSQL 15 on Debian 11. Using PostgreSQL 15 on Debian 11 provides a stable system with the latest database features, ideal for modern applications.

Alternative Installation Methods for PostgreSQL 15 on Debian 11

While the APT method is standard, here are two alternative approaches to install PostgreSQL 15 on Debian 11. These methods cater to different needs, such as those who prefer containerization or want to compile from source.

1. Using Docker

Docker allows you to run PostgreSQL in a container, providing isolation and portability. This is especially useful for development environments or when you need to manage multiple PostgreSQL versions.

Explanation:

Docker containers encapsulate the PostgreSQL server and its dependencies, eliminating conflicts with the host system’s libraries. This also makes upgrades and migrations easier. This approach isolates the database and makes it easier to move between environments.

Steps:

Install Docker: If Docker isn’t already installed, follow the official Docker documentation for Debian.
```
sudo apt update
sudo apt install docker.io -y
sudo systemctl start docker
sudo systemctl enable docker
```
Pull the PostgreSQL 15 Docker Image: Obtain the official PostgreSQL 15 image from Docker Hub.
```
sudo docker pull postgres:15
```
Run the PostgreSQL 15 Container: Start a container with appropriate configurations (port mapping, volume mounting for data persistence, environment variables for initial setup).
```
sudo docker run --name postgres15 -e POSTGRES_USER=myuser -e POSTGRES_PASSWORD=mypassword -e POSTGRES_DB=mydb -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres:15
```
- --name postgres15: Assigns the name "postgres15" to the container.
- -e POSTGRES_USER=myuser: Sets the PostgreSQL user.
- -e POSTGRES_PASSWORD=mypassword: Sets the PostgreSQL password.
- -e POSTGRES_DB=mydb: Sets the initial database name.
- -p 5432:5432: Maps the host’s port 5432 to the container’s port 5432.
- -v pgdata:/var/lib/postgresql/data: Creates a named volume "pgdata" for persistent data storage.
- -d postgres:15: Runs the container in detached mode (background).
Access PostgreSQL: Connect to the PostgreSQL server running in the container using psql or any other PostgreSQL client.
```
psql -h localhost -U myuser -d mydb -p 5432
```

2. Compiling from Source

Compiling from source gives you maximum control over the build process and allows you to optimize PostgreSQL for your specific hardware and software configuration. This method is more complex and time-consuming but can be beneficial in certain scenarios.

Explanation:

Building from source allows for specific compilation flags and optimization based on the server’s hardware. It is especially useful when needing to apply custom patches or use bleeding-edge features not yet available in pre-built packages. However, dependency management and maintenance become your responsibility.

Steps:

Install Build Dependencies: Ensure you have the necessary build tools and libraries.

sudo apt update
sudo apt install build-essential libreadline-dev zlib1g-dev flex bison libssl-dev libxml2-dev xsltproc -y

Download the Source Code: Download the PostgreSQL 15 source code from the official PostgreSQL website or a mirror.

wget https://ftp.postgresql.org/pub/source/v15.0/postgresql-15.0.tar.gz
tar -xzf postgresql-15.0.tar.gz
cd postgresql-15.0

Configure the Build: Use the configure script to prepare the build environment. You can customize the installation path and other options.
```
./configure --prefix=/usr/local/pgsql15
```
Build and Install: Compile the source code and install PostgreSQL.
```
make
sudo make install
```

Set Up Environment: Add the PostgreSQL binaries to your PATH and configure the data directory.

export PATH=$PATH:/usr/local/pgsql15/bin
sudo mkdir /usr/local/pgsql15/data
sudo chown postgres:postgres /usr/local/pgsql15/data
sudo -u postgres /usr/local/pgsql15/bin/initdb -D /usr/local/pgsql15/data

Start the Server: Start the PostgreSQL server.

sudo -u postgres /usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data -l logfile start

These alternative methods offer flexibility in deploying Install PostgreSQL 15 on Debian 11, catering to various use cases and technical requirements.