Tag Archives: postgresql

postgresql, python , , , 4 Comments

A practical guide to using Alembic

I spent some time guiding a coworker through using Alembic for the first time with Socorro this morning and what follows are my notes from that meeting.

I’ve been using Alembic, a database schema migration tool, for about three months now, and really liking it a lot. I created a blog post that served as a slide deck for an internal team called A lightspeed tour of Alembic as my first stab at user education.

Setting things up initially was pretty simple, but explaining it to a coworker after I’d set everything up for myself proved slightly more difficult. Below are my notes on the differences between Alembic and some other migration tool.

Terminology

Alembic calls each migration a revision. Revisions know what order to be run in because each revision is given a down_revision to identify its parent. If down_revision is None, that revision is the very first revision according to Alembic. You can put your whole schema in that revision, or you can just start adding changes to this initial revision. Alembic doesn’t complain either way.

A best practice would likely be putting your entire model into the first revision. I may go back and “fix” this for us later. I opted to just have the default use case be to create a database fresh with a tool we call setupdb_app.py.

If you’re looking to migrate to using alembic, you’ll also need to use SQLAlchemy. I used sqlautocode for my initial schema reflection, and there’s a new tool sqlacodegen you may want to check out for generating your SQLAlchemy models for the first time.

Preparation: edit config and activate a virtualenv

Our environment was set up per the alembic tutorial for creating an environment. I ran:

alembic init alembic

I also put an alembic.ini-dist file into our project’s config/ directory, and modified alembic/env.py to include our model.

To get started working with an existing install, you’ll need to modify alembic.ini-dist, and copy it to config/alembic.ini to fit your environment – setting the connection string and the path to the alembic directory are the two most important settings. We have a script which creates databases from our models.py called setupdb_app.py. This script takes --database_name as a command-line argument. My default for our project is to use breakpad.

We use a virtualenv called socorro-virtualenv. The virtualenv is created automatically if you run make test. If you’re creating a standalone virtualenv, you can do that with virtualenv socorro-virtualenv. Activate this with . socorro-virtualenv/bin/activate.

