Distributed databases: a series of posts including 2-phase commit in Postgres

There’s a fantastic set of blog posts about distributed databases and network partitioning, starting with this post explaining the perils of trying to “communicate with someone who doesn’t know you’re alive.”

The next post is about Postgres and 2-phase commit. And there are four additional posts in the series.

The whole series worth reading for anyone interested in data stores, consistency and Postgres! :)

Migrations with Alembic: a lightspeed tour

I’ve got a Beer & Tell to give about alembic. Alembic is a migration tool that works with SQLAlchemy. I’m using it for database migrations with PostgreSQL.

So, here’s what I want to say today:

The most difficult thing to deal with so far are the many User Defined Functions that we use in Socorro. This isn’t something that any migration tools I tested deal well with.

Happy to answer questions! And I’ll see about making a longer talk about this transition soon.

PostgreSQL security releases now available: versions 9.2.4, 9.1.9, 9.0.13 and 8.4.17

PostgreSQL Global Development Group has just released updates for all currently supported versions of PostgreSQL.

From the release announcement:

The PostgreSQL Global Development Group has released a security update to all current versions of the PostgreSQL database system, including versions 9.2.4, 9.1.9, 9.0.13, and 8.4.17. This update fixes a high-exposure security vulnerability in versions 9.0 and later. All users of the affected versions are strongly urged to apply the update immediately.

A major security issue fixed in this release, CVE-2013-1899, makes it possible for a connection request containing a database name that begins with “-” to be crafted that can damage or destroy files within a server’s data directory. Anyone with access to the port the PostgreSQL server listens on can initiate this request. This issue was discovered by Mitsumasa Kondo and Kyotaro Horiguchi of NTT Open Source Software Center.

I wanted to highlight a couple things from the FAQ we developed for this release.

  1. There are no known exploits for the major security issue fixed by this release. The vulnerability was discovered through security testing conducted by NTT.
  2. Only users of 9.0 PostgreSQL and higher are affected by the major vulnerability.
  3. Affected users are those who allow unrestricted access to the network port PostgreSQL listens on. If you allow anyone, without IP address whitelisting, firewalling or some other kind of network-based access control, to connect to your network port, you are especially vulnerable.

Upgrading from minor version (9.2.3 to 9.2.4, for example) only requires that you install the new binaries and then restart PostgreSQL.

Additionally, if you are using GiST indexes, read the detailed notes in the release announcement to see if you are using features that require you to REINDEX your GiST indexes.

Please update as soon as possible!

Many thanks to our volunteer packagers who worked hard for the past several weeks to make this release possible. All PostgreSQL software releases are managed by volunteers.

A rosetta stone for Mac OS X installers for PostgreSQL

I’m no longer using Mac OS X for my primary desktop, but many of my coworkers and friends do. Particularly developers writing applications that use PostgreSQL (aka Postgres) for their data storage.

I’ve spent a lot time over the last few years troubleshooting people’s Postgres installs in the following, very common, situations:

  • A developer installed Postgres on their Mac laptop >1 year ago
  • Now they need to upgrade their Postgres to help me, or support a new application that needs new features
  • They have an old database they’d like to migrate to the new version
  • They have no idea which particular Mac OS X installer they used last time

For this exact situation, I have documented some features of the Mac OS X Installers for Postgres.

And, I felt so good to see this right after I posted the wiki page earlier today:

@zacduncan: “@selenamarie This is helpful to me at this very moment. Thank you. ”

\o/

Save the Ada Initiative

If you believe that women are a crucial part of the future of free and open source software, you should give to the Ada Initiative.

If you think we should have more women contributing, talking about and using free and open source software, you should donate to the Ada Initiative today.

I spent this past summer working with Mary, Valerie and the many supporters and contributors to the Ada Initiative. I talked to past donors, and spent a lot of time writing and thinking about how the Ada Initiative has evolved.

I met hundreds of people in person and online who believe not only that the Ada Initiative is a crucial advocate for change in the world of open source, but that establishing gender balance in open source through their work is a worthwhile, achievable goal. That work includes research, writing, training and creating culture and community specifically designed for women to flourish.

They’ve created strong relationships across project, business and ideological boundaries, through their board, advisors and AdaCamps.

I’m a member of the Advisors board, a major contributor to PostgreSQL and a data architect at Mozilla. These relationships have formed into a strong, diverse and visible alliance of women in open technology.

Because of the Ada Initiative’s work, I have seen an important shift from identifying problems to seeking solutions among my colleagues in open source. This work is made possible because TAI provides full-time employment to focus, write about and act on these solutions. Their work cannot continue without your support.

Between now and October 31, you can be the crucial donors who made this organization succeed in 2012. If you work for Microsoft, Google or Red Hat your donation with be doubled thanks to charitable giving matching programs. And individuals like Sumana Harihareswara and Leonard Richardson are sponsoring matching grants.

