Our Blog

Viewing schema changes over time with check_postgres

By Greg Sabino Mullane
October 5, 2011

Image by Flickr user edenpictures

Version 2.18.0 of check_postgres, a monitoring tool for PostgreSQL, has just been released. This new version has quite a large number of changes: see the announcement for the full list. One of the major features is the overhaul of the same_schema action. This allows you to compare the structure of one database to another and get a report of all the differences check_postgres finds. Note that “schema” here means the database structure, not the object you get from a “CREATE SCHEMA” command. Further, remember the same_schema action does not compare the actual data, just its structure.

Unlike most check_postgres actions, which deal with the current state of a single database, same_schema can compare databases to each other, as well as audit things by finding changes over time. In addition to having the entire system overhauled, same_schema now allows comparing as many databases you want to each other. The arguments have been simplified, in that a comma-separated list is all that is needed for multiple entries. For example:

./check_postgres.pl --action=same_schema \
  --dbname=prod,qa,dev --dbuser=alice,bob,charlie

The above …

CRUD, RESTful, and JSON for Address Management in Interchange

By Steph Skardal
October 4, 2011

Recently, I worked on a large project for Paper Source that introduced the functionality to allow users to split orders into multiple addresses, which is especially valuable during the holiday season for gift purchase. Paper Source runs on Interchange, one of the ecommerce frameworks End Point is intimately familiar with.

This project requires CRUD functionality for address editing during the checkout process; users must be able to add, edit, list, and remove addresses to be assigned to the various items in the cart. Another challenge here is that both logged in and logged out users are required to have this functionality, where addresses modified by logged in users would persist between sessions, and addresses of logged out users are available during their session and destroyed after they end the session (or in Paper Source’s case, when they close the browser window).

With these requirements, I set off to develop an architecture that followed RESTful practices, described below:

Listing Addresses

A Perl module contains a method for listing user addresses, which is shown below in simplified form. The user_addresses scratch variable is created and contains an array of hashed …

A comparison of Jaspersoft iReport and Pentaho Reporting

By Josh Tolley
October 2, 2011

I’ve recently been involved in reporting projects using both Jaspersoft’s iReport and Pentaho’s Reporting, so this seemed a good opportunity to compare the two. Both are Java-based, open source reporting systems which claim to build “pixel-perfect” documents (“pixel-perfect” means that when you put something somewhere on a report design, it doesn’t move around. That this isn’t taken for granted is a rant for another time). I have more experience with Pentaho than with Jaspersoft, and once reviewed a book on Pentaho; I’ll try to give the two a fair evaluation, but in the end I can’t promise my greater experience with Pentaho won’t affect my conclusions one way or the other. Both suites are available in open source and commercial flavors; I’ll consider only the open source versions here.

First let me point out that Jaspersoft and Pentaho both produce business intelligence software suites. The two suites exist in both community (open source) and enterprise (commercial) forms, are well worth comparing in their entirety, but I’m focusing principally on the report builder component of each, because that’s where my recent experience has led me. These report builder packages allow users to …

Rails 3.1: Upgrading a Simple App — Part 2

By Steph Skardal
September 29, 2011

I recently wrote about upgrading a simple Rails app, which involved applying routing, mailer, ActiveRecord, etc. updates to my Rails 2.1.2 application. An equally important part of the upgrade is working with the asset pipeline, a framework that creates an architecture for managing JavaScript, CSS, and image assets in your Rails 3.1 application.

File Reorganization

Prior to the upgrade, my assets were organized in the following structure:

RAILS_ROOT/
  public/
    javascripts/
      jquery.site.js
      jquery.home.js
      jquery.services.js
      jquery.team.js
      jquery.bios.js
      ...
    stylesheets/
      site.css
    images/
      .. a lot of images ..

As you can see, the JavaScript files were already split into page specific code that was included where it was needed. But the application had one global stylesheet which included styles for the entire site. In general, the site followed performance best practices of minimizing http requests with this organization.

