How to Install PostgreSQL 14 on Debian 11/Ubuntu

PostgreSQL is also known as Postgres, an open-source and powerful objects-based relational database system that is used and combined with many features of SQL language. Using the Postgres database, you can easily store and scale the complicated data workload. Most of the mobile and web applications used the Postgres database for storing data. Postgres database can be installed on almost all operating systems including Linux.

Features of PostgreSQL 14

• Significant performance increases through parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming.
• OUT parameters can now be used to return data from stored procedures.
• JSON Conveniences and multiranges- enabling the representation of non-contiguous data ranges.
• Subscripting operators have been added to the jsonb and hstore types.
• Updates to B-tree indexes are more efficiently managed, resulting in less index bloat.
• Supports pipelined queries through libpq, which can significantly increase throughput over high-latency connections.
• Security enhancements

In this article we will guide you to install PostgreSQL 14 on Debian 11

Install of PostgreSQL on Debian 11

Step 1: Install Prerequisites

apt update && apt upgrade

Then proceed and install the required packages.

apt install -y curl apt-transport-https

apt install gpg

Step 2: Add repository of PostgreSQL

By default, the PostgreSQL packages are not included in the Debian 11 repository. However, you can install the required packages by adding the PostgreSQL repository to your Debian system. So, download or import the signing key of the PostgreSQL repository by using the following command:

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg

Add the PostgreSQL repository to your Debian 11 system by executing the below-mentioned command:

echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main" | tee /etc/apt/sources.list.d/postgresql.list

Step 3: Install PostgreSQL on Debian 11

Now install PostgreSQL 14 on Debian 11 use the command below.

apt update

apt install -y postgresql-14

Once the Postgres installation is completed, the service of PostgreSQL will automatically start on your system.

Verify the ‘Active’ or excited status of Postgres service by running the below-mentioned command:

systemctl status postgresql

If you did not receive the ‘Active’ status of the PostgreSQL service then, you can enable and start the PostgreSQL services by running the following command:

systemctl enable postgresql

systemctl start postgresql

Once the postgreSQL installation is completed on your system, it creates by default a user with the name ‘Postgres’ that you can find in the /etc/passwd file.

cat /etc/passwd | grep -i postgres

Step 4: PostgreSQL server configurations

The postgreSQL listens at the localhost port ‘127.0.0.1’ by default. but, you change it with your system IP address.\

nano /etc/postgresql/14/main/postgresql.conf

listen_addresses = 'IP-Address'

Now, restart the postgreSQL service on your system.

systemctl restart postgresql

Step 5: Login into the PostgreSQL database

Login as a ‘postgres’ user to the postgres database by using the below-mentioned command:

su - postgres

psql

Now, we can use the PostgreSQL database through the psql commands.

Step 6: User Management in PostgreSQL Database

Now that we have configured everything, let’s create a superuser for database management.

Connect to the PostgreSQL role.

Create a superuser, GreenCloud with password as Passw0rd as below.

CREATE ROLE GreenCloud WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';

Sample Output:

Manage application users

Create a database and grant user privileges to the database. In this guide, we will create a database with the name, Green and a user Green_user with password as “password” as below.

create database Green;

create user Green_user with encrypted password 'password';

grant all privileges on database Green to Green_user;

Sample Output:

Conclusion

We have successfully walked through how to install PostgreSQL 14 on Debian 11. In addition to that, we have made configurations to PostgreSQL 14. Good luck !