pgAdmin is a free and open-source graphical administration tool for PostgreSQL which is easy to use. It supports PostgreSQL 9.6 and above, and it can be run on multiple operating systems, including Windows, macOS, and Linux.
Unlike other graphical database administration tools, the pgAdmin can be run on 3 different modes:
- Desktop mode – can be installed as a user application on your laptop and computer. This method is recommended ways if you want to install pgAdmin on your laptop or computer.
- Server mode – this type of deployment can be applied on the server. It’s like phpMyAdmin on MySQL.
- Container mode – can be run as a docker container.
In this tutorial, you will learn how to install pgAdmin server mode on the latest Debian 11 Bullseye. You will learn how to set up pgAdmin with Apache web server, and learn the basic usage of pgAdmin for managing PostgreSQL database and user.
Prerequisites
- A Debian 11 server. Ensure all packages are updated to the latest version.
- A root user or a user with root privileges. This user will be used for installing new packages and editing system configurations.
- PostgreSQL database server – or you can install it using the following guide.
Before starting this guide, execute the ‘sudo‘ or ‘su‘ command below to get the root privileges.
# Type password for your user
sudo su# Type your root password
su
Installing PostgreSQL
If you’ve installed the PostgreSQL server, skip this first stage and go next. Otherwise, you can install PostgreSQL using the following guide.
1. To install the PostgreSQL server on the Debian server, execute the apt command below.
apt install postgresql
Type ‘y‘ and press ‘Enter‘ to continue the PostgreSQL installation.
2. And if the installation is complete, verify the PostgreSQL service by running the commands below.
systemctl is-enabled postgresql
systemctl status postgresql
You will see a similar output as below.
As seen in the screenshot, PostgreSQL is ‘enabled‘, which means it will be running automatically at system startup. And the current status of PostgreSQL is ‘active (exited)‘, which means the service is running, but the systemd cannot find the PostgreSQL daemon to monitor.
3. Next, you will be setting up the password for the default database user ‘postgres’.
Log in to the PostgreSQL shell using the command below.
sudo -u postgres psql
Create a new password for the database user ‘postgres‘ by running the query below. And make sure to change the ‘strongpostgrespassword‘ with your password.
ALTER USER postgres WITH PASSWORD 'strongpostgrespassword';
Now type ‘q‘ or press the ‘Ctrl d‘ to exit from the PostgreSQL shell.
## Add pgAdmin4 Repository to the Debian 11
The pgAdmin provides a repository for its installation for different Linux, including Debian and RHEL based operating systems. For this step, you will be adding a new repository for pgAdmin to the Debian 11 system.
1. First, install the gnupg2 package using the apt command below.
apt install gnupg gnupg2
2. Next, execute the following command to add the pgAdmin repository for the Debian system.
echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main"
| sudo tee /etc/apt/sources.list.d/pgadmin4.list
3. Add the gpg key of the pgAdmin repository using the following command.
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
4. Refresh and update all repositories using the command below
sudo apt update
Now you’re ready to install pgAdmin to the Debian system.
Installing and Configuring pgAdmin4 on Debian 11
1. To install pgAdmin on the server mode, install the ‘pgadmin4-web’ package using the apt command below.
apt install pgadmin4-web
Type ‘y‘ and press ‘Enter‘ to continue the installation.
2. If the installation is complete, execute the following command to configure the pgAdmin4 user account and the apache virtual host configuration.
/usr/pgadmin4/bin/setup-web.sh
Type the user email and password to create a new pgAdmin user, then press ‘Enter‘.
After that, you will be asked to configure the Apache web server, type ‘y‘, and press ‘Enter‘.
Now type ‘y’ again and press ‘Enter’ to restart the Apache service and apply a new configuration.
Now the pgAdmin installation is accessible at the URL path ‘/pgadmin4‘.
Additional info:
The installer script ‘setup-web.sh‘ will automatically create and activate the apache configuration at the ‘/etc/apache2/conf-enabled‘ directory. You can check using the following command.
cd /etc/apache2/conf-enabled/
cat pgadmin4.conf
Below is the configuration you will see.
Verify the pgAdmin Installation
1. Open your web browser and type the server IP address or the domain-name following by the path ‘/pgadmin4’ as below.
2. If your installation is successful, you will see the pgadmin4 login page as below.
Type your login user and email, then click the ‘Login‘ button.
Now you will see the pgadmin4 dashboard as below.
Connect to PostgreSQL Server with pgAdmin4
After logging in to the pgAdmin dashboard, you will be adding a new connection to your PostgreSQL server.
1. At the pgAdmin dashboard, click the icon ‘Add New Server‘.
2. You will get a small window pop-up.
At the tab ‘General‘, type a new ‘Name‘ of your connection.
For this guide, we will give a new connection name as ‘local‘.
3. Move to the tab ‘Connection‘ and type your details PostgreSQL host, database, user, and password.
For this example, we will use the local PostgreSQL server with user ‘postgres‘ to manage a default database ‘postgres‘.
After that, click the ‘Save‘ button to save a new connection.
4. If your connection is successful, you will see a similar page as below. Otherwise, you will see an error.
As you can see at the bottom of the screenshot, the user ‘postgres‘ is connected to the database ‘postgres‘ with the application ‘pgAdmin4‘, and the state of the connection is ‘active‘.
Basic Usage pgAdmin4: Creating New User
For this stage, you will be creating a new PostgreSQL using the pgAdmin4 tool.
1. Right click on the connection name menu “local => Create => Login/Group Role..“.
2. At the tab ‘General‘, type the new user that you want to create. For this example, you will be creating a new user ‘alice‘.
3. Move to the tab ‘Definition‘ and type the password for your user.
4. Move to the tab ‘Privileges‘ and turn on some privileges as you need.
Now click the ‘Save‘ button.
5. To verify your new user, click the menu ‘Login/Group Roles‘ at left, then click the ‘alice‘ that you just created.
Click the tab ‘Properties‘ on the page and you will get details of the user as on the top screenshot.
Basic Usage pgAdmin4: Creating New Database
For this stage, you will be creating a new database from the pgAdmin dashboard.
1. Right click on the connection name menu “local => Create => Database..“.
2. At the tab ‘General‘, type your new database name and choose the user owner of it. For this example, you will be creating a new database ‘alicedb‘ and make the user ‘alice‘ as the owner.
Now click the ‘Save‘ button.
3. To verify your new database, click the menu ‘Databases‘ at the left, then click the database ‘alicedb‘ that you just created.
Click the tab ‘Properties‘ on the page and you will get details of the database ‘alicedb‘ as on the top screenshot.
Verify User Connection
After creating a new user and database using the pgAdmin4, you will be creating a new PostgreSQL connection at the pgAdmin4 using the user ‘alice‘ and database ‘alicedb‘.
1. Back to the pgAdmin4 dashboard and click the icon ‘Add New Server‘.
2. At the tab ‘General‘, type new connection name as ‘alice-connection‘ as below.
3. Move to the tab ‘Connection‘ and type details of the new connection. The host is ‘127.0.0.1‘ with the user ‘alice‘, and will manage the database ‘alicedb‘, and don’t forget to type the password for the user ‘alice‘.
Now click the ‘Save‘ button.
4. If your database credentials are correct, you will see a similar page as below.
As you can see at the bottom of the screenshot, the user ‘alice‘ is connected to the database ‘alicedb‘ with the application ‘pgAdmin4‘, and the state of the connection is ‘active‘.
Conclusion
Congratulations! Now you’ve successfully installed the pgAdmin4 administration tool for PostgreSQL on the Debian 11 Bullseye. Also, you’ve learned how to create a new PostgreSQL connection on the pgAdmin4. And the basic usage of pgAdmin4 for creating a new database and user. For the next stage, you can use add your PostgreSQL server connection and managing your database through the pgAdmin4 dashboard.
