How to Install Postgresql on Debian 12?

Postgresql is a powerful as well as open-source database management system. It is widely used for data analysis, web development, and business applications. Debian 12 is known for its reliability, security, and stability. It offers many improvements and new features, such as enhanced support for cloud computing, containers, and virtualization. Postgresql is a great choice for Debian 12 users who require a robust and scalable database system to deal with complex queries and data types.

This article will demonstrate different methods for the installation of PostgreSQL on Debian 12. Also, cover some basic commands to manage the PostgreSQL service and create a database and a user.

How to Install Postgresql on Debian 12?
How to Install pgAdmin on Debian 12?
How to Use Postgresql on Debian 12?

Let’s begin with the Postgresql installation.

How to Install Postgresql on Debian 12?

Installing PostgreSQL on Debian 12 is a simple process that can be done using the official Debian repositories or the PostgreSQL apt repository.

Method 1: U sing PostgreSQL apt Repository
Method 2: Using the Debian Default Repository

Let’s start with the first method.

Method 1: Install Postgres on Debian 12 Using PostgreSQL apt Repository

This method always gets the latest stable version of PostgreSQL for your Debian system. To install it on Debian 12, follow stated steps:

Step 1: Add the Postgresql Repository

Before installing postgresql, users need to add the Postgresql repository to Debian system via the below command:

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

Step 2: Import the GPG Key

After that, use the “wget” command to import the GPG key with the help of a specific URL. Also, add the key of the Postgresql repository with the “sudo” privileges:

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

Step 3: Update Package Lists

It is necessary to update the system’s package lists via the below conventional command:

sudo apt update

It makes the system ready to install the Postgres packages with dependencies.

Step 4: Install PostgreSQL Server (Latest Package)

Now, install the most recent version of Postgresql by specifying the “postgresql” package/command with the “sudo” privileges:

sudo apt install postgresql

Optional: Install PostgreSQL (Specific Version)

To install specific PostgreSQL version, use the “postgresql” package by mentioning its version:

sudo apt install postgresql-<version>

Step 5: Verify Postgresql Service

After the complete installation, verify that the Postgresql service is running and enabled from the below “systemctl” commands:

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

Note: If users find any issue, they can start and enable the PostgreSQL service by executing the below script:

systemctl start postgresql # Start PostgreSQL service

systemctl enable postgresql # Enable PostgreSQL service

Step 6: Switch the Postgres User

By default, PostgreSQL creates a system user named “postgres” and a database “user” with the same name. Let’s switch this system user:

sudo su - postgres

Step 7: Access PostgreSQL Shell

Now, use the “postgres” user to access the PostgreSQL shell and perform administrative tasks:

psql

Users can now execute SQL commands in the PostgreSQL shell. Exit the Postgresql shell by specifying the “\q” command and pressing Enter.

Optional: Set Password

Users can set/assign a password to the “postgres” (default Postgresql user) with the help of below script:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_password';"

Users can also install additional packages that provide tools and libraries for working with Postgresql, such as “postgresql-contrib”, “libpq-dev”, “pgadmin4”, etc.

So, the installation of postgresql has been done on Debian 12.

Optional: Remove PostgreSQL

To remove the PostgreSQL from Debian 12, use the “remove” utility with the apt command:

sudo apt remove postgresql # Remove (Only Packages)
sudo apt purge postgresql # Remove (Packages and Dependent Files)
sudo apt autoremove postgresql # Remove (Packages, Dependent and Temporary Files)

In this way, postgresql is uninstalled on Debian 12.

Method 2: Install Postgres on Debian 12 Using Debian Default Repository

The Debian default repository contains the PostgreSQL packages that are maintained by the Debian community. These packages are usually stable and secure, but they may not be the latest version of PostgreSQL available.

To install PostgreSQL from the Debian default repository, follow these steps:

Step 1: Update System Packages List

First, update the repository and download/install the latest packages/utility:

