RustProof Labs: blogging for education (logo)

PostgreSQL on Raspberry Pi 4: Initial Testing

By Ryan Lambert -- Published August 23, 2019

The Raspberry Pi 4B is out, and it's pretty much awesome. With how much I love the Raspberry Pi, it's no surprise that I pre-ordered one of the new Raspberry Pi 4B models with 4GB RAM the same day they were announced. I had to wait about a month for it to arrive, and I have had my Pi 4 for about a month now. I had some free time to put the new Pi 4 hardware to the test with PostgreSQL and pgbench. The goal of this post is to set an initial Postgres benchmark for the Raspberry Pi 4 and PostgreSQL 11, compared to older Raspberry Pi hardware.

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

Revisit prior testing

Before we get into the new results, let's recap the results from prior testing with the Pi and Postgres. My first serious test (April 2019) used four of the Pi 3B models and tested Postgres 9.6 through Postgres 12 (devel). Those TCP-B (like) tests showed that 200 TPS was a reliable goal for that hardware. At that time I was pretty happy with the results, until a few months later I tested the Pi 3B again using the newer A1 rated SD cards. That change boosted TPS performance by 140%, upping the target to nearly 500 TPS!

Continue Reading

Create vector layers using PostGIS and QGIS

By Ryan Lambert -- Published August 04, 2019

The goal of this post is to show how PostGIS and QGIS can be used to create spatial data not bound to Earth. Why bother? As I explained in a recent bug report, there are few valid examples of non-Earth use cases for PostGIS:

For this post I focus on creating a fictional world for use in a novel. Most fictional worlds mimic familiar attributes and properties we know on Earth, such as roads, cities, government boundaries, buildings, rivers, lakes and so on. The difference in a fiction world are all the names, locations, routes and details!

Continue Reading

Postgres and Pi: Use the right SD card

By Ryan Lambert -- Published July 24, 2019

I have written a lot about running PostgreSQL on the Raspberry Pi. In the course of this endeavor, the most frequent comment I hear is:

"... But the SD card!"

One of the common complaints about the SD card is it's poor random read/write performance characteristics. This is a well-known side effect of the way SD cards were originally designed for digital cameras and high definition video recording. The SD 5.1 specification included new "application class" designations, more on the details later. This week I finally had the chance to put an A1 card to the test in a Raspberry Pi 3B.

Spoiler alert: You want SD cards with A1 or A2 designations in your Raspberry Pi.

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

Continue Reading

Installing PostgreSQL and PostGIS from source

By Ryan Lambert -- Published July 11, 2019

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.

Continue Reading

PostgreSQL deployments using Ansible and Sqitch

By Ryan Lambert -- Published July 07, 2019

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.

Continue Reading