How to Install or Update PostgreSQL on your cPanel Server?
Posted by Allura on 16 09 2019.
This particular document aims to explain the procedure for installation or update of PostgreSQL® on a server to an experienced PostgreSQL database administrator.
It is to be noted that we support only the versions of PostgreSQL that the user installs with /usr/local/cpanel/scripts/installpostgres script. Thus, cPanel and WHM operate well with the version of PostgreSQL that synchronizes with your operating system.
How to back up databases?
The databases on the server need to be backed up before the user installs or updates PostgreSQL their server. The sole motive is to make sure you can restore and access your databases after the installation and update of the PostgreSQL version on your system.
To back up of your databases, you must log in to the server as the root user making use of SSH. Follow the given steps.
- pg_dumpall -U postgres > db.out command takes a back up of your PostgreSQL databases on your server. The db.out is the database backup file address.
- service postgresql stop mv /var/lib/pgsql /var/lib/directory_file_name command moves your current PostgreSQL database and reverts back to the last version of PostgreSQL. /directory_file_name represents the directory name.
- Via WHM’s Service Manager, if the monitor option is enabled (WHM >> Home >> Service Configuration >> Service Manager), then it should be deselected before the previous command is run. This prevents the restart of the PostgreSQL service on its own after stopping it forcefully.
PostgreSQL Installation or Upgrade
If you want to install, upgrade, and configure PostgreSQL on your server, then follow the given steps.
- Use /usr/local/cpanel/scripts/installpostgres command.
This command upgrades or installs PostgreSQL.
- ConfigurePostgreSQL interface of WHM must be accessed through WHM >> Home >> SQL Services >> Configure PostgreSQL
- In case PostgreSQL is absent on your server, select Install Config while in case PostgreSQL exists on your server, this process replaces the authentication file for configuration.
- Add existing cPanel users to PostgreSQL using Create Users option.
- In the Enter New Password box, input a new password. Make sure to avoid non-alphanumeric characters and symbols to bypass errors.
- Select Change Password after confirmation of new password.
- Use psql -U postgres dbname < dumpfile.sql command to restore your data directories.
How to restore a previous PostgreSQL version successfully?
Users can revert to their previous version of PostgreSQL if the new installation procedure fails. Thus, previous PostgreSQL databases can be restored. The following commands are used.
service postgresql stop
mv /var/lib/pgsql /var/lib/directory_file_name
mv /var/lib/pgsql.old /var/lib/pgsql
service postgresql restart
This document makes installation and upgrade of PostgreSQL simple and the configuration, safe and secure. Users can visit the PostgreSQL website for additional information.