How to Install PostgreSQL 16 and pgAdmin on Debian 12

Introduction

Before we discuss how to install PostgreSQL 16 and pgAdmin on Debian 12, let's first understand-What is PostgreSQL and pgAdmin?

PostgreSQL is a powerful, highly scalable, open source, and cross-platform object-relational database system that runs on Unix-like operating systems including Linux and Windows OS. It is an enterprise-level database system that is highly reliable and offers data integrity and correctness to users.

pgAdmin is a management tool for PostgreSQL and derivative relational databases, such as EnterpriseDB's EDB Advanced Server.

This tutorial will provide instructions on how to install PostgreSQL 16 and pgAdmin on Debian 12. We will also address a few FAQs on how to install PostgreSQL 16 and pgAdmin on Debian 12.

Advantages

1. Advanced Features: PostgreSQL offers a wide range of advanced features such as support for JSON, geospatial data, full-text search, and extensibility through custom data types and functions. pgAdmin provides a comprehensive graphical interface to interact with and utilize these features.
2. Reliability and Stability: PostgreSQL is known for its stability, reliability, and ACID compliance. It has a strong reputation for data integrity and is widely used in production environments.
3. Performance: PostgreSQL is highly optimized for performance, providing efficient query execution, support for indexing, parallel processing, and advanced query optimization techniques.
4. Robust Security: PostgreSQL offers various security features, including access control mechanisms, SSL encryption, authentication methods, and the ability to restrict connections. pgAdmin provides an intuitive interface for managing database users, roles, and permissions.
5. Community and Ecosystem: PostgreSQL has a thriving open-source community with active development and continuous improvement. There is a wide range of community-contributed extensions and tools available, allowing users to extend the functionality of PostgreSQL and pgAdmin.

1. Updating Debian System

Before installing PostgreSQL, make sure to update your Debian package list and upgrade existing packages using the following apt commands.

sudo apt update 
sudo apt upgrade

2. Install PostgreSQL 16 on Debian

Install the PostgreSQL 16 server with the following command.

sudo apt install postgresql postgresql-contrib -y

The PostgreSQL data directory /var/lib/postgresql/16/main/ contains all the data files for the database.

4. Manage PostgreSQL Service

To start, enable, and check the status of the PostgreSQL service, use the following commands.

sudo systemctl start postgresql.service 
sudo systemctl enable postgresql.service 
sudo systemctl status postgresql.service

5. Verify PostgreSQL Installation

After installing the PostgreSQL database system on your server, verify its installation by connecting to postgres database server. The PostgreSQL administrator user is named as postgres, type this command to access the user system account.

sudo su postgres 
cd 
psql

6. Set Postgres User Password

To set a password for the postgres database administrator user, use the following command.

\password postgres

After running the above command, you will be prompted to enter the new password.

Once you’ve set the password, exit the PostgreSQL prompt.

\q exit

7. Install pgAdmin in Debian

pgAdmin is a popular graphical user interface (GUI) for managing PostgreSQL databases and it is used for creating, editing, and managing databases, tables, users, and other database objects.

To install pgAdmin, you need to install the public key for the repository with the following curl command.

# Setup the repository
#
 apt install gpg
# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

# Create the repository configuration file:
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'

#
# Install pgAdmin
# Install for both desktop and web modes:
sudo apt install pgadmin4

Afterward, you’ll have to execute a web setup script to configure the pgadmin4 for web mode operation, as shown.

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

Now, launch your web browser and enter the following URL to reach the pgAdmin4 login web interface.

http://your-server-ip/pgadmin4 
OR 
http://localhost/pgadmin4

Enter your email address and password, then click the Login button to view the pgAdmin4 dashboard page.

FAQs to Install PostgreSQL 16 and pgAdmin on Debian 12

Conclusion

You have now successfully installed PostgreSQL 16 and pgAdmin on Debian 12. You can now start using pgAdmin to manage your PostgreSQL databases.

If you have any queries, feel free to ask them in the comments section and, we will be happy to respond to them...