How do I install PostgreSQL on my server?


Browse by products and services

  • Applies to: DV
    • Difficulty: Medium
    • Time Needed: 10
    • Tools Required: SSH, root access, vi knowledge
  • Applies to: DV 4.0
    • Difficulty: Medium
    • Time Needed: 10
    • Tools Required: SSH, root access, vi knowledge

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 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.

Requirements

Before you start, this article has the following dependencies:

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.

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.

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 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

  1. Log into the Plesk Control Panel for your domain.
  2. Click on Websites & Domains.

    433_menu

  3. Click on Databases.
    425_databases
  4. Click on Add New Database.
    425_database_list
  5. Enter a name for your PostgreSQL database. From the Type menu, select PostgreSQL.

Resources