Drupal and Postgres in Production

Posted12 February 2015

I'm happy to say that I'm getting to play with Drupal a bit at work. I've used it for years for various blogs and projects, and have generally found it to be pretty good. However, there's a vast gulf between running something on your own home server or cheesy VPS and running things on production systems that don't have direct Internet access and live in a secure, change-controlled, regulated company's data centre.

I'm also getting some exposure to Postgres. I've used MySQL quite a bit for all my Drupal playing, and it was preferred over Postgres to some degree at previous employers. However, nowadays its the other way around, and I've got a task coming up to convert an old Drupal system from MySQL to Postgres. So far I have to say I quite like it - I'm no DBA, but it seems pretty easy to get it working and quite easy to make it do things like replication, backups and so on. I've had a couple of 'near-misses' with it where things haven't worked as I wanted, and so far it's recovered pretty cleanly. We've got it quite nicely Puppetised, so getting it going on new boxes is pretty slick.

Drupal's also nicely puppetised, but I haven't yet found a proper solution for its settings.php config file. At the moment, Puppet's putting that in fully populated, which works for rebuilds but not for bare-metal installs.

I also don't have 'clean' solutions for a number of Drupal-niggles. In production, servers have to use a whitelist proxy to get to the Internet. We've got drupal.org whitelisted, and we configure the proxy into settings.php, but we have to 'hack' the Internet-facing name for the site into /etc/hosts so that Drupal can talk to itself. This all seems a bit untidy, but it seems work.

Every once in a while, such setups throw things are you that despite years of working with the product are completely new. The other day I was setting up a dev box which kept failing because of some XML parsing problems during install. I'd never seen this before, and searching the 'net didn't really help either. It turned out we have a (relatively new) proxy/firewall that was intercepting the port 80 traffic and then returning an HTTP 200 saying "sorry, your request is denied" (why would a denial have a 200 response code?). It looks like Drupal 8's got better exception handling around this problem, so it'll be easier in the future.

Another 'gotcha' I bumped into was when moving systems and databases around. I'm not 100% sure why this happens, but the Session table gets 'out of sync' with the new world the system finds itself in. This manifests itself as not being able to log in - the password is accepted, but you just don't actually get logged in. Deleting the contents of the Sessions table seems to fix the problem, although I quietly worry this'll happen if we move data centres (during which we'll fail the database over to one of the hot standbys). We've done this sort of flip before and we didn't seem to have this problem, so maybe my worries are unfounded.

All these things aside, it's nice to see work (a FTSE 500 company) adopting an Open Source solution for quite a few of their needs. Drupal's a good product, but it isn't perfect, but hopefully those imperfections don't get in the way too much.

Tags: #drupal #php #postgres #mysql

Ralph Bolton

Drupal and Postgres in Production