Setting PostgreSQL on Linux in Vesta control panel

In order to set PostgreSQL on Linux in Vesta, you need to clean the system “minimal install base”.

Our technical team support will set the PostgreSQL on Linux in the Vesta control panel on your behalf under our server management services plan. You can also take assistance from our technical team support 24/7 for fixing the error or any sort of installation and migration.

 PostgreSQL on Linux in Vesta control panel

Now you can install the Vesta panel on an RHEL, Ubuntu Server, Debian and CentOS.

Setting PostgreSQL on RHEL/CentOS

1. First, need to install PostgreSQL packages
2. # yum install postgresql postgresql-server postgresql-contrib phpPgAdmin
3. Install >> PostgreSQL >> from Remi repository >>then enable explicitly.
4. # yum install –enablerepo=remi postgresql postgresql-server postgresql-contrib phpPgAdmin
5.  Then initialize the database cluster:
6. # service postgresql initdb
7. Download HBA configuration:
8. # wget http://c.vestacp.com/0.9.8/rhel/pg_hba.conf -O /var/lib/pgsql/data/pg_hba.conf
9. Eventually, start the server:
10. # service postgresql start
11. Set oracle user password:
12. # su – postgres
13. # psql -c “ALTER USER postgres WITH PASSWORD ‘pgp4sw0rd’”
14. # exit
15. Then enable pgsql database support in Vesta.
16. Open /usr/local/vesta/conf/vesta.conf and set DB_SYSTEM to ‘mysql,pgsql’
17. Register pg instance in the control panel:
18. # v-add-database-host pgsql localhost postgres pgp4sw0rd
19. Download phpPgAdmin configuration:
20. # wget http://c.vestacp.com/0.9.8/rhel/pga.conf -O /etc/phpPgAdmin/config.inc.php
21. # wget http://c.vestacp.com/0.9.8/rhel/httpd-pga.conf -O /etc/httpd/conf.d/phpPgAdmin.conf
22. Restart the webserver:
23. # service httpd restart

Setting up PostgreSQL on Debian/Ubuntu

1. Install PostgreSQL packages
2. # apt-get install postgresql postgresql-contrib phppgadmin
3. Download HBA configuration:
4. # wget http://c.vestacp.com/0.9.8/debian/pg_hba.conf -O /etc/postgresql/*/main/pg_hba.conf
5. Later,restart the server:
6. # service postgresql restart
7. Set oracle user password:
8. # su – postgres
9. # psql -c “ALTER USER postgres WITH PASSWORD ‘pgp4sw0rd’”
10. # exit
11. Enable pgsql database support in Vesta.
12. Open /usr/local/vesta/conf/vesta.conf and set DB_SYSTEM to ‘mysql,pgsql’
13. In the control panel >> register pg instance.
14. # v-add-database-host pgsql localhost postgres pgp4sw0rd
15. Download phpPgAdmin configuration:
16. # wget http://c.vestacp.com/0.9.8/debian/pga.conf -O /etc/phppgadmin/config.inc.php
17. # wget http://c.vestacp.com/0.9.8/debian/apache2-pga.conf -O /etc/apache2/conf.d/phppgadmin
18. Restart the webserver:
19. # service apache2 restart

Error while we Setup PostgreSQL on Linux in Vesta

One of the clients came across a similar error while he trying to connect with remote PostgreSQL.

The server is not connecting to the PostgreSQL 

To resolve the issue, you need to start the PostgreSQL server on the remote server. (Need technical team support to resolve the error on your behalf)

# /etc/init.d/postgresql start

If we get the error while it is running, we need to enable TCP/IP support.

By default, the PostgreSQL server only allows connections to the database from the local machine or localhost.

Step 1: Allow remote IP address to access PostgreSQL

You need to open the file,/var/lib/pgsql/data/pg_hba.conf.By using the following su commands try to login into Postgres.

$ su – postgres
$ vi /var/lib/pgsql/data/pg_hba.conf

Now append the following line. To give access to the 192.168.1.0/24 network:

host all all 192.168.1.0 255.255.255.0 trust

Then, save and close the file.

Step 2: Allow communication over TCP/IP

We open PostgreSQL configuration file, /var/lib/pgsql/data/postgresql.conf

$ vi /var/lib/pgsql/data/postgresql.conf

Then bind and open TCP/IP port by setting tcpip_socket to true:

tcpip_socket = true

Finally, save and close the file.

Step 3: Restart PostgreSQL server

Use the following commands to restart the PostgreSQL server

# /etc/init.d/postgresql restart

This will open default port 5432.

Step 4: Test the setup

Finally, use the psql command from the client system.

psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME

Conclusion

In brief, Now you can install the Vesta Control panel on an RHEL, CentOS, Debian, and Ubuntu server. Happy to assist you out with any queries.