Installing or Updating PostgreSQL on Your cPanel & WHM Server
cPanel & WHM works with PostgreSQL 7.3.x or later.
Using the command line interface, log into your server as root.
If you have PostgreSQL 7.2.x installed, back up your databases using pg_dumpall or some other method. If you have databases you wish to keep, move your existing PostgreSQL data directory somewhere else by, for example, typing the following command.
mv /var/lib/pgsql /var/lib/pgsql.old
To begin the installation process:
On a CentOS or RedHat system, use the following script:
This script executes yum install postgresql (and related RPMs).
Cpanel disk space consumed by an account’s MySQL and PostgreSQL database
To enable this feature toggle the following Tweak Setting:
When displaying disk usage in cPanel/WHM include Postgresql and MySQL® disk usage.
As with the database counts, the disk usage calculation is handled by /scripts/update_db_cache which executes every 4 hours. This script is executed upon enabling the Tweak Setting. Administrators may also execute this script to recalculate the figures.
The disk usage figures are stored in /var/cpanel/datastore/mysql-disk-usage and /var/cpanel/datastore/postgres-disk-usage.
cat /var/cpanel/datastore/mysql-disk-usage
Support for /scripts/updatemysqlquota ends as of cPanelTM 11.25.0. Per this change /scripts/updatemysqlquota is no longer distributed with the product.
Reference: http://cpanel.net
Installing PostgreSQL on Your cPanel/WHM Server
cPanel requires PostgreSQL 7.3.x or later.
First step is to update cpanel using /scripts/upcp from command line while logged in as root. If you have PostgreSQL 7.2.x installed, back up your databases using pg_dumpall.
Move your existing PostgreSQL data directory somewhere else by, for example, typing the following command:
mv /var/lib/pgsql /var/lib/pgsql.old
Type the following command:
From WHM now select Postgres Config in the SQL Services section of WHM. Type a new PostgreSQL password in the text box and click Change Password.
Postgresql Error on Plesk
When trying to use Postgresql on Plesk Control Panel >> servers >> Database Servers >> Local PostgreSQL, getting the following error while using the postgresql for the first time.
Unable to rewrite /var/lib/pgsql/data/pg_hba.conf
1
Error on query to PostgreSQL
1
Unable to rewrite /var/lib/pgsql/data/pg_hba.conf
1
0: /usr/local/psa/admin/plib/DatabaseManagerPostgreSQL.php:263DatabaseManagerPostgreSQL-
>createLocalAdminLoginImpl(string ‘admin’, string ‘********’, NULL null)1: /usr/local/psa/
admin/plib/DatabaseManager.php:513DatabaseManager->updateLocalAdmin()2:
/usr/local/psa/admin/plib/DatabaseServerManager.php:229DatabaseServerManager->
updateServer(object of type DatabaseServer)3: /usr/local/psa/admin/htdocs/server/db_server_edit.php:52
This means postgresql is not running and when checking the error logs we can see like this:
#tail -f /usr/local/psa/admin/logs/httpsd_error_log
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/tmp/.s.PGSQL.5432″?
sed: can’t read /var/lib/pgsql/data/pg_hba.conf: No such file or directory
#tail -f /var/lib/pgsql/pgstartup.log
postmaster cannot access the server configuration file “/var/lib/pgsql/data/postgresql.conf”:
No such file or directory
Usually this happens when the postgresql database is not properly initialized. For fixing this error, we need to reinitialize the postgrsql database.
# rm -rf /var/lib/pgsql/data
# /etc/rc.d/init.d/postgresql start
Then again go to Plesk Control Panel >> servers >> Database Servers >> Local PostgreSQL and give your
administrator username and password.
Post PostgreSQL Installation
After installation of PostgreSQL start PostgreSQL by executing the command to see if the installation worked.
/etc/init.d/postgresql start
In the file ‘/var/lib/pgsql/data/pg_hba.conf’, replace all entries of “ident sameuser” with “trust”.
In the file ‘/var/lib/pgsql/data/postgresql.conf’, change the line “max_connections=100″ to max_connections=500″.
Restart PostgreSQL.
/etc/init.d/postgresql stop
/etc/init.d/postgresql start
