Our Blog

Splitting Postgres pg_dump into pre and post data files

By Greg Sabino Mullane
January 20, 2010

I’ve just released a small Perl script that has helped me solve a specific problem with Postgres dump files. When you use pg_dump or pg_dumpall, it outputs things in the following order, per database:

1. schema creation commands (e.g. CREATE TABLE)1. data loading command (e.g. COPY tablename FROM STDIN)1. post-data schema commands (e.g. CREATE INDEX)

The problem is that using the –schema-only flag outputs the first and third sections into a single file. Hence, if you load the file and then load a separate –data-only dump, it can be very slow as all the constraints, indexes, and triggers are already in place. The split_postgres_dump script breaks the dump file into two segments, a “pre” and a “post”. (It doesn’t handle a file with a data section yet, only a –schema-only version)

Why would you need to do this instead of just using a full dump? Some reasons I’ve found include:

• When you need to load the data more than once, such as debugging a data load error.
• When you want to stop after the data load step (which you can’t do with a full dump)
• When you need to make adjustments to the schema before the data is loaded (seen quite a bit on major version upgrades)

Usage …

Gathering server information with boxinfo

By Greg Sabino Mullane
January 15, 2010

I’ve just publicly released another Postgres-related script, this one called “boxinfo”. Basically, it gathers information about a box (server), hence the catchy and original name. It outputs the information it finds into an HTML page, or into a MediaWiki formatted page.

The goal of boxinfo is to have a simple, single script that quickly gathers important information about a server into a web page, so that you can get a quick overview of what is installed on the server and how things are configured. It’s also useful as a reference page when you are trying to remember which server was it that had Bucardo version 4.5.0 installed and was running pgbouncer.

As we use MediaWiki internally here at End Point (running with a Postgres backend, naturally), the original (and default) format is HTML with some MediaWiki specific items inside of it.

Because it is meant to run on a wide a range of boxes as possible, it’s written in Perl. While we’ve run into a few boxes over the years that did not have Perl installed, the number that had any other language you choose (except perhaps sh) is much greater. It requires no other Perl modules, and simply makes a lot of system calls.

Various information …

Rails Ecommerce with Spree: Customizing with Hooks Comments

By Steph Skardal
January 13, 2010

Yesterday, I went through some examples using hook and theme implementation in Spree, an open source Ruby on Rails ecommerce platform. I decided to follow-up with closing thoughts and comments today.

I only spent a few hours working with the new Spree edge code (Version 0.9.5), but I was relatively happy with the Spree theme and hook implementation, as it does a better job decoupling the extension views with Spree core functionality and views. However, I found several issues that are potential areas for improvement with this release or releases to come.

Theme too clunky?

One concern I have is that the entire “views” directory from SPREE_ROOT/app was moved into the theme with this theme-hook work (all of the “V” in MVC). Yesterday, I discussed how WordPress had designed a successful theme and plugin interaction and one thing I mentioned was that a WordPress theme was lightweight and comprised of several customer-facing PHP files (index, single post page, archive pages, search result page). Moving all of the Spree core views to the theme presents a couple of issues, in my opinion:

• A developer that jumps into theme development is immediately met with more than 50 files in the theme …

Rails Ecommerce with Spree: Customizing with Hooks Tutorial

By Steph Skardal
January 12, 2010

In the last couple months, there’s been a bit of buzz around theme and hook implementation in Spree. The Spree team hasn’t officially announced the newest version 0.9.5, but the edge code is available and developers have been encouraged to work with the edge code to check out the new features. Additionally, there is decent documentation about theme and hook implementation. In this article, I’ll go through several examples of how I would approach site customization using hooks in the upcoming Spree 0.9.5 release.

Background

I’ve been a big proponent of how WordPress implements themes, plugins, and hooks in the spree-user Google group. The idea behind WordPress themes is that a theme includes a set of PHP files that contain the display logic, HTML, and CSS for the customer-facing pages:

• index
• a post page
• archive pages (monthly, category, tag archives)
• search result page
• etc.

