26.6 C
Jaipur
Wednesday, October 27, 2021

How to Install pgAdmin 5 PostgreSQL Administration Tool on Debian 11

Must read

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.

Create new password for user postgres

## 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.

Add repository pgAdmin4 Debian 11

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‘.

Configuring pgAdmin4

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.

Apache configuration for pgAdmin4

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.

http://192.168.1.10/pgadmin4

2. If your installation is successful, you will see the pgadmin4 login page as below.

pgAdmin4 login page

Type your login user and email, then click the ‘Login‘ button.

Now you will see the pgadmin4 dashboard as below.

pgAdmin4 Dashboard

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‘.

Add new connection

2. You will get a small window pop-up.

At the tab ‘General‘, type a new ‘Name‘ of your connection.

Connection name

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‘.

Connection details

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.

Connected to PostgreSQL Server

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..“.

Create new role or user

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‘.

Username for the new user

3. Move to the tab ‘Definition‘ and type the password for your user.

Password for the new user

4. Move to the tab ‘Privileges‘ and turn on some privileges as you need.

Privilege for new user

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.

Verify new user

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..“.

Create new 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.

Setup database name and 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.

Verify

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‘.

Add new connection

2. At the tab ‘General‘, type new connection name as ‘alice-connection‘ as below.

Create new connection alice

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‘.

Details connection for user alice

Now click the ‘Save‘ button.

4. If your database credentials are correct, you will see a similar page as below.

Connection successfully

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.

Source link

- Advertisement -

More articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement -

Latest article