How to Install PostgreSQL on Debian 12 Server

May 21, 2024 May 21, 2024 9 min read

Hello geekandnix fellow, in this guide, I will show you how to install PostgreSQL on Debian 12 server.

I’ll cover the following:

How to install PostgreSQL on Debian (via the Debian repository and the official PostgreSQL repository)
How to secure PostgreSQL deployment
How to create a user, database, and table in PostgreSQL
Basic PostgreSQL queries for CRUD operations

With those, both system administrators and developers can benefit from this guide.

Let’s get started.

Prerequisites

Before you start, you need the following:

A Debian 12 server. Follow our guide to install Debian 12 Server.
A non-root user with sudo privileges. Follow our tutorial to setup and initialize Debian 12 server.

Installing PostgreSQL server via Debian repository

PostgreSQL is a free, open-source, high-performance, and advanced RDBMS (Relational Database Management System). On Debian, you can easily install it via APT, with the current available version of PostgreSQL 15.

To install PostgreSQL on Debian, follow these steps:

1. First, run the apt command below to update your Debian package index.

sudo apt update

2. Install PostgreSQL to your Debian server by executing the apt install command below. Enter Y to confirm the installation.

sudo apt install postgresql postgresql-contrib

3. After installation is complete, run the command below to verify PostgreSQL service status.

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

If PostgreSQL is enabled and running, you will see an output like the following:

4. Optional, run the ss command below to check PostgreSQL server port 5432.

ss -tulpn

If PostgreSQL running, you will port 5432 are used by the postgressql process:

5. Lastly, run the command below to check the PostgreSQL version.

sudo -u postgres psql -c "SELECT version();"

As seen in the following output, PostgreSQL 15 is installed via the Debian repository.

Installing PostgreSQL through official PostgreSQL repository

The second method to install PostgreSQL on Debian is via the official PostgreSQL repository. Thus provides multiple PostgreSQL versions, such as 16, 15, and 14. This scenario is suitable when you need to install a specific version of PostgreSQL, from the latest, stable, or old version.

You can install specific PostgreSQL server versions on Debian with the following:

1. To begin with, run the following command to install packages apt-transport-https, gnupg2, and lsb-release.

sudo apt install apt-transport-https gnupg2 lsb-release -y

2. Add the PostgreSQL GPG key and repository to your Debian server with the command below.

# adding GPG key for PostgreSQL repository
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/pgdg.gpg

# adding PostgreSQL repository
sudo sh -c 'echo "deb [arch=amd64 signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

3. Now, execute the following apt command to update the package list and install the PostgreSQL server.

# installing PostgreSQL 16
sudo apt update && sudo apt install postgresql-16 postgresql-client-16

Enter Y to confirm the installation.

4. After installation is finished, run the systemctl command below to start, enable, and verify the PostgreSQL service.

sudo systemctl enable --now postgresql
sudo systemctl status postgresql

The following output confirms that the PostgreSQL service is running.

Starting and verifying PostgreSQL service

5. Lastly, run the command below to check your PostgreSQL version.

sudo -u postgres psql -c "SELECT version();"

As you can see below, the PostgreSQL 16 is installed via the official PostgreSQL repository.

Checking PostgreSQL version from the command line

Securing PostgreSQL server installation

Securing your PostgreSQL deployment is your top priority after you have installed it. So you’ll ensure that you have a proper authentication method for your PostgreSQL server.

There are multiple host-based authentication methods for PostgreSQL, which can be configured via the pg_hba.conf file. Take a look at the PostgreSQL authentication methods below:

Trust: Always trust the connection from this host.
Password: Enable password authentication for this host.
Peer: For local user authentication.

Without delay, proceed with the following to secure PostgreSQL installation.

1. Open the /etc/postgresql/15/main/pg_hba.conf file using the vim editor.

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

Change the default password authentication for 127.0.0.1 to scram-sha-256 like the following:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Save and exit the file.

2. Now, run the systemctl command below to restart PostgreSQL and apply the new password authentication.

sudo systemctl restart postgresql

3. Next, execute the psql command below to login to the PostgreSQL server. The psql is a command line interface for connecting and managing the PostgreSQL server.

sudo -u postgres psql

4. Run the following queries to change the password for the postgres user and type quit to exit. Make sure to replace the StrongP4ssw0rd password with your new strong password.

# change postgres password
ALTER ROLE postgres WITH PASSWORD 'StrongP4ssw0rd';

# exit from PostgreSQL
quit

5. After you change the password, run the command below to connect to your PostgreSQL server. Enter your postgres password when prompted.

