Our Blog

Fun with SQL

By Josh Tolley
October 10, 2009

Many programmers, I expect, have a favorite obscure language or two they’d like to see in wider use. Haskell has quite a following, though it sees relatively little use; the same can be said for most pure functional languages. Prolog seemed like a neat idea when I first read about it, but I’ve never heard of anyone using it for something serious (caveat: there are lots of things I’ve never heard of).

My own favorite underused language is SQL. Although most programmers have at least a passing familiarity with SQL, and many use it daily, few seem to achieve real SQL fluency. This is unfortunate; SQL databases are powerful and ubiquitous tools, and even the least among them can generally manage a great deal more than the fairly simple uses to which they are commonly put.

I recently had the opportunity to trot out this curmudgeonly opinion of mine at the Utah Open Source Conference. Now in its third year, this annual conference continues to surprise me. Utah is home to an extraordinarily vibrant open source community, and it shows, in the attendance (over 400 expected), the number of presentations (92 by my count of the schedule, perhaps minus a few that aren’t …

New End Point site launched: Rails, jQuery, Flot, blog feed

By Steph Skardal
October 7, 2009

This week we launched a new website for End Point. Not only did the site get a facelift, but the backend content management system was entirely redesigned.

Goodbye Old Site:

Hello New Site:

Our old site was a Rails app with a Postgres database running on Apache and Passenger. It used a custom CMS to manage dynamic content for the bio, articles, and service pages. The old site was essentially JavaScript-less, with the exception of Google Analytics.

Although the new site is still a Rails application, it no longer uses the Postgres database. As developers, we found that it is more efficient to use Git as our “CMS” rather than developing and maintaining a custom CMS to meet our ever-changing needs. We also trimmed down the content significantly, which further justified the design; the entire site and content is now comprised of Rails views and partial views. Also included in the new site is cross browser functioning jQuery and flot. Some of the interesting implementation challenges are discussed below.

jQuery Flot Integration

The first interesting JavaScript component I worked on was using flot to improve interactivity to the site and to decrease the excessive text that …

rsync and bzip2 or gzip compressed data

By Jon Jensen
October 6, 2009

A few days ago, I learned that gzip has a custom option --rsyncable on Debian (and thus also Ubuntu). This old write-up covers it well, or you can just man gzip on a Debian-based system and see the --rsyncable option note.

I hadn’t heard of this before and think it’s pretty neat. It resets the compression algorithm on block boundaries so that rsync won’t view every block subsequent to a change as completely different.

Because bzip2 has such large block sizes, it forces rsync to resend even more data for each plaintext change than plain gzip does, as noted here.

Enter pbzip2. Based on how it works, I suspect that pbzip2 will be friendlier to rsync, because each thread’s compressed chunk has to be independent of the others. (However, pbzip2 can only operate on real input files, not stdin streams, so you can’t use it with e.g. tar cj directly.)

In the case of gzip --rsyncable and pbzip2, you trade a little lower compression efficency (< 1% or so worse) for reduced network usage by rsync. This is probably a good tradeoff in many cases.

But even more interesting for me, a couple of days ago Avery Pennarun posted an article about his experimental code to …

Using ln -sf to replace a symlink to a directory

By Jon Jensen
September 25, 2009

When you want to forcibly replace a symbolic link on some kind of Unix (here I’m using the version of ln from GNU coreutils), you can do it the manual way:

rm -f /path/to/symlink
ln -s /new/target /path/to/symlink

Or you can provide the -f argument to ln to have it replace the existing symlink automatically:

ln -sf /new/target /path/to/symlink

(I was hoping this would be an atomic action such that there’s no brief period when /path/to/symlink doesn’t exist, as when mv moves a file over top of an existing file. But it’s not. Behind the scenes it tries to create the symlink, fails because a file already exists, then unlinks the existing file and finally creates the symlink.)

Anyway, that’s convenient, but I ran into a gotcha which was confusing. If the existing symlink you’re trying to replace points to a directory, the above actually creates a symlink inside the dereferenced directory the old symlink points to. (Or fails if the referent is invalid.)

To replace an existing directory symlink, use the -n argument to ln:

ln -sfn /new/target /path/to/symlink

That’s always what I have wanted it to do, so I need to remember the -n.

GNU Screen: follow the leader

By Kiel Christofferson
September 24, 2009

First of all, if you’re not using GNU Screen, start now :).

