How to use streaming replication with PostgreSQL

In this post, I want to explain how to set up streaming replication for PostgreSQL. We want to be able to do (read-only) reporting on live data, but reduce load on the main database server. Some background: We offer Metabase to many of our customers for data analysis and reporting. This means we don’t have to hand-code everything into our application. It even allows savvy customers to write their own reports.

Subtleties of time zone calculations in Postgres

As any programmer well knows, time zones can be a real pain. They seem almost invented just to make programmers’ lives more difficult! Luckily, if your tools are good, a lot of pain can be spared. At Code Yellow, we work a lot with PostgreSQL. This really has time zones handled. Imagine my surprise when a customer opened a ticket that they got unexpected dates in their CSV files.

Fun with foreign data wrappers

One of our clients is expanding their business into different countries. At Code Yellow we’ve created custom production software for this client. Of course, they would like to use our software in the other countries, too. At first, we considered modelling companies explicitly in the database, and adding scoping to each and every view and query in the system. This would be a valid approach, but quite prone to mistakes when adding such filters to an existing large system.