PostgreSQL is an open-source object rational and SQL database management system focusing on data integrity and reliability. Debian on the other hand is also a reliable and stable Linux distribution, which makes it suitable for creating and managing databases using PostgreSQL. 

This tutorial will explain several ways to install the PostgreSQL database management system on Debian 12. Also, some basic tasks in PostgreSQL will be explained along with the installation of pgAdmin4, an administration and development platform specifically designed for managing PostgreSQL databases.

3-Ways to Install PostgreSQL on Debian 12

PostgreSQL is a client-server model, where the server manages the database file and handles connections from the client applications. PostgreSQL stores data in the form of tables by using numerous indexing and storage methods. There are primarily three ways to install PostgreSQL in Debian 12.

1: Through Debian Apt Package Manager

To install PostgreSQL on Debian one way is by using its default repository, but the default repository comes with version 15 of PostgreSQL. Use the below command to install PostgreSQL on Debian:

sudo apt install postgresql postgresql-contrib

Here, the postgresql-contrib installs some extra packages for PostgreSQL that enhance its functionality:

Next, to see the version of PostgreSQL installed on Debian use the below command to display details information on PostgreSQL:

dpkg -s postgresql

Here, the number 15 represents the major version of PostgreSQL, while the +248 indicates additional revisions, patches, or build numbers that have been applied on top of the major version.

Further, to switch to the PostgreSQL user, execute the below command:

sudo -i -u postgres

2: Manual PostgreSQL Installation on Debian Using PGDG Repository

Another way to install PostgreSQL is by adding and configuring its official repository on Debian. The repository comes with version 16 and later versions of PostgreSQL. To add the PostgreSQL repository on Debian you first need to create the PGDG directory:

sudo install -d /usr/share/postgresql-common/pgdg

Next, download a file named apt.postgresql.org.asc from the PostgreSQL website and save it to the /usr/share/postgresql-common/pgdg directory on Debian:

sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

Here, the –fail flag ensures the command exits with an error if the download fails for any reason:

Next, add the PostgreSQL repository in the source.list directory of Debian 12 signed by the file downloaded in the previous step:

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Now update the Debian 12 default installer packages list to successfully save the PostgreSQL repository:

sudo apt update

Now install PostgreSQL on Debian by running the below command:

sudo apt install postgresql -y

Next, to verify you can move to the PostgreSQL shell on Debian by using the following command after switching to PostgreSQL user:

psql

This shell allows you to execute SQL queries, and to access and manage the database metadata and objects. Here, from the image below, you can see that the client version 16.3 is installed:

3: Automated PostgreSQL Installation on Debian Using PGDG Repository

The previous method contained several steps, so if you want the PostgreSQL to automatically configure, then try this method. First, install the common files associated with PostgreSQL server on Debian:

sudo apt install -y postgresql-common

Next, execute the apt.postgresql.org.sh file so that its repository can configure automatically on Debian:

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Next, update the apt packages list to save the changes to its repository:

sudo apt update

Now, you can install the PostgreSQL client version of your choice, here I have installed version 16 which is the latest one that is available to install. However, the newer version 17 has also been released.

sudo apt install postgresql-client-16 -y

Now to install the server version of PostgreSQL, execute the command below:

sudo apt install postgresql-16 -y

Configuring PostgreSQL on Debian 12

The configuration of PostgreSQL on Debian does not really include doing extensive settings, just check the service using the sysytemctl utility:

sudo systemctl status postgresql

If the service is inactive or disabled, then execute the below command:

sudo systemctl start postgresql && sudo systemctl enable postgresql

Another setting that you can do is setting a password for the PostgreSQL user account, which can help to restrict unauthorized access:

sudo passwd postgres

To see the list of all the database saved on PostgreSQL, execute the below command in the PostgreSQL shell:

\l

How to Install PgAdmin4 for PostgreSQL on Debian 12

Pgadmin4 is an open-source visual management and SQL query tool that is designed for PostgreSQL administration. It provides multiserver support for PostgreSQL, which helps the database administrators to oversee multiple databases. To install pgAdmin4 on Debian, first download its GPG key for package validation:

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

Next, add pgAdmin4 repository signed by the GPG key added previously in the sources list directory:

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'

Now update the list of repositories in the apt package manager:

sudo apt update