Years ago, Jon and I spoke of submitting patches to implement some form of “follow the leader” (like the children’s game, but with a work-specific purpose) in GNU Screen. This was around the time he was patching screen to raise the hard-coded limit of 40 windows allowed within a given session, which might give an idea of how much screen gets used around here (a lot).

The basic idea was that sometimes we just want to “watch” a co-worker’s process as they’re working on something within a shared screen session. Of course, they’re going to be switching between screen windows and if they forget to announce “I’ve switched to screen 13!” on the phone, then one might quickly become lost. What if the cooperative work session doesn’t include a phone call at all?

To the rescue, Screen within Screen.

Accidentally arriving at one screen session within another screen session is a pretty common “problem” for new screen users. However, creative use of two (or more) levels of nested screen during a shared session allows for a “poor man’s” follow the leader.

If the escape sequence of the outermost screen is changed to something other than …

Permission denied for postgresql.conf

By Greg Sabino Mullane
September 21, 2009

I recently saw a problem in which Postgres would not startup when called via the standard ‘service’ script, /etc/init.d/postgresql. This was on a normal Linux box, Postgres was installed via yum, and the startup script had not been altered at all. However, running this as root:

 service postgresql start

…simply gave a “FAILED”.

Looking into the script showed that output from the startup attempt should be going to /var/lib/pgsql/pgstartup.log. Tailing that file showed this message:

  postmaster cannot access the server configuration file
  "/var/lib/pgsql/data/postgresql.conf": Permission denied

However, the postgres user can see this file, as evidenced by an su to the account and viewing the file. What’s going on? Well, anytime you see something odd when using Linux, especially if permissions are involved, you should suspect SELinux. The first thing to check is if SELinux is running, and in what mode:

# sestatus

SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 21
Policy from config file: …

SEO: External Links and PageRank

By Steph Skardal
September 17, 2009

I had a flash of inspiration to write an article about external links in the world of search engine optimization. I’ve created many SEO reports for End Point’s clients with an emphasis on technical aspects of search engine optimization. However, at the end of the SEO report, I always like to point out that search engine performance is dependent on having high quality fresh and relevant content and popularity (for example, PageRank). The number of external links to a site is a large factor in popularity of a site, and so the number of external links to a site can positively influence search engine performance.

After wrapping up a report yesterday, I wondered if the external link data that I provide to our clients is meaningful to them. What is the average response when I report, “You should get high quality external links from many diverse domains”?

So, I investigated some data of well known and less well known sites to display a spectrum of external link and PageRank data. Here is the origin of some of the less well known domains referenced in the data below:

• www.petfinder.com: This is where my dogs came from.
• www.endpoint.com: That’s Us!
• www.sonypictures.com/movies/district9/: …

Migrating Postgres with Bucardo 4

By Greg Sabino Mullane
September 16, 2009

Bucardo just released a major version (4). The latest version, 4.0.3, can be found at the Bucardo website. The complete list of changes is available on the new Bucardo wiki.

One of the neat tricks you can do with Bucardo is an in-place upgrade of Postgres. While it still requires application downtime, you can minimize your downtime to a very, very small window by using Bucardo. We’ll work through an example below, but for the impatient, the basic process is this:

1. Install Bucardo and add large tables to a pushdelta sync
2. Copy the tables to the new server (e.g. with pg_dump)
3. Start up Bucardo and catch things up (e.g. copy all rows changes since step 2)
4. Stop your application from writing to the original database
5. Do a final Bucardo sync, and copy over non-replicated tables
6. Point the application to the new server

With this, you can migrate very large databases from one server to another (or from Postgres 8.2 to 8.4, for example) with a downtime measured in minutes, not hours or days. This is possible because Bucardo supports replicating a “pre-warmed” database—​one in which most of the data is already there.

Let’s test out this process, using the handy pgbench utility to create a …