In the HestiaCP panel, you can add support for the database management system (DBMS) PostgreSQL during installation, just like MySQL. However, if the panel was installed only with the MySQL DBMS and you need PostgreSQL additionally, it can be installed and integrated into the HestiaCP panel so that database management is done through this panel. This can be done without reinstalling the panel.
All actions will be performed mainly based on the installation scripts of the HestiaCP panel in the configuration nginx + apache2 + php-fpm (multi PHP). The Linux system used in this article is Ubuntu 20.04.
Installing PostgreSQL and Adding it to the HestiaCP Panel
First, execute the following commands to install PostgreSQL.
# Adding the 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
# Installing PostgreSQL
apt install postgresql postgresql-contrib
Add a user for the management database. The name of the management database 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, specify your password. You need to generate a complex, long password that contains uppercase and lowercase Latin letters, as well as special characters.
Next, you need to write the new system into the HestiaCP configuration file. To do this, open the file /usr/local/hestia/conf/hestia.conf in any convenient editor. Find the parameter DB_SYSTEM and add pgsql to it. As a result, it should look like this:
DB_SYSTEM='pgsql,mysql'
Add PostgreSQL to the HestiaCP system by executing the command:
v-add-database-host pgsql localhost postgres PutYourPasswordHere
Installing pgadmin4
In the HestiaCP panel, along with the installation of PostgreSQL, phpPgAdmin (analogous to phpMyAdmin) is usually installed, but this application may not work with new PostgreSQL or PHP servers. For managing PostgreSQL databases, it is convenient to install and use the pgadmin4 system.
To install it, follow these steps:
# Adding the 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'
# Installing 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 desktop version, but it will not be considered here as it would require the installation of a desktop environment, which is not suitable for a web server.
Configuring the pgadmin4 Admin Panel
After installing the system, run the script and set up the admin panel account:
/usr/pgadmin4/bin/setup-web.sh
You need to enter your e-mail (as a login) and password. The password can be different from the one we set for the postgres database. The script will also prompt you to create an apache2 config. Confirm the creation.
Copy the configuration file to the appropriate place 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 the address:
https://<Your HestiaCP panel domain>/pgadmin4
You can also:
http://<Server IP address>/pgadmin4
Enter the e-mail and password created during the setup of the pgadmin4 admin panel.
After logging in, the first thing you need to do is add the PostgreSQL server to the pgadmin4 system. To do this, click the Add New Server button.
On the General tab, enter any name for the server:
On the Connection tab, enter the server address - localhost or 127.0.0.1. Enter the credentials for the database postgres you created, and enable the Save password? option.
Then click the Save button. After that, you will be able to manage PostgreSQL databases through the pgadmin4 panel. I recommend creating and deleting databases through the HestiaCP panel in the DB section, while other actions can be done through pgadmin4. The pgadmin4 panel will have access to all databases of PostgreSQL for all users of the server.
Conclusion
This article discussed the method of adding PostgreSQL and pgadmin4 to the HestiaCP control panel.