Creating a revision

  1. Create a fresh database to work from. For Socorro, the command is: PYTHONPATH=. socorro/external/postgresql/setupdb_app.py --database_name=breakpad
  2. Edit models.py with the change to the schema
  3. Run: PYTHONPATH=. alembic -c config/alembic.ini revision -m 'your message about the migration'. The output will include the name of the new file.
  4. Edit the new file as needed alembic/versions/*.py
  5. Run: PYTHONPATH=. alembic -c config/alembic.ini upgrade +1
  6. Test your downgrade with PYTHONPATH=. alembic -c config/alembic.ini downgrade -1

If all goes well, your revision is ready! If something goes wrong, edit and try again. The revision will automatically rollback if there are any errors.

Downgrades are a little tricky to properly execute. In an ideal world, you’d be able to revert the underlying code, but preserve only the commit containing the migration. More on this in a future blog post!

Creating a revision using --autogenerate

This is very similar to the above, with the addition of --autogenerate to your revision command. This should do the right thing, but definitely check your generated file for accuracy.

  1. Create a fresh database to work from. For Socorro, the command is: PYTHONPATH=. socorro/external/postgresql/setupdb_app.py --database_name=breakpad
  2. Edit models.py with the change to the schema
  3. Run: PYTHONPATH=. alembic -c config/alembic.ini revision --autogenerate -m 'your message about the migration'. The output will include the name of the new file.
  4. Edit the new file as needed alembic/versions/*.py
  5. Run: PYTHONPATH=. alembic -c config/alembic.ini upgrade +1
  6. Test your downgrade with PYTHONPATH=. alembic -c config/alembic.ini downgrade -1

If all goes well, your revision is ready! If something goes wrong, edit and try again. The revision will automatically rollback if there are any errors.

Production deployment

You’ll need to deploy an alembic.ini on your production database system and probably a virtualenv to support your python modules.

We deploy our virtualenvs with our application, so this step was pretty simple for everything except for alembic itself. The virtualenv put in full, static paths for the python binaries and had some dependencies that I haven’t figured out yet for actually running alembic. To get around this, I created a virualenv locally on the system for the postgres user. Having your postgres user run the migrations locally is a must for me because I need to access the filesystem to pull in new versions of user defined functions stashed in the directory my model lives in.

I just deploy a new release of our application code on the database server locally, and then I run alembic against the versions directory that’s deployed.

FAQ

And here’s an FAQ for the common problems folks ran into:

OOPS I forgot to create a database before I created a revision!

To “fix” this, try:

  1. Create the database from scratch using your current models.py.
  2. Run: PYTHONPATH=. alembic -c config/alembic.ini downgrade -1
  3. Run: PYTHONPATH=. alembic -c config/alembic.ini upgrade +1

Assuming your downgrade function works, this should allow you reverse the latest revision and then test your migration.

Error message: “Only a single head supported so far.”

See Working with Branches.

I’m using schemas, and alembic doesn’t recognize them when I try to use --autogenerate.

See include_symbol. And be sure to add this to both the “offline” and “online” versions of the revision code in env.py.

Error message: Target database is not up to date.

This means you’ve got a file in your versions directory that contains one or more migrations that haven’t been applied to the current database. You can either apply them with alembic upgrade head or have a look in that directory and remove the migration(s) that you don’t want.

postgresql ,

Why give credit to reviewers?

This is a lightly edited version of an email I sent to pgsql-hackers today.

Josh Berkus asked:

> How should reviewers get credited in the release notes?

Without getting into how the community might decide to do this, I thought it might be helpful to share the reasons why I believe recognizing and expressing gratitude to reviewers is a helpful, useful and gratifying exercise for the Postgres community.

I support crediting reviewers in a more formal way than we currently do for a few different reasons.

First, I believe it’s worth finding a way to say “Hey, you just did something great for Postgres”, publicly, to a bunch of people who could have spent their valuable time and energy in some other way.

Second, reviewers get better at their work by reviewing multiple times – so I’d like to encourage people to review more than once.

Third, reviewers don’t always need to be expert developers, or experts at Postgres to get started, but many people who do open source work have no idea this is true. Public recognition helps make it clear that we have people who give useful reviews and are relative novices.

We also have several different kinds of reviews:

  • “does it compile”
  • style/typo/easily seen bug passes
  • in-depth discussion of design choices, use case, interface
  • complex testing cases
  • performance testing
  • pre-commit checks for more subtle bugs or committer preferences.

All of those, except probably the very last, can be done by people who are familiar with Postgres or its configuration, but aren’t necessarily Postgres or C experts.

Fourth, we have very few accepted ways to recognize contributions to Postgres. Naming in Release Notes is one way this community has consistently supported as a public way to say “hey, you just did something great for Postgres”. The complete list of ways I’m aware of are:

  1. Recognizing major, minor and emeritus contributors
  2. Making someone a committer
  3. Being part of the -core group
  4. Naming authors by name in commit messages (but without consistent metadata, making it difficult to count well)
  5. Naming authors in release notes

That’s pretty much it. That’s great for the people who have already secured positions through seniority, or because they’re amazing C hackers. I don’t know if I need to lay out for everyone the value of public recognition – if you want me to I can enumerate them. But the benefits of public recognition are huge — both in a social and a financial sense.

Currently, the only way I know of to be recognized for work on Postgres that is not seniority or code-related is #1. If you’re a reviewer, there’s almost no chance you’ll be recognized in our list of contributors without some additional, very significant contribution to our community. (Please let me know if I’m mistaken about this — I only know what I know!) Adding names to Release Notes (or some variant of Release Notes) seems like a minor concession for work that we as a community need, value and want to encourage.

We are so few in terms of patch contributors – somewhere between 300-400 people contribute code to PostgreSQL each year based on the names I try to pull out of commits. I haven’t counted how many reviewers we have who do not also contribute patches separately.

Giving people appreciation for the review work they’re doing, for free, is a good thing for everyone. Naming more names helps describe the true scope of our community. Spreading gratitude is good for those who thank and those who receive thanks (like, proven scientifically!). And we increase the number of people who benefit directly from the work that they do here, by giving them something they can point their boss, their company and their colleagues to.

So, when we’re debating how recognition might be done, please don’t lose sight of why this is important in the first place.

postgres, postgresql , , , , 5 Comments

Inheritance and sharding with Postgres

A friend told me about their sharding scheme last night, and it made me very curious about how others are handling this problem. This question about database design turns into a devops issue, so it’s something really the entire development group and devops and DBAs need to be aware of and concerned about. And it’s not a problem exclusive to Postgres.
Continue reading →

postgres, postgresql , , , , , ,

Security and maintenance release for PostgreSQL: versions 9.1.3, 9.0.7, 8.4.11 and 8.3.18

Today, PostgreSQL Global Development Group released new versions of all active branches. This includes three security bugfixes, two of which are pretty obscure and one that fixes a possible security issue with restoring un-sanitized output from pg_dump. Details about the security issues are included in the release announcement.
Continue reading →

conference, postgres, postgresql, speaking , , , , ,

Where to find me at #LCA2012

I’m going to be pretty busy while in Melbourne and Ballarat for the next 10 days.

Here’s my itinerary:

There’s a rumor that Stewart Smith and I might do a Q&A about databases in the cloud. If it happens, it will involve lots of pessimism and swearing.

Drop me an note if you want to meet up! I’ll be in Ballarat until early Friday morning.

Then I fly back to LA to give a keynote at SCaLE that Sunday (blog post about that coming).

postgres, postgresql , , , , 1 Comment

Day 3 at PgConf.EU: the future, replication, performance and the closing keynote

I was room host for Simon Riggs, Magnus Hagander and Greg Smith today before giving my final talk this afternoon.

The morning started with Simon Riggs talking about his wishlist for the future of Postgres – including some boundary-stretching ideas for bi-directional replication (a way to possibly support multi-master architecture for Postgres). Simon named his talk “Postgres Futures”, but also called it his personal “shopping list” of features he’d like to see implemented, or implement himself. Magnus deep-dove into the replication protocol and how to use pg_basebackup with 9.1. Greg’s talk on benchmarking is always fantastic, and I learn something new every time. He included some graphs for FusionIO testing he’d done in the last couple weeks.

I also gave my last talk of the conference, “Managing Terabytes” about my experiences managing 8.x version clusters of a terabyte or larger in size for several companies. I reorganized this talk from the last time I’d given it, and I think it came across quite a bit more clearly to the audience. One developer gave me the suggestion that I should have tried to do a series of updates to a catalog tables to try to recover page space. I’m designing a little test case to help someone do this in the future if they run into this problem with older versions of Postgres. HOT (8.4 and later) essentially fixes this issue, by the way.

The keynote was shared by Ed Boyajian and Bruce Momjian. Ed mentioned that Oracle had the best earnings statement ever in the most recent shareholders call. In spite of that, there’s a rising tide of Oracle users who are looking for alternatives, given how strongly they’re locked into their technology. He said that he was recommending companies use Postgres is a strategic lever to negotiate with Oracle. And as IT departments strapped for cash are trying to figure out how to fund new data initiatives – they’re turning to products that are free.

Bruce then quoted the opening keynote by Ram Mohan – “With open source, support is a whole new level.” And Bruce’s comment was that what Ram did when he started 10 years ago with Afilias was heretical for conventional IT wisdom at the time.

Bruce also said that he’d always thought Postgres would ultimately only ever be a niche player among databases. But with all the progress we’ve made as a project, and the new markets being explored, he sees much greater possibilities for the project.

He asked the audience about the speed at which bugs had been fixed – within 24 hours, a few days or a single week. Only one hand was raised for a bug requiring more than 1 week to be fixed, among probably 40-50 hands raised for much faster fixes.

Bruce also noted that developers are often moved to work and stay with Postgres as a project, because they have decided that “this is an important thing for me to do in my life.”

PgConf EU was a great conference, and I’d be happy to be invited back, wherever they decide to hold it in 2012.

postgres, postgresql , , , , , , ,

Update releases for 9.1.1, 9.0.5, 8.4.9, 8.3.16 and 8.2.22

Today the Global PostgreSQL Development Group released branch updates for all supported versions. You can go ahead and download them now!

There were quite a few fixes for somewhat obscure crashes, fixes for memory leaks discovered by some valgrind testing, and a couple big fixes for GiST indexes, like this:

* Fix memory leak at end of a GiST index scan

gistendscan() forgot to free so->giststate.

This oversight led to a massive memory leak — upwards of 10KB per tuple
— during creation-time verification of an exclusion constraint based on a
GIST index. In most other scenarios it’d just be a leak of 10KB that would
be recovered at end of query, so not too significant; though perhaps the
leak would be noticeable in a situation where a GIST index was being used
in a nestloop inner indexscan. In any case, it’s a real leak of long
standing, so patch all supported branches. Per report from Harald Fuchs.

There were a few fixes for catalog or catalog index corruption, and avoidance of buffer overflows which could cause a backend crash. There were also a few fixes that will improve the performance of VACUUM over time.

Release notes have all the details. Many of the fixes have already been committed to 9.1 (there are only 11 new commits in 9.1.1). So, you’re about to experience a great many bugfixes, users of 8.2->9.0.

Another thing to note – 8.2 will be deprecated in 2011! You ought to upgrade anyway, just to get HOT and to get yourself into a position to use pg_upgrade for future upgrades. But now, you’ve got extra incentive.

postgres, postgresql , , , , 8 Comments

My Postgres Performance Checklist

I am asked fairly frequently to give a health assessment of Postgres databases. Below is the process I’ve used and continue to refine.

The list isn’t exhaustive, but it covers the main issues a DBA needs to address.

  1. Run boxinfo.pl on a system
    Fetch the script from http://bucardo.org/wiki/Boxinfo. Run as the postgres user on the system (or a user that has access to the postgres config).
  2. Check network.
    What is the network configuration of the system? What is the network topology between database and application servers? Any errors?
  3. Check hardware.
    How many disks? What is the RAID level? What is the SLA for disk replacement? How many spares? What is the SLA for providing data to the application? Can we meet that with the hardware we have?
  4. Check operating system.
    IO scheduler set to ‘noop’ or ‘deadline’, swappiness set to 0 (http://www.pythian.com/news/1913/what-exactly-is-swappiness/)
  5. Check filesystems.
    Which filesystem is being used? What parameters are used with the filesystem? Typical things: noatime, ‘tune2fs -m 0 /dev/sdXY‘ (get rid of root reserved space on database partition), readahead – set to at least 1MB, 8MB might be better.
  6. Check partitions.
    What are the partition sizes? Are the /, pg_xlog and pgdata directories separated? Are they of sufficient size for production, SLAs, error management, backups?
  7. Check Postgres.
    What is the read/write mix of the application? What is our available memory? What is the anticipated transpactions per second? Where are stats being written (tmpfs)?
  8. Check connection pooler.
    Which connection pooler is being used? Which system is it running on? Where will clients connect from? Which connection style (single statement, single transaction, multi-transaction)?
  9. Backups, disaster recovery, HA
    Big issues. Must be tailored to each situation.

What’s your checklist for analyzing a system?

postgres, postgresql , , , ,

Seeking: Database Disaster Stories

I’m going to give another “Mistakes Were Made” talk at PgConf.EU next month.

I have many disaster stories of my own, but am always looking for more! Stories of data-destruction and tales of unexpected failure are welcome.

You can leave them in the comments, or email me.

The talk focuses on the ways in which systems fail, and the typical kinds of failure we find in web operations. Types of failure I focus on are:

* Failure to Document
* Failure to Test
* Failure to Verify
* Failure to Imagine
* Failure to Implement

Stories that fall outside those categories are especially welcome.

I look forward to your tales of woe!

postgres, postgresql, presentation, speaking, user groups , , , ,

9.1 presentation at Windy City Perl Mongers

I recently updated my PostgreSQL 9.1 slides for a presentation at the Windy City Perl Mongers.

We discussed 10 features that the Postgres community decided to emphasize in our press releases. The crowd was primarily people who had never used Postgres before, which was a bit of a different audience for me.

It was great to be able to compare notes with folks who are supporting Oracle and SQL Server, and see a lot of excitement for trying out 9.1.

When I’m traveling around, I’ll be looking for more non-Postgres user groups to give talks like this. Let me know if you’d like me to come speak at yours!