‍

PostgreSQL, a free, open-source RDBMS (Relational Database Management System), is renowned for its extensibility and strict compliance with SQL standards. Alongside it, the pgAdmin tool, a user-friendly graphical administration platform, is widely used for PostgreSQL management. Together, these tools provide a robust, feature-rich environment for database management.

This guide will walk you through the process of installing the PostgreSQL database server and the pgAdmin4 PostgreSQL administration tool on a Debian 12 system. Additionally, we will explore the basic operation for creating a PostgreSQL database and user via the PostgreSQL shell (psql) and the pgAdmin4 administration tool.

Prerequisites

Before we begin, it’s important to ensure that you have the following:

1. A Debian 12 server.
2. A non-root user with administrator privileges.

Step 1: Installing PostgreSQL Database Server

PostgreSQL, an open-source and powerful object-relational database server, is renowned for its robust performance and features. It supports multiple operating systems, including Debian 12.

To install PostgreSQL on your Debian 12 server, we will use the official PostgreSQL repository. The following command will add the PostgreSQL repository to your Debian system.

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'

Next, add the GPG key of the PostgreSQL repository using the following command.

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

To refresh your available repositories and get the latest package information, use this command:

sudo apt update

Now, you can install the latest version of the PostgreSQL server using the apt install command:

sudo apt install postgresql

Once PostgreSQL is installed, verify that the postgresql service is running with the following command:

sudo systemctl status postgresql

If the service is running and enabled, it will start automatically at system boot.

Step 2: Configuring the Default PostgreSQL User

Upon successful installation of PostgreSQL, the first task is to configure the password for the default user ‘postgres’. This can be accomplished via the PostgreSQL shell.

Here’s how you can access the PostgreSQL shell:

sudo -u postgres psql

Now change the password for the PostgreSQL default user ‘postgres’ using the following command:

ALTER USER postgres WITH PASSWORD 'your_password' ;

Remember to replace ‘your_password’ with your own secure password.

To exit from the PostgreSQL shell, type ‘quit’.

Step 3: Creating PostgreSQL User and Database via psql

The next step is to create a PostgreSQL user and database via PostgreSQL shell (psql). The psql is a handy tool for interacting and managing the PostgreSQL server from the terminal.

To log in to the PostgreSQL shell, use the following command:

sudo -u postgres psql

You can create a new PostgreSQL user and password by executing the following queries:

CREATE USER your_userwith CREATE CREATEDB ROLE;
ALTER USER your_userwith PASSWORD 'your_password' ;

Replace ‘youruser’ and ‘yourpassword’ with your preferred username and secure password.

To verify the creation of the new user, use the following query:

du

Next, create a new database with your user as the owner:

CREATE DATABASE alice OWNER alice;
\l

Replace ‘user_database’ with your preferred database name.

To exit from the PostgreSQL shell, type ‘quit’.

Step 4: Installing the pgAdmin4 Administration Tool

The pgAdmin tool is an open-source administration and development platform for PostgreSQL. It includes both Desktop and Server versions. The Desktop version can be installed on your local machine, while the Server version is web-based and runs under a web server.

To install pgAdmin4 alongside the PostgreSQL server on your Debian 12 system, follow these steps.

First, add the pgAdmin4 repository to your system:

sudo echo "deb [arch=amd64 signed-by=/usr/share/keyrings/pgadmin4.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main"\
| sudo tee /etc/apt/sources.list.d/pgadmin4.list

Then, add the GPG key of the pgAdmin4 repository:

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

Refresh your Debian package index and install the pgadmin4-web package:

sudo apt update
sudo apt install pgadmin4-web

After installing pgAdmin4, you need to configure the admin user and password for your pgAdmin4 installation:

/usr/pgadmin4/bin/setup-web.sh

During the process, you will be asked to input your admin user and password for pgAdmin4, enable the wsgi module, and configure pgadmin4 to be accessible via path URL /pgadmin4.

Step 5: Accessing pgAdmin4

Once the setup is complete, you can access pgAdmin4 by launching your web browser and visiting the pgAdmin4 installation via the server IP address followed by the path /pgadmin4.

Step 6: Adding PostgreSQL Server to PgAdmin Administration Tool

After installing pgAdmin4, you can add your PostgreSQL server to the tool. To do so, click the Add New Server menu within the pgAdmin dashboard and fill in the necessary details.

Step 7: Creating a Database via PgAdmin4

You can also create a database directly from the pgAdmin4 dashboard. Simply right-click on the PostgreSQL server, select Create > Database, enter the database name and select the owner, then click Save.

Conclusion

By following this guide, you have installed the PostgreSQL database server and the pgAdmin4 administration tool on your Debian 12 system. You also learned how to create a database and user via the PostgreSQL shell (psql), connect to the PostgreSQL server via pgAdmin4, and create a database from the pgAdmin4 dashboard.

To make your experience even smoother, consider using Shape.host services. They offer ultra-fast Linux SSD VPS, perfect for running database servers like PostgreSQL. Their service ensures a high-performance, stable, and secure environment for your databases.

Christian Wells