sudo apt update && sudo apt upgrade

Note: This step is necessary to make the (Debian) system ready for installation of the PostgreSQL server package.

Step 2: Install PostgreSQL

Now, install the PostgreSQL package and its dependencies with the “apt” command. In this way, the PostgreSQL service starts automatically after the installation:

sudo apt install postgresql

Step 3: Check PostgreSQL Services

Now, users can check its status and verify that PostgreSQL is running and enabled via the command:

sudo systemctl is-enabled postgresql

sudo systemctl status postgresql

Step 4: Switch to postgres User

Finally, switch to the (above-created) postgres user with the “sudo -u” command. Then, enters into the PostgreSQL shell via the “psql”:

sudo -u postgres

psql

In this way, create databases, tables, users, and perform various operations with PostgreSQL. To exit the shell, type “\q” and press Enter.

PostgreSQL has been successfully installed on Debian 12 using the Debian default repository.

How to Install pgAdmin on Debian 12?

You can also use other tools to interact with PostgreSQL, such as pgAdmin, a graphical user interface for PostgreSQL administration.

Step 1: Install the Public Key

First, use the “curl” command to import the GPG key with the help of a specific URL:

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

Note: If curl is not installed, use the “sudo apt install curl” command or the below commands with dependent packages (ca-certificates, gnupg) as well:

sudo apt install curl ca-certificates gnupg

Step 2: Create a Repository

Then, users need to add the Postgresql repository to Debian system as well as update package list via the below command:

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Step 3: Install pgAdmin

To install pgAdmin on Debian 12, use the “pgadmin4” package with the “sudo” privileges:

sudo apt install pgadmin4

Optional: For Desktop and Web Mode

Users can install pgadmin for desktop as well as web mode via the below commands:

sudo apt install pgadmin4-desktop # Desktop mode

sudo apt install pgadmin4-web # Web mode

Verification

To launch pgAdmin, run pgadmin on terminal or use the “Activities” icon and type the “pgadmin” in search field:

In this way, users can create databases and users, and access them using the psql command or a graphical tool like pgAdmin.

How to Use Postgresql on Debian 12?

After installing Postgresql on Debian 12, users are required to perform configuration based on their preferences. You can use the “pg_ctl” command to start, or restart, or stop the Postgresql server. Also, use the “psql” command to connect to the Postgresql server and execute SQL commands:

Create a New Database

Users can create a new database with the <CREATE DATABASE db_name;> command. For instance, create a “db_linuxgenie” database:

CREATE DATABASE db_linuxgenie;

Create a New Database User

For instance, create a new database user with the <CREATE USER username WITH PASSWORD ‘password’;> command. For instance, create the “debian_user” as below:

CREATE USER debian_user WITH PASSWORD '1212qwqw';

Grant Privileges to User on a Database

To grant privileges to a user on a database, use the “<GRANT ALL PRIVILEGES ON DATABASE dbname TO username;>” command. For instance, access the “db_linuxgenie” privileges to “debian_user” as below:

GRANT ALL PRIVILEGES ON DATABASE db_linuxgenie TO debian_user;

To exit the PostgreSQL shell, type “\q” and press Enter.

Additional: Users can use the “postgresql.conf” file to modify the Postgresql server settings, such as port number, memory allocation, logging level, etc. Also, use the “pg_hba.conf” file to control the access permissions to the Postgresql server from different hosts and users.

To explore more, navigate to the official PostgreSQL documentation.

Conclusion

Debian 12 also comes with a large number of software packages that are updated to their latest versions. To install PostgreSQL on Debian 12, update the system packages, and install the PostgreSQL package. In addition, start and enable the PostgreSQL service.

The Debian default repository method is recommended for most users, as it provides a stable and secure version of PostgreSQL that is compatible with the Debian system. If users require to install a newer version of PostgreSQL, use the PostgreSQL official repository.