RustProof Labs: blogging for education (logo)

Webinar: Intro to PostGIS and OpenStreetMap (1/6)

By Ryan Lambert -- Published January 09, 2020

Below is the recordring of my Getting Started with PostGIS and OpenStreetMap webinar from December 11, 2019. This is the first in a series of six covering how I work with PostGIS and OpenStreetMap.

I had a great time presenting this session, I hope having this recording available is helpful. Below the video is a list the links showing throughout the slides.


Continue Reading

Updated for 2020: Load OpenStreetMap data to PostGIS

By Ryan Lambert -- Published January 04, 2020

I originally wrote about how to load OpenStreetMap data into PostGIS just under one year ago. Between then and now, 364 days, a number of major changes have occurred in the form of new versions of all the software involved. Due to the combination of changes I decided to write an updated version of the post. After all, I was no longer able to copy/paste my own post as part of my procedures!

The goal of this post is to cover the typical machine and configuration I use to load OpenStreetMap data into PostGIS with the latest versions. The first part of the post covers the how of setting up and loading OpenStreetMap data to PostGIS. The second portion explains a bit of the reasoning of why I do things this way. The latest versions of the software at this time are:

  • PostgreSQL 12
  • PostGIS 3
  • osm2pgsql 1.2

This post is part of the series PostgreSQL: From Idea to Database.

Changes to software

PostgreSQL 12, PostGIS 3, and osm2pgsql 1.2 are all new releases since the original post. The original used Postgres 11, PostGIS 2.5 and osm2pgsql 0.94. If you just look at the version numbers they may look like small increases, but the whole package is chock-full of improvements!

If you can upgrade to the latest and greatest, I recommend you do so!

Continue Reading

PostgreSQL at RustProof Labs: 2019 in Review

By Ryan Lambert -- Published December 28, 2019

As I look back on 2019, I can say with great joy: I have spent a lot of time working with PostgreSQL and PostGIS this year! Last year I decided to write an end-of-year post, this is the 2019 version.

This post is a quick snapshot of my Postgres/PostGIS activities in 2019 with a quick look into 2020.

PostgreSQL popularity

Starting with popularity again, PostgreSQL has continued it's trend in popularity growth through 2019, a long running trend according to DB-Engine's ranking. Postgres is still 4th on the list, ahead of MongoDB and behind Oracle, MySQL and MS SQL. Through 2019 Postgres and MongoDB continued their growth in popularity, while all of the top 3 databases have gained back popularity they lost in 2018. The gap between Postgres and the top 3 on the list continues to shrink.

Chart from showing trend of top-5 most popular databases.  PostgreSQL (in 4th place) continues to grow at a steady rate through 2019 while the top three (Oracle, MySQL, MS SQL) worked to gain back losses in popularity they saw in 2018.

Yay, PostgreSQL!!

Continue Reading

Exploring PgConfig comparison tool

By Ryan Lambert -- Published December 27, 2019

PgConfig is a RustProof Labs hosted tool that makes it easy to compare configuration changes (postgresql.conf) between major PostgreSQL versions. The initial version of this tool was the result of a lazy Sunday at home, and since then has seen multiple enhancements. This post goes over how I have been using the tool over the past few months.

postgresql.conf comparison (pgconfig) logo)

The current version of PgConfig has three (3) main functions:

  • Differences between version X and Y
  • Single parameter history
  • Compare your configuration to version defaults

Continue Reading

Upgrading to Postgres 12 and PostGIS 3

By Ryan Lambert -- Published December 15, 2019

My initial testing to upgrade our PostGIS servers to Postgres 12 and PostGIS 3 went well. After that testing I noticed an extra snippet in the output of SELECT PostGIS_Full_Version(); in all of our databases, post-upgrade:

procs need upgrade for use with PostgreSQL "120"

The only way I have found to resolve this message is to perform a hard upgrade of PostGIS databases after the main upgrade is complete. If you have managed PostGIS databases for any amount of time you have likely experienced this before.

"By HARD UPGRADE we mean full dump/reload of postgis-enabled databases. You need a HARD UPGRADE when PostGIS objects' internal storage changes or when SOFT UPGRADE is not possible."

At first I thought this was only affecting a lone, older database that maybe I didn't get cleanly upgraded last time. Then I noticed it affecting a PostGIS-enabled database I had created in the past couple of months on Postgres 11 and PostGIS 2.5.

This post outlines how to upgrade to Postgres 12 and PostGIS 3 from the most recent prior versions.

Continue Reading