Steps to Install or Update PostgreSQL Database Wizard
Posted by Allura on 13 12 2019.
This specific document aims to clarify the procedural ways for the installation or update of PostgreSQL® on a server to a fully-fledged PostgreSQL database administrator.
It is to be noted that we tend to support solely 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 have to be compiled to be insured before the user installs or updates PostgreSQL their server. The sole motive is to restore and access the databases to post the installation and update of the PostgreSQL version on your system.
To back up 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 wish the installation, upgrade, and configuration of the PostgreSQL on your server, then follow the given steps.
- Use /usr/local/cpanel/scripts/installpostgres commandTh
This command upgrades or installs PostgreSQL.
- ConfigurePostgreSQL interface of WHM must be accessed through WHM >> Home >> SQL Services >> Configure PostgreSQL
- In case PostgreSQL does not exist on the server, the user will have the option to 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 by employing the 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 a new password.
- Use psql -U postgres dbname < dumpfile.sql command to restore your data directories.
How to Restore a Previous PostgreSQL Version Successfully?
Users will 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.old /var/lib/pgsql
- service postgresql restart
This document makes installation and upgrade of PostgreSQL easy, therefore making the configuration, safe and secure. Users will visit the PostgreSQL site for any extra valuable information.