PostgreSQL is an open source object-relational database system. PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.
PostgreSQL provides an alternative to MySQL for running database applications. Many popular applications today have support for both. The DV server includes MySQL 5.5. However, PostgreSQL support is not included. This tutorial will guide you through the simple steps of installing PostgreSQL and having Plesk recognize it as a valid database source.
PostgreSQL provides an alternative to MySQL for running database applications. Many popular applications today have support for both. The DV includes MySQL 5.5. However, PostgreSQL support is not included. This tutorial will guide you through the simple steps of installing PostgreSQL and having Plesk recognize it as a valid database source.
Before you start, this article has the following dependencies:
- You must have SSH access set up for root or a sudo user.
READ ME FIRST
This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is unsupported by (mt) Media Temple. Please take a moment to review the Statement of Support.
Step 1: Installing the server files
yum comes installed on your server, you can use it to install the PostgreSQL server with this command:
yum install postgresql postgresql-server
Packages will be downloaded and you will be prompted to confirm.
Total download size: 6.8 M Is this ok [y/N]:
y to continue.
Step 2: Configure the server
Start PostgreSQL so that we can add our admin user:
Make sure you know your Plesk admin password. Use this command to see what it is. We suggest highlighting it and copying it, so that you can paste it in exactly as is when prompted in the next step.
cat /etc/psa/.psa.shadow && echo
Now, change to the postgres system user and issue the command to add the
admin user which Plesk uses:
su postgres createuser -slPE
You'll be prompted to enter the username and password. Enter the username
admin and enter or paste in your Plesk password (twice for confirmation).
We're done with our Postgres user. So use the
exit command to return to your root user:
The last configuration step is to modify a configuration file to allow password logins for requests that come from your server. You need to edit the
/var/lib/pgsql/data/pg_hba.conf file, comment out the existing
host all all 127.0.0.1/32 ident line, and add this line:
host all all 127.0.0.1/32 md5.
Copy and paste this command to make the required change and restart your PostgreSQL server:
sed -i 's/host\s*all\s*all\s*127.0.0.1\/32\s*ident\s*sameuser/#&/' /var/lib/pgsql/data/pg_hba.conf && echo -e "\n# Allow password connections from localhost\nhost all all 127.0.0.1/32 md5" >> /var/lib/pgsql/data/pg_hba.conf && /etc/init.d/postgresql restart
Step 3: Register PostgreSQL with Plesk
This command registers your PostgreSQL installation with Plesk. This way, you can create and manage databases from within Plesk.
/usr/local/psa/bin/database-server --update-server localhost:5432 -type postgresql -admin admin -passwd `cat /etc/psa/.psa.shadow` && service psa restart
Access in Plesk
- Log into the Plesk Control Panel for your domain.
- Click on Websites & Domains.
- Click on Databases.
- Click on Add New Database.
- Enter a name for your PostgreSQL database. From the Type menu, select PostgreSQL.