The B-52 Stratofortress was designed in the years after World War II to serve as a heavy bomber for the Cold War and took its maiden flight in 1952. The Cold War came and went, but the B-52 is still in active military service 94 of the 102 H-model B-52 aircraft built in the 1960’s remain on duty.

This year marks the sixtieth year of B-52 operation  that is a laudable goal for any piece of technology, but even more so considering the demanding reliability and performance requirements for military hardware. The plan is for the aircraft to remain in service through 2044. This 2002
article has some relevant quotes:

• In an era when a 9-month-old laptop already feels retro, when people who keep their cars for six years are considered quaint, the fact that the most powerful military in the world relies on a fleet of 40-year-old bombers [now 50] is pretty astonishing.
• And the B-52 is not some creaky relic that the military keeps around for air shows. The planes have seen more combat in this decade than in the previous three decades combined.
• If the B-52 does remain in service for 80 years, it will be like using a weapon from the Civil War to win World War II.
• “Right now, there are three generations of pilots who have flown that plane — grandfather, father, and son — in the same family. If it lasts until 2040 [now 2044], five generations will have flown the same plane.”

So, how does the B-52 relate to Postgres? Ignoring the B-52’s destructive purpose, there are some similarities:

• Both were developed by the U.S. Defense Department (Postgres was initially funded by DARPA)
• Both were developed decades ago and have remained in use far longer than expected
• Both are easily enhanced with new technology, which has increased their longevity
• Both operate in demanding environments where reliability and performance are critical
• Both are inexpensive, compared to the alternatives
• Both are covered on Slashdot
  

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.

Postgres, since version 8.2, has supported the ability to create indexes without blocking writes (INSERT, UPDATE, or DELETE) on the table being indexed. This is done by adding the keyword CONCURRENTLY to the CREATE INDEX command. (Reads are never blocked by CREATE INDEX.)

Implementing this feature was very complex, and it has been recently discovered that all versions of pg_upgrade have a bug related to processing such indexes. The problem revolves around the rare case when CREATE INDEX CONCURRENTLY fails. When this happens, an invalid index remains in the system. The invalid index is incomplete and is not used by queries. The pg_upgrade problem is that pg_dump dumps it as a valid index. While a normal restore of pg_dump output would create a valid index, pg_upgrade moves the old index file into place without recreating it  hence, an invalid index file is upgraded as a valid index. In this case, the index could return invalid results or crash the server. The steps necessary to trigger this bug are:

• Use of CREATE INDEX CONCURRENTLY
• Failure of CREATE INDEX CONCURRENTLY
• Not dropping or recreating the index after failure
• Use of pg_upgrade without the patch applied on December 11, 2012. The patch uses this query to throw an error if invalid indexes are found before the upgrade begins (pg_upgrade –check also performs this check):

SELECT n.nspname, c.relname
FROM   pg_catalog.pg_class c, pg_catalog.pg_namespace n,
       pg_catalog.pg_index i
WHERE  (i.indisvalid = false OR i.indisready = false) AND
       i.indexrelid = c.oid AND c.relnamespace = n.oid AND
       n.nspname != 'pg_catalog' AND
       n.nspname != 'information_schema' AND
       n.nspname != 'pg_toast'

Continue Reading »

Having just reported a rare but serious bug in pg_upgrade (), I wanted to address the meticulous effort the Postgres community takes in developing software. While most end-users just see the software releases and the release notes, there is obviously a huge amount of effort that goes into to creating those releases. Postgres backend developers are acutely aware of that effort, as every patch they submit receives detailed scrutiny to detect flaws or potential improvements.

Databases, like operating systems, are held to a high standard for reliability because failure often causes downtime for organizations. Also, databases lock user data into their software beyond many operating system use cases. Again, another reason that database server work requires serious focus.

However, the focus on reliability can’t be borne alone by the database server developers. Users must also provide a reliable platform to run database software. Postgres can’t maintain high reliability if it is dependent on an unreliable platform. Here are some of the things users can do to provide a reliable platform for Postgres:

• Choose an operating system whose focus is on reliability, rather than desktop user experience or supporting the latest hardware or software
• Choose hardware designed for 24-hour operation in a demanding environment, not desktop hardware Use hardware that reports errors, like ECC memory and
• SMART storage
• Make sure your storage system performs reliable writes
• Have on-site and off-site backups for cases when disasters happen
• Educate administrative staff so mistakes don’t cause downtime

Continue Reading »

