RustProof Labs: blogging for education (logo)

What version of PostgreSQL?

By Ryan Lambert -- Published March 12, 2019

Are you trying to decide which version of PostgreSQL to install? Maybe you need to decide if it's worth upgrading to the latest version. This post is here to help.

I started thinking about this after reading Brent Ozar's post on this topic from a MS SQL Server perspective. One thing I realized is that PostgreSQL major versions are maintained for roughly 5 years compared to MS SQL's 10 year support. The more I have pondered this difference, the more I agree with only keeping versions in support for 5 years.

New Installations

If you are starting from scratch with Postgres, start with v11. There aren't going to be many good reasons to start a new project on anything but the latest version.

Continue Reading

Low Power Computing with Raspberry Pi

By Ryan Lambert -- Published February 25, 2019

It's no secret that I love the Raspberry Pi. Earlier this year I wrote about using osm2pgsql on a Raspberry Pi to load OpenStreetMap data to PostgreSQL. Less than a year ago I released the PiWS, an open source weather station based around the Raspberry Pi. In fact, my first test PiWS was setup almost exactly one year ago on a single-core, 512 MB RAM Raspberry Pi Zero W and it is still running today.

Anyway, the point of this post is to explain why I think the Pi is so awesome, why I continue to chose it over other competing hardware available today, and why I think the challenge of developing for low-power devices is so valuable.

View this post on Instagram

The #raspberrypi looks good when rack mounted! They also do quite a bit of work too! #piws #postgresql #openstreetmap #trackyourgarden

A post shared by Track Your Garden (@trackyourgarden) on

Continue Reading

PgOSM transformations explained

By Ryan Lambert -- Published February 21, 2019

I wrote about the PgOSM project last month (GitHub) showing how to transform OpenStreetMap data into a more relational format. The goal of this post is to show how you can easily customize the transformations and reductions to suit your needs. The prior post used a command to load a layer_definitions.sql file (view on GitHub) under the section Deploy schema and define layers.

psql -d pgosm -f ~/git/pgosm/db/data/layer_definitions.sql

Customizing the data loaded in that step is the key to changing how PgOSM transforms, reduces and organizes your OpenStreetMap data. This post shows you how to do that!

History of PgOSM

I came up with the core of this project in 2015 over the course of a weekend or two. Since then it has continued to serve my needs reliably, mostly without updates, ready to load fresh OpenStreetMap data any time I asked. Recently I decided to clean up the project and release it under the MIT license. I've always felt like it's an ugly solution, it's far from perfect... but being reliable and customizable counts for a lot!

Continue Reading

osm2pgsql on a Raspberry Pi

By Ryan Lambert -- Published January 29, 2019

I love the Raspberry Pi! It's an affordable little box of "I can do it" Linux-goodness. Though, some tasks are not quite as easy to get right on the Pi, and getting osm2pgsql to run reliably on a Raspberry Pi 3B has been a challenge. This post examines one limitation of the Raspberry Pi system, how this can make osm2pgsql go horribly wrong, and how to configure the system to make it work.

If you are new to osm2pgsql, read my initial post first, I do not repeat the overall process here.

Raspberry Pi = Slow I/O

The main storage of the Raspberry Pi is a micro SD card. While this is great from a cost and availability perspective, the performance of the best SD cards is sub-par for most relational database work. I mention this first, because even when working with more powerful hardware, including faster disks, the main limitation is disk I/O. This is even more of an issue on the Pi. In my previous post I warned:

Postgres and osm2pgsql both use a lot of disk I/O during this process!

Continue Reading

PgOSM: Transform OpenStreetMap data in PostGIS

By Ryan Lambert -- Published January 25, 2019

My previous post, Load OpenStreetMap data to PostGIS, covered how to use osm2pgsql to load a sub-region export of OpenStreetMap data into PostGIS (PostgreSQL). With the data loaded to Postgres, one quickly finds out that it isn't very easy to jump in and use right away. To help solve this problem, the PgOSM project was created. The main purpose of PgOSM is to restructure the OpenStreetMap data into a more friendly format for relational databases.

This post starts by showing how the PgOSM project is used to transform our OpenStreetMap data and ends with showing why we do it this way.

Continue Reading