PostgreSQL or Postgres is a powerful high-performance object-relational database management system (ORDBMS) released under a flexible BSD-style license. PostgreSQL is well-suited for large databases and has many advanced features.
pgAdmin4 is an open-source PostgreSQL management tool designed for multiple PostgreSQL database versions. pgAdmin4 has been created with all of the features found on the PostgreSQL server. Written in Python and jquery and can be installed on Windows, Mac, and Linux. It provides multiple deployment models, and can be installed as a desktop application or a server application running behind the webserver, such as Apache2.
This tutorial will show you how to install the latest PostgreSQL database and the pgAdmin4 on Debian 10. We will install PostgreSQL on the Debian Buster and then configure the password for the default ‘postgres’ user. And after that, we will install and configure the pgAdmin4 PostgreSQL management tool.
Prerequisite
We will install PostgreSQL and pgAdmin for this guide on the Debian Buster 10 with 1GB of RAM and 2 CPUs. Also, you need root privileges for the installation and the basic Linux command and PostgreSQL queries.
What we will do:
- Install Dependencies
- Add PostgreSQL Repository
- Install and Configure PostgreSQL on Debian 10
- Install pgAdmin4 on Debian 10
- Testing
Step 1 – Update Packages and Install APT Dependencies
Firstly, we will update and upgrade all packages to the latest version and install additional apt dependencies.
Update and upgrade all packages on Debian 10 using the apt command below.
sudo apt update sudo apt upgrade
Once all packages have been updated, you can install additional APT packages as below.
sudo apt install -y curl ca-certificates gnupg
Now go to the next step.
Step 2 – Add PostgreSQL Repository
In this step, we will add the official PostgreSQL repository to our system. All packages, including the PostgreSQL itself and the pgAdmin4, is available on the official PostgreSQL repository.
Before adding the PostgreSQL repository, we need to add the key at first. Download the PostgreSQL key and add it to the system using the ‘apt-key add’ command as below.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
After that, add the PostgreSQL repository to the Debian 10 system using the following command.
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee -a /etc/apt/sources.list.d/pgdg.list
Now update all available repositories on the system.
sudo apt update
And below is the result.
And as a result, the PostgreSQL key repository has been added. And you’re ready to install the PostgreSQL and pgAdmin packages.
Step 3 – Install and Configure PostgreSQL on Debian 10
Install the latest PostgreSQL database using the apt command below.
sudo apt install postgresql postgresql-common postgresql-contrib
Once the installation is complete, start the PostgreSQL service and add it to the system boot.
systemctl start postgresql systemctl enable postgresql
The PostgreSQL service is up and running on Debian 10.
Next, we will add a password for the PostgreSQL user ‘postgres’.
Login as a ‘postgres’ user and access the PostgreSQL command-line interface using the ‘psql’ command below.
su - postgres psql
Create a new password for the ‘postgres’ user using the following Postgres query.
\password postgres PASSWORD
Now type ‘exit’ or ‘Ctrl+d’ to exit from the shell.
As a result, the latest PostgreSQL database has been installed on the Debian 10 system. And the password for ‘postgres’ user has been updated.
Step 4 – Install pgAdmin4 on Debian
This step will install the pgAdmin4 PostgreSQL management tool to our Debian system.
Install the pgAdmin4 packages and dependencies using the apt command below.
sudo apt install pgadmin4 pgadmin4-apache2
The command will automatically install the Apache2 web server and add the additional Apache2 configuration for the pgAdmin4 application.
During the installation, you will be asked for an initial email address for the pgAdmin4 login. Type your own email on the box and choose ‘OK’ to continue.
After that, you will be asked for the initial password of the pgAdmin4. Type your strong password and choose ‘OK’ to continue.
And the pgAdmin4 installation on Debian 10 has been completed.
The pgAdmin4 is running under the Apache webserver on default HTTP port 80. Check the ‘LISTEN’ port on the system and you will get the port ’80’ has been used by the Apache2 service.
netstat -plntu
Next, check the ‘pgadmin4’ configuration inside the Apache2 directory. Go to the ‘/etc/apache/conf-enabled/’ directory and print the ‘pgadmin4.conf’ configuration file.
cd /etc/apache/conf-enabled/ cat pgadmin4.conf
And you will get the result as below.
As a result, you will see the pgAdmin4 is running as a uWSGI daemon and accessible through on the URL path ‘/pgAdmin4’.
Now the pgAdmin4 installation on Debian 10 has been completed successfully.
Step 5 – Testing
Open your web browser and type the server IP address and the URL path ‘/pgadmin4’ on the address bar.
http://10.5.5.15/pgadmin4
And you will get the pgAdmin4 login page.
Type your email address and the password that was initialized during the installation, then click the ‘Login’ button.
Once logged in to pgAdmin4, you will get the dashboard as below.
After that, we must add our PostgreSQL server to the pgAdmin4 management tool.
On the pgAdmin4 dashboard, click the ‘Add New Server’ button.
On the ‘General’ tab, type the new server’s name. Type details about your PostgreSQL server on the ‘Connection’ tab, including Host/IP Address, Username, and Password.
Now click the ‘Save’ button.
Once the PostgreSQL server is added, you will get the details about your PostgreSQL server on the left menu.
As a result, the installation and configuration of PostgreSQL and pgAdmin4 on Debian 10 have been completed successfully.