Since writing this, I've migrated to Heroku and Gandi simple hosting for personal projects, with Heroku Postgres for postgres database hosting when needed. This post is mostly for archival purposes, but it still may be useful for those dealing with older (dv) servers on Media Temple.

Preface

Last week Django 1.4 was officially released. It brought a lot of new features, so I wanted to upgrade most of my projects as soon as I could. I use Heroku for almost all of my Django apps, and have a (dv) server from Media Temple for serving static content and database hosting. Up until Django’s new release, this worked really well. I soon discovered a problem though. A minimum of PostgreSQL 8.2 is now required. My server only had 8.1.

The Problem

Now, there are all kinds of tutorials and guides walking you through an install or upgrade of PostgreSQL 8.x to other versions, including 9. What these guides fail to take into account is servers with the Plesk web admin interface also running. According to Plesk’s release notes (which itself is hard to find), they only support up to 8.4. You can go with the 9.X branch, but then we miss out on the Plesk integration, and the ability to manage Postgres databases from the admin web interface.

One of the few nice things about Plesk is it comes with a handy updater/installer that will actually occasionally work. It can be accessed by running (as root, or anything equivalent):/usr/local/psa/admin/sbin/autoinstaller

After fixing a few errors (most of which were my own doing), I successfully updated to the latest version of Plesk (10.4.4). While Plesk claims the auto-installer should install PostgreSQL to 8.4, they are wrong when it comes to CentOS 5. Instead, it only installs the 8.1 branch.

The Solution

If you attempt to install via Yum, you will presented with two separate packages: ‘postgresql’ (8.1 branch) and ‘postgresql84’ (8.4.x branch). Either will install just fine. The server will start, and you can even start using it. While both technically work, only the 8.1 package will be recognized by Plesk. So, how do we solve this? We need to manually grab the latest 8.4 RPM package from PostgreSQL servers.

First, we'll need to remove any and all existing packages:yum remove postgresql

And hide the current packages in the yum repo, by editing /etc/yum.repos.d/CentOS-Base.repo and adding this line to the base and updates sections:exclude=postgresql*

This will tell yum to ignore packages from the normal repos, and only display the packages from the RPM we'll be using. So next we get PostgreSQL 8.4 directly from the PostgreSQL servers:wget http://yum.postgresql.org/8.4/redhat/rhel-5-x86_64/pgdg-centos-8.4-3.noarch.rpm

And install the rpm distribution:rpm -ivh pgdg-centos-8.4-3.noarch.rpm

From there we can install PostgreSQL like usual:yum list postgres*
yum install postgresql-server
service postgresql initdb
chkconfig postgresql on
service postgresql start

Next we begin the process of letting Plesk access it, by adding our Plesk admin account (make sure to use the same username/password as the plesk account):su postgres
createuser -slPE

We’re done as the Postgres user, so you can exit out of it:exit

Then we enable access to allow logins from the (dv) server. Edit/var/lib/pgsql/data/pg_hba.conf and comment out the line:

all all 127.0.0.1/32 ident

And add this line:

all all 127.0.0.1/32 md5

Finally, we can register PostgreSQL 8.4 with Plesk by using the command:/usr/local/psa/bin/database-server —update-server localhost:5432 -type postgresql -admin admin -passwd `cat /etc/psa/.psa.shadow` && service psa restart

If all went well, PostgreSQL 8.4 will finally be installed and accesible through Plesk/pgAdmin!

Hydrophobia and the fearless mingling of ideas

Tags