sudo -u postgres psql -U postgres -h localhost

6. Once logged in, run the query \conninfo to check your connection details to PostgreSQL.

\conninfo

You can see below that you’ve connected to PostgreSQL via postgres with the password authentication.

Logging in to PostgreSQL with password authentication

Creating user and database in PostgreSQL

After you’ve secured PostgreSQL, let’s move on to create a new user and database on PostgreSQL. Whether you’re a system administrator or developer, you must know how to manage users and databases in PostgreSQL for installing or deploying (to production) your applications.

To create a user and database in PostgreSQL, follow these actions:

1. First, run the following queries to create a new user alice with the password aliceP4ssword on your PostgreSQL server. Then, confirm the list of users via the \du or display user query.

# create a new user
CREATE USER alice WITH CREATEDB PASSWORD 'aliceP4ssword';

# display users / listing users
\du

You should see the user alice is created in PostgreSQL.

2. Secondly, run the following query to create a new database alice with the owner alice. Then, check the list of databases and exit from the PostgreSQL server.

# create database
CREATE DATABASE alice WITH OWNER alice;

# listing databases
\l

# exit
quit

4. Next, run the following query to log in to PostgreSQL with the new user alice to the database alice. Enter the password for user alice when prompted.

sudo -u postgres psql -U alice -h localhost -d alice

5. After logging in, execute the query \conninfo to check the detailed connection to the PostgreSQL server.

\conninfo

As you can see below, you’ve connected to PostgreSQL via user alice to the default database alice.

Logging in to PostgreSQL with specific user and database

Creating a table in PostgreSQL

Now that you’ve created the user and database, the next step is to create a new table in PostgreSQL. Complete these steps to create a table in PostgreSQL:

1. To start, run the following queries to create a new database mydb in PostgreSQL.

CREATE DATABASE mydb;

2. Now, run the \connect query to switch to the new database mydb.

\connect mydb;

Creating new database mydb and switch to the mydb database

3. Next, run the CREATE TABLE query below to create a table company with columns id, name, age, and address.

CREATE TABLE company(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50) NOT NULL
);

4. Lastly, run the following queries to check and describe the table in PostgreSQL.

# listing tables
\dt

# describe table company
\d company

You will see table company with columns id, name, age, and address in the database mydb.

Listing table and describe table in PostgreSQL

Basic CRUD operations in the PostgreSQL server

So now you’ve created a table company, move to basic CRUD operations and queries in PostgreSQL. A CRUD or Create, Read, Update, and Delete are the basic operations for applications. Developers must know CRUD queries when developing applications using PostgreSQL.

Learn basic CRUD (Create, Read, Update, and Delete) operations in PostgreSQL with the following actions:

1. Insert new data to the table company with the INSERT query below.

INSERT INTO company(name, age, address) VALUES ('alice', 30, 'Texas'), ('joana', 20, 'New York'), ('bob', 25, 'California');

Once data is added, you should get an output INSERT 0 2.

2. Now, run the SELECT query below to retrieve data from the table company. With the * character, you will retrieve data from all available columns, while specific column names such as name and age will show you the data from those specific columns.

SELECT * FROM company;
SELECT name, age FROM company;

Retrieve data using SELECT query in PostgreSQL — Retrieve data using SELECT query

3. Next, run the UPDATE query below to update data in PostgreSQL. Followed by the SET with the new data and the WHERE clause to specify your target data. Once data is updated, you will see an output UPDATE 1.

UPDATE company SET age = 25 WHERE name = 'alice';

4. Now, run the SELECT query below to verify your updated data. In this example, you can see the age for alice is changed to 25.

SELECT * FROM company WHERE name = 'alice';

Update data with the UPDATE query in PostgreSQL

5. Lastly, run the DELETE query followed by the WHERE clause to delete data in PostgreSQL.

DELETE FROM company WHERE id = 3;

Once data is deleted, you should get an output DELETE 1.

Conclusion

By completing this tutorial, you’ve installed PostgreSQL on Debian 12 server (via the Debian repository and the PostgreSQL repository). You’ve also learned how to secure PostgreSQL via authentication, creating user, database, and table in PostgreSQL. Furthermore, you’ve learned basic CRUD (Create, Read, Update, and Delete) operations and queries in PostgreSQL.

From here, you can continue to deploy your PostgreSQL-based applications or develop your applications using PostgreSQL as the database.

ArvidL

System administrator and devops enthusiast, leveraging over 10+ years of Linux expertise to optimize operations. Proficient in FreeBSD, VMWare, KVM, Proxmox, PfSense, Ansible, Docker, and Kubernetes.