In many cases, themes include sections (referred to as partial views in Rails), or components that are included in multiple template pages. An example of this partial view is the sidebar that is likely to be included in all of the page types mentioned above. The WordPress theme community is abundant; there are …

Postgres Upgrades — Ten Problems and Solutions

By Greg Sabino Mullane
January 11, 2010

Upgrading between major versions of Postgres is a fairly straightforward affair, but Murphy’s law often gets in the way. Here at End Point we perform a lot of upgrades, and the following list explains some of the problems that come up, either during the upgrade itself, or afterwards.

When we say upgrade, we mean going from an older major version to a newer major version. We’ve (recently) migrated client systems as old as 7.2 to as new as 8.4. The canonical way to perform such an upgrade is to simply do:

pg_dumpall -h oldsystem > dumpfile
psql -h newsystem -f dumpfile

The reality can be a little more complicated. Here are the top ten gotchas we’ve come across, and their solutions. The more common and severe problems are at the top.

1. Removal of implicit casting

Postgres 8.3 removed many of the “implicit casts”, meaning that many queries that used to work on previous versions now gave an error. This was a pretty severe regression, and while it is technically correct to not have them, the sudden removal of these casts has caused lots of problems. Basically, if you are going from any version of PostgreSQL 8.2 or lower to any version 8.3 or higher, expect to run into …

Postgres SQL Backup Gzip Shrinkage, aka Don’t Panic!!!

By David Christensen
January 9, 2010

I was investigating a recent Postgres server issue, where we had discovered that one of the RAM modules on the server in question had gone bad. Unsurprisingly, one of the things we looked at was the possibility of having to do a restore from a SQL dump, as if there had been any potential corruption to the data directory, a base backup would potentially have been subject to the same possible errors that we were trying to restore to avoid.

As it was already the middle of the night (anyone have a server emergency during the normal business hours?), my investigations were hampered by my lack of sleep.

If there had been some data directory corruption, the pg_dump process would likely fail earlier than in the backup process, and we’d expect the dumps to be truncated; ideally this wasn’t the case, as memory testing had not shown the DIMM to be bad, but the sensor had alerted us as well.

I logged into the backup server and looked at the backup dumps; from the alerts that we’d gotten, the memory was flagged bad on January 3. I listed the files, and noticed the following oddity:

 -rw-r--r-- 1 postgres postgres  2379274138 Jan  1 04:33 backup-Jan-01.sql.gz
 -rw-r--r-- 1 postgres postgres …

DevCamps on different systems, including Plesk, CPanel and ISPConfig

By Ron Phipps
January 8, 2010

In the last few months I’ve been active setting up DevCamps for several of our newer clients. DevCamps is an open source development environment system, that once setup, allows for easily starting up and tearing down a development environment for a specific site/code base.

I’ve done many camps setups, and you tend to run into surprises from system to system, but what was most interesting and challenging about these latest installs was that they were to be done on systems running Plesk, CPanel, and ISPConfig. Some things that are different between a normal deployment and one on the above mentioned platforms are:

• On the Plesk system there was a secured Linux called ‘Atomic Secured Linux’ which includes the grsecurity module. One restriction of this module is (TPE) Trusted Path Execution which required the camp bin scripts to be owned by root and the bin directory could not be writable by other groups, otherwise they would fail to run.

• Permissions are a mixed bag, where typically we set all of the files to be owned by the site owner, in Plesk there are special groups such as psacln that the files need to be owned by.

• On the CPanel system we needed to move the admin images for …

SSHFS and ServerAliveInterval

By Ethan Rowe
January 7, 2010

If you’re using SSHFS (as I do recently since OpenVPN started crashing frequently on my OpenBSD firewall), note that the ServerAliveInterval option for SSH can have significant impact on the stability of your mounts.

I set it to 10 seconds on my system and have been happy with the results so far. It could probably safely go considerably higher than that.

It’s not on by default, which leaves the stability of your SSH tunnels up to the success of TCP keepalive (which is on by default). On my wireless network, that alone has not been sufficient.