Wrong defaults for zone_reclaim_mode on Linux

My coworker Jeff Frost just published a writeup on "zone reclaim mode" in Linux, and how it can be a problem.  Since his post is rather detailed, I wanted to give a "do this" summary:

1. zone_reclaim_mode defaults to the wrong value for database servers, 1, on some Linux distributions, including Red Hat.
2. This default will both cause Linux to fail to use all available RAM for caching, and throttle writes.
3. If you're running PostgreSQL, make sure that zone_reclaim_mode is set to 0.

Frankly, given the documentation on how zone_reclaim_mode works, I'm baffled as to what kind of applications it would actually benefit.  Could this be another Linux misstep, like the OOM killer?

Today's Security Update: XML vulnerabilities

If you're subscribed to the PostgreSQL News feed (and if not, why aren't you?) you already know that we released another security patch today.  This update release patches two security holes having to do with XML functionality, which can be used by any authenticated user for privilege escalation.  Everyone should apply the updates at the next reasonable downtime, and users of contrib/xml2 should schedule a downtime this weekend.

You'll notice that this security update release is on a Friday.  This is not our normal practice, since it makes it difficult for IT departments to respond in a timely fashion (especially the folks in Australia/Japan, who will be getting the announcement on Saturday).  However, as our number of packagers for different platforms has grown, it's become increasingly difficult to schedule coordinated releases, especially during the summer and the India/Pakistan holiday season.  As it is, the Solaris folks will be getting their binaries late.

Anyway, about these vulnerabilities:  The first one is a vulnerability in the built-in xmlparse() function.  Among other things, this function used to allow users to validate the XML against a DTD contained in an external file.  However, a creative user would be able to call any file which the "postgres" user has permissions on, and read parts of that file in the error messages from the DTD reader.  Since "files the postgres user has access to" includes pg_hba.conf and .pgpass (if defined), this is a dangerous capability.  As such, we have had to disable the DTD-validation feature.  This validation feature may return at a later date if we can figure out a reasonable way to make it safe.

The second security hole is in the outdated extension (or "contrib module"), xml2.  Users still use xml2 rather than the built-in XML because of its XSLT support.  The problem is that xslt_process() had a documented "feature" which allowed the function to transform XML documents fetched from external resources, such as URLs -- without requiring superuser permissions.  Not only could this be used to read local files on the PostgreSQL server, it could be used to write them as well, making this a much worse security hole if you have xml2 installed.   As such, the xslt_process() feature has been disabled, and will probably not return.

We've been sitting on both of these patches for an embarassingly long time (for our project, at least), because we were looking for a solution which didn't involve disabling functionality which is potentially valuable to some users.  Sadly, we were not able to.

My Autumn Travel Schedule

Just updating folks on where I'll be and what I'll be presenting, in case anyone wants to say "hello" or buy me a beer:

August: LinuxCon, San Diego.  Presenting "The Accidental DBA".

September: Postgres Open, Chicago.  Presenting an updated "Super Jumbo Deluxe".    Also, doing a big PostgreSQL 9.2 talk for SFPUG.

October: pgconf.EU.  Not sure what I'll be presenting; talk acceptances aren't final yet.  But, hey, Prague!

November: no conferences, thank goodness.

December: Back to San Diego for Usenix LISA.  Working a booth and doing a Guru Session with Selena Deckelmann and Joe Conway, and likely a BOF as well.  Drop by the booth!   Possible San Francisco pgDay in December; watch this space for more information.

Launchpad minimizes downtime using 9.1 replication

Canonical staff member Robert Collins has a nice blog about how Launchpad reduced downtimes for agile schema deployment from an hour down to 5 seconds.  They did this over the last couple months by moving from Slony to Slony+pgBouncer and then to pgBouncer+Binary Replication.  This is what we make nice tools for; to make our users' lives easier.

MySQL-to-PostgreSQL Migration Data from The451.com

As you know, due to PostgreSQL's wide redistribution, worldwide user base, and liberal licensing policies (i.e. no registration required), hard data on PostgreSQL adoption is somewhat hard to come by.  That's why I'm very grateful to The451.com, an open-source-friendly analyst service, for sharing with me the PostgreSQL-relevant contents of their report, MySQL vs. NoSQL and NewSQL: 2011-2015.

The451.com is no stranger to open source databases; their analytics services are built using PostgreSQL, MySQL and probably others.  So their analysis is a bit more on-target than other analysts (not that I would be thinking of anyone in particular) who are still treating open source databases as a fringe alternative.  Matt Aslett is their database and information storage technology guru.

The part of their report I found the most interesting was this part:

In fact, despite significant interest in NoSQL and NewSQL products, almost as many MySQL users had deployed PostgreSQL as a direct replacement for MySQL (17.6%) than all of the NoSQL and NewSQL databases combined (20%).

And:

Our survey results support this anecdotal evidence, with PostgreSQL by far the most popular choice among users as a direct replacement for MySQL, ahead of MySQL derivatives such as MariaDB, Percona Server and Amazon's RDS (Relational Database Service).

I'd some idea that we were seeing a lot of post-Oracle-acquisition MySQL refugees in the PostgreSQL community.  However, given the strong appeal that the new databases have for web developers, I'd assumed that three times as many developers were going to non-relational solutions as were to PostgreSQL.  I'm pleasantly surprised to see these figures from the451.com.

Of course, this means that us PostgreSQL folks really need to work even harder on being welcoming to former MySQL users.  They're our users now, and if they have funny ideas about SQL syntax, we need to be encouraging and helpful rather than critical.  And, above all, not bash MySQL.

The summary report also has the first solid figure I've seen on PostgreSQL market share in a while (hint: it's higher than 10%).   You can access it here.  To access the full base report, you need to apply for a full free trial membership.  As a warning, that free trial registration feeds into their sales reps, so don't be surprised if you get a call later.

See you in Chicago!

Just bought my plane tickets for Postgres Open in Chicago.  I'm really looking forward to this one, which will be even more user/application-developer-oriented than the first Postgres Open.  We have a keynote by Jacob Kaplan-Moss, the founder of Django, and talks by staff from Heroku, Engineyard, Evergreen, and Paul Ramsey of PostGIS, as well as some of the usual suspects.  Register and buy a plane ticket now!  There's still time!

I'll be presenting an updated version of my PostgreSQL for data warehousing talk, Super Jumbo Deluxe.  My coworker Christophe will do PostgreSQL When It's Not Your Job.

Chicago is a great city to visit, too, and September is a good time to be there weather-wise.   It's generally sunny and pleasant but not too warm, and the flying spiders are gone.  There's tons of museums and world-renowned restaurants.  In fact, I'm bringing Kris this year.

Oh, and there's still Sponsorship slots open, hint, hint.

PostgresXC Live Streaming at SFPUG Aug. 7

Once again, we will have SFPUG Live streaming.  This month's presentation is Mason Sharp presenting PostgresXC -- the Clustered, Write-Scalable Postgres.  Video will be on Justin.TV; I will try to make HD video work this time.  We'll see!  

Streaming video should start around 7:15PM PDT, +/- 10 minutes.