Social change is never easy, and the organizations like the Ada Initiative, who chose to step into the void, need our support.

Take a few minutes and give to the Ada Initiative, to Mary and Val, and help their work continue in 2013.

Wrapping up Postgres Open, new job, shift away from twitter

Last week in Chicago was amazing! 37 speakers, an incredible keynote by Jacob Kaplan-Moss (video coming soon!) and re-connecting with all the great people in Chicago. We announced a new conference committee for next year’s conference, and said we’d do it again in September in Chicago! That group is just getting started now, and will have some announcements for everyone in the coming weeks.

I’m going to be busy with a new job at Mozilla starting Monday, working on databases with the WebTools team.

Another small change is: I’m writing a few times a day to my tumblr and I’ve just stopped using twitter for the next few weeks. In the last day, I have really only thought about one or two things to share that would have been more than fleetingly useful. As I come across things, I’ll be sending them to the tumblr instead.

I’m also looking for patches and projects to work on for Postgres itself. During Thursday’s code sprint, I picked up an old patch for config directories, and today I spent some time re-generating a list of contributor names for the last 5 major versions of Postgres.

As usual, I feel so energized from hanging out with my favorite Postgres people. I’m only sad that I won’t see most of them in person again until next year.

What features do developers get excited about in Postgres?

I’m here at DjangoCon in Washington, DC and thinking about what it is that developers are currently excited about in Postgres.

Postgres hackers are often very focused on solving our own problems, problems people bring up on our mailing lists and dealing with database scaling, replication and data management.

Developers using Postgres seem more interested in the features which make creating applications easier and removing complexity from architecture.

So, what are they interested in?

The features that I hear mentioned most often include:

(thanks to @ipmb for the list in a lightning talk today!)

What are the features you hear about from developers? Or if you’re a web developer, what are your favorite features in PostgreSQL?

While we’re here, let’s fix computer science education: DjangoCon keynote and resources

My keynote today is done, the resources list is here and the slides are below. I wrote slightly different text to address our experience here in the US, but a mostly-complete transcript of the talk is here.

A ton of people came up to me after the talk and we started talking about all the ways that we might be able to solve problems. I created a mailing list for our first few discussions. If you are a person that doesn’t like google groups, contact me, as I of course can set up something that’s outside of that infrastructure if we have enough people who’d prefer a different place to have this conversation.

We have a plan to contact teachers in our local communities, and ask them what they need that we as open source software developers could help them with. And we all agreed that want to build things, but we’re pausing for a minute to ask the teachers around us what they need first.

For some background, the key bits of reading you should do to get up to speed are the following:
Continue reading

FrOSCon: Mistakes were Made: Education Edition talk slides and notes

I just finished giving my keynote at FrOSCon, and am pasting the notes I spoke from below. This was meant to be read aloud, of course. Where it says [slide] in the text is where the slides advance.

Update: My slides are now available on the FrOSCon site.

FrOSCon – Mistakes Were Made: Education Edition

[slide]

Thank you so much for inviting me here to FrOSCon. This is my first time visiting Bonn, and my first time enjoying K├Âlsch. I enjoyed quite a lot last night at the social event.

Especially, I would like to thank Scotty and Holgar who picked me up at the train station, Inga who talked with me at length on Thursday night. All the volunteers who have done a terrific job making this conference happen. Thank you all so much for a wonderful experience, and for cooking all the food last night!

And I promised to show off the laser etching on my laptop I had done here by the local hackerspace. I come from the PostgreSQL community, so I got an elephant etched into the laptop. It only costs 10 euro and looks awesome.

[slide]

I’ve also made a page of resources for this talk. I’ll be quoting some facts and figures and this pirate pad has links to all the documents I quoted.

For those of you from countries other than Ireland, Great Britain, United States, German and Turkey – if you know where to get a copy of computer science curriculum standards for your country, please add a link. Right at the top of this pirate pad is a link to another pirate pad where we’re collecting links to curriculum standards.

[slide]

And finally, this talk is really a speech, without a lot of bullet points. So, the slides will hopefully be helpful and interesting, but occasionally I will be showing nothing on a slide as I speak. This is a feature, not a bug.

[slide]

For the past few years, I’ve been giving talks about mistakes, starting with problems I had keeping chickens alive in my backyard. Here’s a map of my failures. Scotty is familiar with the video that is online that tells the whole story of how all these chickens died.

Next, I talked about system administration failures – like what happens when a new sysadmin runs UNIX find commands to clean up — and delete all the zero length files, including devices, on a system. Or how to take down a data center with four network cables and spanning tree turned off. Here’s a tip: it really only takes first cable.
Continue reading