PgAdmin4 comes with a desktop version and a web version, so if you need both versions on Debian 12 then use the below command:

sudo apt install pgadmin4

To install the web version of pgAdmin4, use the below command:

sudo apt install pgadmin4-web

For the desktop mode installation of pgAdmin4 on Debian, use the following command:

sudo apt install pgadmin4-desktop

You can launch the desktop version by searching pgAdmin4 in the Debian’s app menu:

For the web version, you need to execute the below command as it will set up the web version by executing the setup version sh file:

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

While setting up the web version, you will have to enter the password and email address which you will use as login details:

To access pgAdmin4 on web use your system IP address as in the syntax below:

[ip-address]/pgadmin4

Here, enter the login credentials added during the web version setup:

Here is the basic interface of the pgAdmin4 web version on Debian:

Now to add a server, for instance, PostgreSQL click on Add New Server:

Next, add the relevant details in each tab and after that click on Save:

Creating a Database in PostgreSQL on Debian

The main purpose of PostgreSQL is creating databases and managing them, so to create a database switch to PostgreSQL user and in the psql shell environment use the below syntax for creating a database:

CREATE DATABASE [databas-name];

Further, you can list all the databases in PostgreSQL by executing the following command in psql shell:

\l

Also, if you want to shift to any database for adding tables, users or any other sort of data in PostgreSQL then use the below syntax:

\c [database-name]

Creating a Table in PostgreSQL in Debian12

Most of the time data is saved in tabular form as one can add multiple entries of the data under the same category. Tables keep data organized and easy to find the irregularities if any. To create a table on PostgreSQL on Debian, use the below syntax. You can further make changes in it as this as it is just for educational purposes:

CREATE TABLE [table-name] (

[Item_ID] serial PRIMARY KEY,

[data-1] VARCHAR ( 50 ) NOT NULL,

[data-2] VARCHAR ( 50 ) NOT NULL,

[data-3] VARCHAR ( 255 ) UNIQUE NOT NULL,

[data-4] VARCHAR ( 50 ) NOT NULL);

Now once the table is created successfully you will see CREATE TABLE at the end:

Further, to verify, you can list the relations (tables) by executing:

\dt

The next thing to do after creating a table in PostgreSQL is to add information to it for that use the below syntax:

INSERT INTO [Table-Name]

VALUES ( detail-1, 'detail-2', 'detail-3', 'detail-4', detail-5);

Here, I have added the sale record of a car care product:

To verify the details added in the table, view the details by running:

SELECT *

FROM [Table-Name];

Moreover, to list the entries in the PostgreSQL table in Debian, use the below syntax:

\d [Table-Name]

If you want to delete a table from PostgreSQL, then use the DROP TABLE command along with the name of the respective table:

DROP TABLE [Table-Name];

Creating a Role in PostgreSQL in Debian 12

One of the most common tasks on PostgreSQL is creating a user, as most of the time it is necessary to assign different permission levels if multiple user have to access the database.

To create a user in PostgreSQL, the best way is to create a user in the PostgreSQL shell. If you want to create a user using the PostgreSQL user, then it must be added to the sudoers file. To have detailed info on that read my detailed article on how to add a user to sudoers file in Debian:

sudo -u postgres createuser [name]

So, it is better to create a user on psql shell environment and for that run the following command:

CREATE USER [name];

Alternatively, you can also add the password for the PostgreSQL user, and for that use the below syntax:

CREATE USER [name] WITH PASSWORD '[password]';

To list down users along with their assigned permissions execute the below command:

\du

To assign certain permissions to the users on PostgreSQL using the ALTER ROLE command as in the syntax below:

ALTER ROLE "[user-name]" WITH LOGIN [attribute-1] [attribute-2] ……[attribute-n];

Here, I have assigned a user named Shane permission to create roles and databases on PostgreSQL on Debian:

Note: To move back to the shell after listing anything in psql press the q key.

Here, I have listed some basic commands used in PostgreSQL:

Conclusion

To install PostgreSQL on Debian there are three ways which include using an apt package manager, and manual or automatic configuration of the PostgreSQL repository. Further, to visually manage PostgreSQL on Debian, install the pgAdmin4 through its official repository. To create a user use the CREATE USER command, for creating a table use CREATE TABLE, and for the database use the CREATE DATABASE command in PostgreSQL.