I often ask people about their backup configuration because I want to make sure my own server backup procedures are sufficient. I remember asking someone a few years ago about their personal backup methods, and they said they used raid1 (mirroring), and didn’t need backups. That seemed insufficient, and when I asked about recovery of deleted or improperly-modified files, I wasn’t given a satisfactory answer. The bottom line is that while raid1 protects against the failure of a drive, it does not address other causes of data loss. Here is a table I created to illustrate the data loss and recovery options I use for my home server:

How likely are these failure cases? This report (article) shows the statistics for data retention failure:

As you can see, there are many failure cases which raid1 cannot recover. Don’t believe it? Watch this video about a Pixar film almost lost due to file deletion (and backup failure).

Continue Reading »

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

At the PGCon 2012 Developer Meeting, we discussed the schedule for PostgreSQL 9.3 development.  Here’s what we agreed:

CommitFest #1: June 15, 2012 – July 15, 2012
CommitFest #2: September 15, 2012 – October 15, 2012
CommitFest #3: November 15, 2012 – December 15, 2012
Planning Week: December 8, 2012 – December 15, 2012
CommitFest #4: January 15, 2013 – February 15, 2013
Final Triage: February 1, 2013 – February 7, 2013

This is basically the same schedule we used for the PostgreSQL 9.2 development cycle, but we’ve added a “planning week” and a “final triage”.  The idea of the planning week, and also of the final triage week, is to identify which large patches still have a hope of getting done in time to be committed to PostgreSQL 9.3.  In that way, if a patch has no chance of being ready for commit, people can stop working on it and focus their efforts on those that do.  Hopefully, this will make the final CommitFest go a little more smoothly than it did this time, with less burn-out and a quicker denouement.

We also agreed on one significant change to the review process: if you submit a patch, you must also volunteer to review a patch.  In earlier development cycles, we have been able to rely on volunteer reviewers to do much of the reviewing work, but that didn’t work as well during the 9.2 cycle.  Although I’m hopeful that this will ease some of the reviewing crunch, I’d also like to underscore the importance of volunteer reviewers.  Reviewing is a great way to get involved in the development process without spending too much time on it, and often leads to significant improvements in design and usability as well as code quality.  It also helps to ensure a variety of opinions about important patches, which usually leads to a stronger product overall, and, at least in my experience, it is also a great way to learn more about PostgreSQL.

During Robert Haas’s talk (slides) last week at PGCon, I realized that three of our major performance features for Postgres 9.2 involved representing data in a more compact form:

• Allow queries to retrieve data only from indexes, avoiding heap access
• Allow hint bits to be set sooner
• Move the frequently accessed members of the PGPROC shared memory array to a separate section, for performance

The first item reduces disk reads, the second reduces memory reads, and the third reduces CPU cache line accesses.

With pg_upgrade gaining wide acceptance and the Firefox browser implementing a continuous upgrade model, it might be time to consider auto-upgrades for Postgres. Consider this possible future output:

$ psql test
psql (12.3.7)
Postgres is up to date
Type "help" for help.

test=>

Continue Reading »

Pg_upgrade (documentation, presentation) was created in 1998 to allow in-place major version upgrades of Postgres. It was written as a shell script, as were many of our command-line administration tools at the time. (Postgres didn’t support Windows natively until 2005). In those early years, Postgres changed its on-disk format frequently in major releases, causing pg_upgrade to be only occasionally useful for major version upgrades (6.5, 7.1, disabled in 2002).

Postgres feature additions soon made the shell script method unworkable, and binary-upgrades were unsupported for many years. In 2006, EnterpriseDB took on the job of rewriting pg_upgrade in C, a task that had been discussed many years on the mailing lists — this email
thread, titled “Upgrading rant”, give you an idea of the tone of previous discussions.

In 2009, EnterpriseDB realized that a successful binary-upgrade utility had to be community-developed, so they changed the license from GPL to BSD and encouraged me to work with the community to improve the tool. In 2010, pg_upgrade was added to the official Postgres 9.0 release. Postgres 9.1 saw only performance improvements. Postgres 9.2 will improve the user interface and error reporting. It is significant that pg_upgrade has not required large changes to support new major releases, only minor improvements.

Continue Reading »

PGCon 2012 is now over. Surprisingly, all the talks that excited me were from the second/last day. My morning started with WAL Internals Of PostgreSQL (slides) by Amit Kapila. I rarely see this topic covered, and his comparisons to Oracle’s transaction logging were fresh. Also in the morning, Hooks in PostgreSQL by Guillaume Lelarge: I also rarely see this covered. It highlighted the C-language plug-in capabilities of Postgres.

My afternoon highlight was Big Bad “Upgraded” PostgreSQL by Robert Treat. After the occasional maligning of pg_upgrade (usually in jest), it was great to hear a big pg_upgrade success story. (This OmniTI blog entry has the details. This talk was also presented at PG Corridor Days DC.)

As part of yesterday’s PGCon Developer Meeting, I hosted a discussion about adding resource parallelism to Postgres, which I blogged about previously. Josh Berkus has kindly summarized the results of that discussion. I am hoping to help track and motivate progress in this area in the coming months; it is a multi-year project to complete.

You might have heard of the template1 database before, or seen it in the output of pg_dumpall:

REVOKE ALL ON DATABASE template1 FROM PUBLIC;
…

Continue Reading »

I have completed the Postgres 9.2 release notes I started seven days ago. Writing the release notes is always a taxing experience. I have to basically dedicate an entire week to the process of digesting 1100 commit messages to produce 3000 lines of SGML text. Once I am done though, it is rewarding to see the finished product. It is like working on a painting all year, and spending a frustrating week framing it and hanging it on the all — once it is hung, you stand and admire it, and forget much of the sweat it took to produce. I am sure many community members feel the same.

Curious about how the 9.2 release item count compares to previous major releases? Here are the results:

Continue Reading »

My children have traveled to many conferences with me, and have heard many webcasts and training calls at home. I guess, after hearing about Postgres so often, they pick up certain phrases of interest, and the big one for them is “shared buffers”. Anytime someone uses those words, they start howling and make up some funny sentence using the words.

There are a variety of beer drinking games that trigger drinking when a word is said — perhaps “shared buffers” is enough on its own to make a drinking game.