Today we formally announced the release of xDB Replication Server v5.0.  Its an exciting release delivering one of the most asked about features we get amongst all our products:  multi-master replication.  What I find especially intriguing about this feature is the additional versatility it adds to this already indispensable Postgres tool.

Multi-master replication allows separate database instances to be updated at the same time by users and then exchange/consolidate their data with each other providing a unified view of all the data for all users in near real time.  This provides a much better solution to consolidating disparate data from similar systems than the clunky alternative of one or more daily batch updates which always leave long periods of stale or out of date data.

Those that stand the most to gain from multi-master replication are organizations with geo-dispersed databases where each locality needs to enter and update data but also needs an integrated view of all the other locations’ data. Rather than all users accessing a single central database which may slow down under heavy loads or impose latency delays for far flung offices, each location gets its own master database.  Each location updates their own data which is then synchronized with the other masters in near real time.  No network latencies and no stale data.

There are many benefits to this multi-master configuration.  Write operations tend to be faster at the local level.  Read operations scale better off multiple machines rather than a single central machine. And overall write availability can be improved by setups that re-route operations from a downed master to one of the other masters.  When the downed master returns, it re-synchronizes with the other masters.

The single master version of xDB Replication Server has for years provided multiple solutions to thorny problems in Postgres data centers:  read scaling, improved OLTP performance, standby backup databases, data migration, data integration with Oracle and SQL Server, scheduled replication, filtered replication, initializing new systems, and testing systems in parallel.

Multi-master replication brings some exciting new options to a truly versatile multi-function tool.  Every Postgres shop needs a tool like this.  Download it and check out the blades and see which ones you can put to use today.

Oh, I almost forgot to mention, we also dropped the price on xDB, so there is no excuse not to give it a test run.

I already covered how to set Postgres configuration variables at various levels, and the power of reloading the postgresql.conf configuration file.

While setting configuration variables at the SQL level is very easy to program, modifying the postgresql.conf file programatically can be more complex. (The postgresql.conf file exists in the cluster data directory.) Some administrators use sed or perl to modify the file directly, but a simpler solution is to use the postgresql.conf include directive:

include 'filename'

Continue Reading »

Having returned to blogging after a two-month break, I wanted to point out two new things on my blog site. First, months ago, I started categorizing my blog posts — the categories appear as small images next to the blog titles.

This has allowed me to automatically create a category index of all my posts. (The index is accessible by clicking on View Index at the top of my
blog detail page, and then choosing View Category Index.) For example, the page collects all my tips together for easy review. (See “Tip” at the bottom of the page.) I wanted to mention this because those tips have been collected from discussions in many of my training classes, and might be helpful for new users. My blog posts about conferences, pg_upgrade, and performance are similarly organized.

Second, my old commenting service shut down, so I have migrated to a new blog commenting service. I was able to import all 362 comments into the new service, so nothing was lost.

Post a Comment

I began working on pg_upgrade (previously called pg_migrator) in 2009 after my employer EnterpriseDB changed the license of their binary migration tool to BSD and instructed me to work on improving it. My initial goal was to produce a reliable and popular method for binary upgrades (avoiding data dump/restore). That goal was achieved in 2011, and since then I have been focusing on usability and performance enhancements.

One repeated request has been for better performance, especially for databases with many tables. You might wonder, “Isn’t pg_upgrade already faster than dumping/reloading the database?” Yes, it is, but that doesn’t mean it can’t be made even faster.

After receiving scattered reports of link-mode upgrades taking 45 minutes or more, I dug in and made some major performance improvements in Postgres 9.2. However, that wasn’t sufficient, and I got slowness reports from users using pg_upgrade 9.2. Therefore, I have been back on pg_upgrade performance duty this past month, and I got good results!

Continue Reading »

I attended two interesting conferences in October. The first was the Highload ++ conference in Moscow. The two-day conference had one full day of Postgres talks, thanks to dedicated work by Nikolay Samokhvalov. The conference has grown significantly in recent years, and I am hopeful Postgres will continue to play a significant role.

The second was the PostgreSQL Conference in Prague, which had record attendance of 291. The conference had many highlights for me — it opened with a keynote by Joe Celko.

Jonathan S. Katz’s presentation, Marketing PostgreSQL (slides), inspired me because he accurately portrayed the methodical, results-driven approach organizers used to grow the New York PostgreSQL User Group. Rather than do what was easy, they analyzed what efforts would lead to significant user group growth, and pursued them consistently.

Continue Reading »