Our Blog

PostgreSQL tip: using pg_dump to extract a single function

By David Christensen
January 31, 2010

A common task that comes up in PostgreSQL is the need to dump/edit a specific function. While ideally, you’re using DDL files and version control (hello, git!) to manage your schema, you don’t always have the luxury of working in such a controlled environment. Recent versions of psql have the \ef command to edit a function from within your favorite editor, but this is available from version 8.4 onward only.

An alternate approach is to use the following invocation:

  pg_dump -Fc -s | pg_restore -P 'funcname(args)'

The -s flag is the short form of –schema-only; i.e., we don’t care about wasting time/space with the data. -P tells pg_restore to extract the function with the following signature.

As always, there are some caveats: the function name must be spelled out explicitly using the full types as they occur in the dump’s custom format (i.e., you must use ‘foo_func(integer)’ instead of ‘foo_func(int)’). You can always see a list of all of the available functions by using the command:

  pg_dump -Fc -s | pg_restore -l | grep FUNCTION

Postgres: Hello Git, goodbye CVS

By Greg Sabino Mullane
January 28, 2010

It looks like 2010 might be the year that Postgres officially makes the jump to Git. Currently, the project uses CVS, with a script that moves things to the now canonical Postgres Git repo at git.postgresql.org. This script has been causing problems, and is still continuing to do so, as CVS is not atomic. Once the project flips over, CVS will still be available, but CVS will be the slave and Git the master, to put things in database terms. The conversion from Git to CVS is trivial compared to the other way around, so there is no reason Postgres cannot continue to offer CVS access to the code for those unwilling or unable to use Git.

On that note, I’m happy to see that the number of developers and committers who are using Git—​and publicly stating their happiness with doing so—​has grown sharply in the last couple of years. Peter Eisentraut (with some help from myself) set up git.postgresql.org in 2008, but interest at that time was not terribly high, and there was still a lingering question of whether Git was really the replacement for CVS, or if it would be some other version control system. There is little doubt now that Git is going to win. Not only for the Postgres project, but …

Slony: Cascading Subscriptions

By David Christensen
January 28, 2010

Sometime you run into a situation where you need to replicate one dataset to many machines in multiple datacenters, with different costs associated with sending to each (either real costs as in bandwidth, or virtual costs as in the amount of time it takes to transmit to each machine). Defining a Slony cluster to handle this is easy, as you can specify the topology and paths taken to replicate any changes.

Basic topology:

• Data center A, with machines A1, A2, A3, and A4.
• Data center B, with machines B1, B2, B3, and B4.
• Data center C, with machines C1, C2, C3, and C4.

Figure 1: Non-cascaded slony replication nodes/pathways.

Node A1 is the master, which propagates its changes to all other machines. In the simple setup, A1 would push all of its changes to each node, however if data centers B and C have high costs associated with transfer to the nodes, you end up transferring 4x the data needed for each data center. (We are assuming that traffic on the local subnet at each data center is cheap and fast.)

The basic idea then, is to push the changes only once to each datacenter, and let the “master” machine in the data center push the changes out to the others in the data center. This …

Blog versus Forum, Blogger versus WordPress in Ecommerce

By Steph Skardal
January 25, 2010

Today, Chris sent me an email with two questions for one of our ecommerce clients:

• For ecommerce client A, should a forum or blog be added?
• For ecommerce client A, should the client use Blogger or WordPress if they add a blog?

These are relevant questions to all of our clients because forums and blogs can provide value to a static site or ecommerce site. I answered Chris’ question and thought I’d expand on it a bit for a brief article.

First, a rundown comparing the pros and cons of blog versus forum:

SEO 2010 Trends and Strategies

By Steph Skardal
January 22, 2010

Yesterday I attended SEOmoz’s webinar titled “SEO Strategies for 2010”. Some interesting factoids, comments and resources for SEO in 2010 were presented that I thought I’d highlight:

• Mobile browser search

  • Mobile search and ecommerce will be a large area of growth in 2010.
  • Google Webmaster Tools allows you to submit mobile sitemaps, which can help battle duplicate content between non-mobile and mobile versions of site content. Another way to handle duplicate content would be to write semantic HTML that allows sites to serve non-mobile and mobile CSS.

• Social Media: Real Time Search

  • Real time search marked its presence in 2009. The involvement of Twitter in search is evolving.
  • Tracking and monitoring on URL shortening services should be set up to measure traffic and benefit from Twitter.
  • Dan Zarrella published research on The Science of Retweeting. This is an interesting resource with fascinating statistics on retweets.

• Social Media: Facebook’s Dominance

  • Recent research by comScore has shown that 5.5% of all time on the web is spent in Facebook.
  • Facebook has very affordable advertising. Facebook has so much demographic and psychographic data that allows sites to deliver …

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 …