Installing PostgreSQL and pgadmin4 on HestiaCP

In the HestiaCP panel, you can add support for the PostgreSQL database management system (DBMS) during installation, just like MySQL. But if the panel was installed only with the MySQL DBMS, and you need additional PostgreSQL, then it can be installed and built into the HestiaCP panel so that the databases are managed through this panel. This can be done without reinstalling the panel.

All actions will be performed mainly on the basis of the HestiaCP panel installation scripts in the nginx + apache2 + php-fpm (multi PHP) configuration. The Linux system that this article is working with is Ubuntu 20.04.

Installing PostgreSQL and adding it to the HestiaCP panel

First run the following commands to install PostgreSQL.

# Adding a repository
echo "deb [arch=$ARCH signed-by=/usr/share/keyrings/postgresql-keyring.gpg] https://apt.postgresql.org/pub/repos/apt/ $codename-pgdg main" > $apt /postgresql.list
curl -s https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | tee /usr/share/keyrings/postgresql-keyring.gpg >/dev/null 2>&1
apt update

# Install PostgreSQL
apt install postgresql postgresql-contrib

Add a user for the control base. The management database name is postgres. The username will also be postgres.

sudo -iu postgres psql -c "ALTER USER postgres WITH PASSWORD 'PutYourPasswordHere'" > /dev/null 2>&1

Here, instead of PutYourPasswordHere, enter your password. It is necessary to generate a complex, long password containing large and small Latin letters, as well as special characters.

Next, you need to add a new system into the HestiaCP configuration file. To do this, open the /usr/local/hestia/conf/hestia.conf file in any convenient editor. Find the DB_SYSTEM parameter and add pgsql to it. The result should look like this:

DB_SYSTEM='pgsql,mysql'

Add PostgreSQL to the HestiaCP system by running the command:

v-add-database-host pgsql localhost postgres PutYourPasswordHere

Installing pgadmin4

The HestiaCP panel usually has phpPgAdmin (analogous to phpMyAdmin) installed along with the PostgreSQL installation, but this application may not work with new PostgreSQL or PHP servers. To manage PostgreSQL databases, it is convenient to install and use the pgadmin4 system.
To install it, follow these steps:

# Adding a repository
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
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 pgadmin4
apt install pgadmin4-web

These commands will add the application repository, repository keys and install the web version of the panel. There is also a version for the desktop, but it will not be considered here because it will “pull” the installation of the desktop environment, which is not suitable for a web server.

Setting up the pgadmin4 admin panel

After installing the system, run the script and set up the panel account:

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

You need to enter an e-mail (as a login) and a password. The password may be different from what we set for the postgres database. The script will also offer to create an apache2 config. Confirm creation.

Copy the configuration file to the appropriate location in the apache2 configuration files of the HestiaCP panel:

cp /etc/apache2/conf-enabled/pgadmin4.conf /etc/apache2/conf.d/
systemctl restart apache2

After installing pgadmin4, you need to add the PostgreSQL server to it. To do this, go to:

https://<Your HestiaCP panel domain>/pgadmin4

You can also:

http://<server IP address>/pgadmin4

Enter the e-mail and password created during the pgadmin4 admin panel configuration step.

After logging in, the first step is to add the PostgreSQL server to the pgadmin4 system. To do this, click on the Add New Server button.

On the General tab, enter any server name:

On the Connection tab, enter the server address - localhost or 127.0.0.1. Enter the data from the postgres database you created, enable the Save password? option.

Then click the Save button. You will then be able to manage PostgreSQL databases through the pgadmin4 panel. I recommend creating and deleting databases through the HestiaCP panel in the DB section, and the rest of the actions through pgadmin4. All PostgreSQL databases of all server users will be available in the pgadmin4 panel.

Conclusion

The article discussed how to add PostgreSQL and pgadmin4 to the HestiaCP control panel.