Posts Tagged ‘postgresql’

Tonight I presented a talk on using JSON in Postgres at the Toronto Postgres users group. Pivotal hosted the talk at their lovely downtown Toronto office. Turnout was good with a little over 15 people attending (not including the construction workers banging against some nearby windows).

I talked about the JSON and JSONB datatypes in Postgres and some idea for appropriate uses of NoSQL features in a SQL database like Postgres.

My slides are available for download

We are thinking of having lighting and ignite talks for the next meetup. If anyone is in the Toronto area and wants to give a short (5 minute) talk on a Postgres related topic let me know.

I was recently working on a project where we had about half a dozen developers working on an established code base. All of the developers were new to the code base and I knew that we were going to be making a fair number of database schema and data-seeding changes in a short period of time. Each developer had their own development environment with a dedicated database (PostgreSQL). The developers on the project had their hands full learning about the code base and I didn’t want to distract them by having to take a lot of their time managing their development database instances.

I decided to try using Alembic to manage the database schema migrations.

PostgreSQL is becoming a more popular choice for an embedded database because of its BSD license, relatively low memory footprint and great list of features. A few people have asked me if Slony would be a good choice for replication in an embedded environment. Embedded deployments haven’t been a primary use-case for Slony and some of the challenges you would face are worth writing about.


Last week I was in Chicago giving a talk at PostgresOpen on managing PostgreSQL with puppet. The talk was well attended and appears to have been well received.

Puppet is configuration management software that allows you to describe how your servers should look using a declarative syntax. You describe what packages you want to install (obviously postgres) and how your configuration files should look. Puppet also allows you to run commands to create databases or database objects such as users.

In my talk I discuss why it is important to use a repeatable procedure for building production database servers and how this is a tool in bridging the divide between developers and operations staff.

I talk about how deploying servers with automation allows your servers to be similar. Similar might not mean identical but the differences between your database servers is controlled and managed. This also applies to your development and QA servers. If you deploy your staging, QA, and development servers using the same puppet manifest as your production servers but with possibly different configuration options then you will be more confident in your testing.

You can view my slides. They recorded the talk and I will update this post with a link to the talk when it is posted.

Updated: You can view a recording of the video below

I spent the weekend attending Pycon Canada where I gave a talk on Pl/Python. I want to thank the conference organizers for putting on an excellent conference. I am told that this was the first time Pycon had a regional conference in Canada and that it was put together by a group of volunteers in less than 6 months.

One of my favourite parts of local/regional conferences held on weekends is that they tend to attract attendees who are passionate about computers and technology. The people who I spoke with at the conference were there because they wanted to be there,not because there boss wanted them to be there, and either loved Python or wanted to learn more about it. I’ve attended many great PostgreSQL conferences over the past few years but it was nice to spend sometime talking with people from broader development backgrounds.

In my discussions with people at the conference I noticed a trend. People I spoke with who are working at companies that did Python development tended to be using PostgreSQL. The ones that weren’t currently using PostgreSQL were using MySQL and talking about moving to PostgreSQL or were apologetic for still being on MySQL. The MySQL users were often apologizing before I told them that I was a PostgreSQL contributor. Some of the MySQL users also mentioned that they were using non-Oracle forks like Percona.

This was in contrast to the people at the Python conference that described their workplaces as doing primarily Java development. The Java development shops tended to be using Oracle or SQL Server. I admit that the sample size of of the Java developers wasn’t that big (this was a Python conference after all) but my observations are worth keeping in mind since they might be indicating a pattern. Other people have commented about the popularity of PostgreSQL in the Ruby community.

I wonder how much of this observations is because older written in Java are already using SQL Server/Oracle and there hasn’t been a strong enough driver to change to PostgreSQL. While newer software projects are tending to choose Python or Ruby over Java and at the same time picking a FLOSS database such as PostgreSQL where they don’t have to worry about migrating a legacy application.

My talk on writing stored functions in Pl/Python was well received. A lot of people saw appeal in being able to write their stored functions in Python instead of pl/SQL but that shouldn’t be a surprise considering this was a Python conference.

My slides are available here, the video of the talk is posted at pyvideo

Slides from my 2012 PGCon presentation Making your Own Maps are now available

The presentation covered

  • Common reasons people render their own maps
  • Where to get OpenStreetMap data and how to load it into your PostGIS database
  • How to use Tilemill to design your own map style
  • How to render map tiles, both statically and dynamically
  • How to use OpenLayers to display your map

The presentation was recorded. I will update this post when the recording comes online.

