Installing PostgreSQL and PostGIS from source
This post walks through the process of building and installing PostgreSQL
and PostGIS from source on a Raspberry Pi. Building from source
might not be for everyone, but having this skill opens the doors to a number of
new opportunities. With a little experience with Linux and the command line this
task can quickly become second nature.
Yes, it is more involved than running apt install and it will take longer.
What you gain is the ability to test new features, bug fixes, review
Commitfest patches, and ultimately have
more understanding about what is installed and how.
You'll probably also learn a thing (or 10) in the process... I know I did!
This post is part of the series PostgreSQL: From Idea to Database.
Nuances of the Pi
My main driver to install from source on the Pi is the lack of availablity of
version choices with software via apt.
Raspbian OS,
being a customized ARM-specific derivitive of Debian has its challenges,
and Rasbian Jesse easily can install the 9.6 line, typically
lagging 1-3 minor releases from current.
I've never had success getting the PGDG repos to work on the Pi either,
and while 9.6 isn't that old (< 3 years) there are a ton of
improvements and features in the major releases since.
PostgreSQL deployments using Ansible and Sqitch
The database is a critical component of pretty much every application in existence today. Being such an important component it is good to be able to consistently deploy your databases in a efficient, reliable and accurate manner. At RustProof Labs, Postgres is our database of choice so in this post I examine how we use Ansible and Sqitch together at a high level to deploy, maintain, and upgrade our Postgres servers and databases.
This post is part of the series PostgreSQL: From Idea to Database.
Define goals
Before diving into how Ansible and Sqitch work together with Postgres
lets define some goals. At the server/service level, we need the ability
to quickly provision new Postgres instances ready for production or development.
It should be easy to set/change many common configuration options
(postgresql.conf, pg_hba.conf, etc.) to allow efficient operation
on a variety of hardware and handle widely different workloads.
Geometry viewer added to DBeaver
PostGIS users have a new option for viewing spatial data! DBeaver has joined the spatial database club with its geometry viewer, available since version 6.0.3. I previously wrote about PgAdmin4's geometry viewer, now we have two general purpose database GUI options supporting PostGIS spatial data. This post gives an overview of DBeaver's implementation and my initial thoughts.
This post was written using DBeaver v6.1.0, PostgreSQL 11.3, and PostGIS 2.5.
What it looks like
DBeaver's geometry viewer allows you to easily select and display a single row, multiple rows, or all rows with relative ease. This is handled by you selecting one or more rows of spatial data. Clicking the spatial column's header displays all returned rows.
Contributing to PostgreSQL
This post outlines my experience with becoming more involved with the PostgreSQL development process. Having even a tiny part in reporting and fixing a bug was exciting and rewarding. Turns out, though, there's quite a steep learning curve to get involved. The Postgres project isn't small and things move fast, making it easy to get lost. I have learned a lot and am looking forward to contributing more to the PostgreSQL project and community.
Getting involved
I got involved with the development of PostgreSQL
after years of frustration with a particular headache related to
QGIS layer styles and pg_dump
(fixed!).
Granted,
the workaround
wasn't that big of a deal but did add
unnecessary complexity and a point of confusion.
I dug into the source code to find why that workaround was required,
and that led me to find a case where the workaround no longer worked.
Now it wasn't just a hassle, it
was a bug 🐛.
Fixed: XML data and pg_dump
A few months ago I
submitted a bug
to the PostgreSQL
hackers mailing list...
It is already fixed thanks to Chapman Flack and others!
While the official bug was recently submitted, users in the PostGIS
and QGIS communities have been long-plagued by this issue. If
adding the following line of code to your pg_dump file is familiar,
an upgrade to PostgreSQL is in your near future!
SET XML OPTION DOCUMENT;