Overview

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) Dedicated-Virtual Server includes MySQL 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.

Requirements

Before you start, this article has the following dependencies:

• You must have SSH access set up for root or a sudo user.
  • Connecting via SSH to your server
  • How do I enable root access to my (dv)?
  • How do I disable SSH login for the root user?

Instructions

Step 1: Installing the server files

Since 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]:

Enter y to continue.

If you have installed yum on your server, you can use it to install the PostgreSQL server with this command:

yum install postgresql postgresql-server

If you haven't installed yum, you can manually download and install the package files with these commands:

rpm -Uvh http://mirrors.kernel.org/centos/5/updates/i386/RPMS/postgresql-server-8.1.22-1.el5_5.1.i386.rpm
rpm -Uvh http://mirrors.kernel.org/centos/5/updates/i386/RPMS/postgresql-8.1.22-1.el5_5.1.i386.rpm

Step 2: Configure the server

Start PostgreSQL so that we can add our admin user:

/etc/init.d/postgresql start

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:

exit

The last configuration step is to modify a configuration file to allow password logins for requests that come from your (dv). 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. You need to edit the /var/lib/pgsql/data/pg_hba.conf file, comment out the existing local all all line, and this line: local all all 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

sed -i 's/local\s*all\s*all\s*ident\s*sameuser/#&/' /var/lib/pgsql/data/pg_hba.conf && echo -e "\n# Allow password connections from localhost\nlocal all all 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

server localhost:5432 -type postgresql -admin admin -passwd `cat /etc/psa/.psa.shadow`

Access in Plesk

1. Log into the Plesk Control Panel for your domain. See Figure 1:

   
   Figure 1. The Plesk Control Panel. Click for a larger view.

2. From the Home page, click on Websites & Domains. See Figure 2:

   
   Figure 2: Click on the "Websites & Domains" tab.

3. Click on Show Advanced Options. See Figure 3 for the location of this link.

   
   Figure 3: Click on "Show Advanced Options" for an additional menu.

4. Next, click on Databases. See Figure 4:

   
   Figure 4: Click "Databases" to begin.

5. Click on Add New Database. See Figure 5:

   
   Figure 5: Click "Add New Database" to begin.

6. Enter a name for your PostgreSQL database. From the Type menu, select PostgreSQL. See Figure 6.

   
   Figure 6.

Resources

• PostgreSQL Wiki
• How do I enable remote access to my PostgreSQL server?