I’ve been a regular attendee of PGCON since the first year it was held in Ottawa. Like past years I enjoyed the conference and I would like to thank Dan Langille for putting together another first-rate conference. My favourite part of PostgreSQL conferences is meeting and reconnecting with users people in the community.

Updated: The video is available here

The Toronto OSM hack weekend (2012) started on friday with an introduction to OpenStreetMap presentation at the Ryerson Geography department. Experienced OpenStreetMap presenter, Richard Weait gave a talk to a room of Geography students, staff and faculty. After Richard explained the basics of OpenStreetMap A.J. and Tristen gave a short presentation on some of the work that they have been doing for MapBox.

After the presentations we did a QA session with the audience. The OSM ‘experts‘ in the room tried to answer the questions from the audience. Most people in the audience were geographers and GIS people who had already heard of OSM. A number of the attendees already had worked with various components in the OSM stack. We had questions on projections, layers, TileMill, Mapnick, tagging and an assortment of other topics.

After the Q/A I went with A.J and Tristen to Chiplote for some Burritos. At Chipotle we learned three things. 1) Chipotle in Washington does more of the meat preperation in the back room than they do in Toronto; Toronto should take a hint. 2) Keeping the Burrito in the tinfoil wrap it in keeps it from falling apart as you eat it. 3) Next time I need to remember to bring Buritos back to my fellow hackers. Friday night we had a social at the hack weekend social HQ, there was plenty of beer and the room was packed.

Saturday morning I took the train back downtown for the first day of hacking. We had local OSM developers and out of town hackers from three different countries participate along with a group of Ryerson Geography students. I spent some time Saturday morning helping someone get the rails port working on their OSX laptop. The part they seemed to have the most difficulty with was getting PostgresSQL running. This tells me two things. First that the work people in the OSM community have done to make the rails port easier to install has paid off and some PostgreSQL binary distributions could make their user experience a bit better.

I had suggested the EDB one-click installer for OSX but they opt’d for the KyngChao binaries. They were okay installing the package but they weren’t clear how to startup PostgreSQL and connect to it. I feel that the EDB installer makes it easier for an inexperienced PostgreSQL user to get up and running.

In addition to helping people with installing PostgreSQL I also helped some people with SQL queries and helped with changes to the website look cleaner for users logged in but haven’t uploaded any GPX traces.

Saturday night we had another party at social HQ. Party highlights included beer with ingredients from seven continents, comparision of political systems, talk of election rigging. Thai food, wine and beer were also spotted. Most of the geography students from the hack sessions were able to make it out and saw how fun OSM parties can be. I hope to see them at the regular Toronto OSM pub nights.

The GO train back to Oakville was pretty full. This was St. Patricks day and the streets of Toronto were filled with drunk people in green. The 11:43pm train to the suburbs is a bit too early for serious leprecons. Many of the riders had been at a Van Halen concert, it didn’t seem like many of them were interested in making maps.

There is a saying in the OpenStreetMap world that if you want to build an local OpenStreetMap community you should import a bunch of Germans. If you can’t import some Germans the next best thing is to import Richard Weait. He almost single-handedly organized the Toronto hack weekend, secured a venue and provided food, drinks and social entertainment. I also want to thank the Ryerson Geography department and Claus Rinner for letting us use their facilities and providing eager GIS students.

February 6’th 2012 marks the 10 year anniversary of the open source release of the DBMirror replication system. DBMirror was not the first PostgreSQL replication solution to be released but it was the first one I was involved with.

In the summer of 2001 I was working for Navtech System Support, an aviation software company. We were using PostgreSQL 6.x to store data for one of our applications. We needed to have an up to date copy of the database available on servers at a remote site. We also needed a standby database server in case our primary server failed.

My talk on PostGIS replication at FOSS4G 2011 went well. It looked like there were about 150 people in the room. Most of them had not yet deployed a PostGIS replication solution.

My talk covered Slony and streaming replication. It gave an overview of different replication patterns that can crop on in the GIS space. I then gave an overview of the key features and limitation of Slony and streaming replication.

A video of the talk is available at FOSSLC

My slides are available here

This past week I attended FOSS4G in Denver, a conference run by the open-source geospatial foundation (OSGEO) that also happened to be the largest PostgreSQL conference in North America.

FOSS4G is a big tent conference that attracted about 900 attendees from all over the world with over 400 of them from the United States, over 50 from Canada.
A big tent conference is a conference is similar to a big-tent political party. The idea is appeal to a broad base of people by catering to as many groups as possible while keeping true to some key common themes. Out of the 900 people at the conference I’d say 75% raised their hands when asked if it was their first FOSS4G. Last years FOSS4G was held in Europe and also had over 800 attendees.