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

One of the main new features in Postgres 12 I was excited for was generated columns. I mentioned this feature breifly in my intial reivew of Postgres 12 where I gave one example of how I have already used generated columns. The most likely and common use cases for generated columns work as expected and improve performance for SELECT queries.

This post dives further into the feature including a peek at performance as usage goes beyond "convenience" columns (concatenate strings, basic formulas, etc.) to "heavy compute" columns.

Continue Reading

The PgDD project is now an extension for Postgres! This post is about the PgDD (PostgreSQL Data Dictionary) project, why it exists, and how to install the extension. I have found the ability to query the most important database structures a huge benefit, hopefully others will find this project useful too.

This post is outdated, PgDD has been rewritten using the pgx framework. Read PgDD extension moves to Pgx and Postgres Data Dictionary for everyone for more details!

Why PgDD is helpful

PgDD makes it possible to easily explore your database structure with standard SQL syntax. You do not need to use a specific tool or learn a set of new commands, simply install the extension and start querying. To find the 5 largest tables by size on disk, simply query the dd.tables view.

Continue Reading

Postgres 12 and PostGIS 3 have been out and production ready for about a month now. I have been testing the upgrade from Pg 11 to 12 and have evaluated various workloads, mainly looking for red flags that would hold up an upgrade. At this point I am happy with what I see and am planning the upgrade for all our Postgres databases! This post covers a few points I found interesting during my initial testing. See the Postgres 12 release notes and PostGIS 3 release notes for more details about what has changed in these versions.

Reasons to upgrade

The main feature making me want to upgrade to Postgres 12 is stored generated columns. This allows columns to store a calculated value to disk, much like materialized views store the results of queries.

Continue Reading

I love working with OpenStreetMap (OSM) data in PostGIS. In fact, that combo is the main reason I made the leap to Postgres a few years ago! One downside? Getting data through osm2pgsql takes a bit of patience and can require quite a bit of Oomph from the system doing the load. Earlier this year I wrote about this process for smaller regions using a local virtual machine and the Raspberry Pi 3B. While small area extracts, such as Colorado, are great for some projects, other projects require much larger data sets. Larger data sets can benefit from (or even require!) more powerful hardware.

As OSM's PBF file approaches and exceeds 1GB the process starts requiring some serious RAM in order to complete the process. Testing this process in depth got me curious about cost effectiveness and time involved to load OpenStreetMap data as the source data size increases. I know that I can spool up a larger instance and the process will run faster, but does it end up being cheaper? Or just faster? This post is an attempt to document what I have found.

Continue Reading