What version of PostgreSQL?
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.
Low Power Computing with Raspberry Pi
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.
Since publishing this post, I have also tested PostgreSQL performance on the Pi. Parts One and Two.
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.
PgOSM transformations explained
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!
osm2pgsql on a Raspberry Pi
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
Update: Most of this complaint is resolved by using application class (A1 or A2) SD cards. The rest is resolved with the introduction of the Raspberry Pi 4.
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!
PgOSM: Transform OpenStreetMap data in PostGIS
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.