In Rails 3.1, the generators encourage you to build out individual JavaScript and CSS files for each controller by creating the files upon each controller instantiation. While in development, those files are …

PostgreSQL Serializable and Repeatable Read Switcheroo

By Greg Sabino Mullane
September 28, 2011

PostgreSQL allows for different transaction isolation levels to be specified. Because Bucardo needs a consistent snapshot of each database involved in replication to perform its work, the first thing that the Bucardo daemon does when connecting to a remote PostgreSQL database is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;

The ‘READ WRITE’ bit sets us in read/write mode, just in case the entire database has been set to read only (a quick and easy way to make your slave databases non-writeable!). It also sets the transaction isolation level to ‘SERIALIZABLE’. At least, it used to. Now Bucardo uses ‘REPEATABLE READ’ like this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ WRITE;

Why the change? In version 9.1 of PostgreSQL the concept of SSI (Serializable Snapshot Isolation) was introduced. How it actually works is a little complicated (follow the link for more detail), but before 9.1 PostgreSQL was only sort of doing serialized transactions when you asked for serializable mode. What it was really doing was repeatable read and not trying to really serialize the transactions. In 9.1, PostgreSQL is doing true serializable transactions. It also adds a new distinct …

Another Post-Postgres Open Post

By Josh Williams
September 22, 2011

Well, that was fun! I’ve always found attending conferences to be an invigorating experience. The talks are generally very informative, it’s always nice to put a face to names seen online in the community, and between the “hall track”, lunches, and after-session social activities it’s difficult to not find engaging discussions.

My favorite presentations:

• Scaling servers with Skytools — seeing what it takes to balance several high-velocity nodes was intriguing.
• Mission Impossible — lots of good arguments for why Postgres can be an equivalent, nay, better replacement for an enterprise database.
• The PostgreSQL replication protocol — even if I never intend to write something that’ll interact with it directly, knowing how something like the new streaming replication works under the hood goes a long way to keeping it running at a higher level.
• True Serializable Transactions Are Here! — I’ll admit I haven’t had a chance to fully check out the changes to Serializable, so getting to hear some of the reasoning and stepping through some of the use cases was quite helpful.

But what of my talks? Monitoring went well—​it seemed to get the message out. There was a lot of “gee, I have Postgres, …

Rails 3.1: Upgrading a Simple App — Part 1

By Steph Skardal
September 21, 2011

Here at End Point, I’ve worked with a few Rails 3 applications in production and a couple of Rails 3.1 apps in development, so I’ve become familiar with the new features and functionality including the Rails 3.1 Asset Pipeline that I mentioned earlier this year. I thought it was a good time to upgrade our website to Rails 3.1 and share the experience.

To start, here’s a quick summary of our website:

• Simple Rails application running on Rails 2.1.2 with no database
• Static pages throughout the site, fully cached
• Rake tasks to generate partials throughout the site to display dynamic blog content
• Site uses a moderate amount of jQuery and jQuery plugins.
• Site is optimized in terms of asset serving (ETags, Expires headers, CSS sprites, etc.)

While I’ve worked with a few Rails 3 apps, I haven’t been involved in the actual upgrade process myself. There are plenty of resources out there with upgrade advice, including a few RailsCasts (one, two, and three). My favorite resource was the rails_upgrade gem, a gem that is now officially supported by Rails to help with the upgrade process. I followed the instructions to install the gem (script/plugin install …

Headed out to PgWest next week

By David Christensen
September 20, 2011

I’m gearing up to go out to San Jose to attend and speak at the PG West PostgreSQL conference in sunny San Jose. (Does anyone have directions…?)

I’m excited to again meet and mingle with more PostgreSQL experts and enthusiasts and look forward to the various talks, technical discussions, and social opportunities. My talk will be on Bucardo and many uses for it as a general tool. It’ll also cover additional changes coming down the pipe in Bucardo 5.

I look forward to seeing everyone!