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.

FOSDEM 2018

We’ve visited the yearly FOSDEM event, where open source developers gather. The place to get free stickers, see other developers and listen to inspiring talks.

“First time visitor”:

First time visitor

“Pro visitor”:

Pro visitor

“Free coffee”:

Free coffee by Github

The most “interesting” talk I’ve visited was the one of IoT.js, a Javascript framework for Internet of Things. My first reaction:

But Why?

APIs only a mother could love

Lately we’ve been integrating more 3rd party APIs than usual, and the experience was less than great in almost every case. Let’s take a look at how ugly some APIs will get. In the descriptions below, company names have been redacted to protect the (not so) innocent. XML is not hip, we must offer JSON One of our 3rd party vendors has a geocoding API that is based on XML.

QR encoding

Intro For a project we had to put text in QR-codes, seemed simple enough, but sadly the printers that our customers used only supported the alphanumeric-type QR-code which has a small available character set of 45 characters (0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ $%*+-./:). But we wanted to be able to send unicode strings which require a much larger character set. So it quickly became apparent that we needed some kind of encoding. The most well-known encoding is probably Base64 however sadly this uses too many characters for our goal.

Accessing your Vagrant boxes through a reverse proxy

The past year we’ve been making increased use of Vagrant to streamline our development systems. Vagrant makes it easy to manage and provision virtual machines for each project we work on. When a new developer joins a project team, there is no more need to spend half a day fiddling to make the dev stack “just so” that it works for the project. In this post, we’d like to show why we made a Vagrant plugin for proxying your local HTTP server to your vagrant boxes.

Queue

In our organization we frequently need to execute some scripts that are either time consuming, or very heavy on the server. Most of the times these scripts needn’t necessarily be executed synchronously. Therefore we use a queuing system to execute those scripts when the time is better. For a long time we used an old open source PHP queuing system, named fuel-queue. This was good for the basic stuff we did with it, however this system had some major drawbacks.

Refactoring our frontend development stack

At Code Yellow we write frontend heavy apps. This means that the browser is responsible for all the rendering, and the backend only handles API requests. In the last few weeks we have been busy refactoring a large part of our backend, frontend and development workflow to ensure the best technologies available are used. In this article I want to highlight the changes we made to our frontend stack. As such, this article is intended for frontend developers.

Tangible Breakage

After spending many years coding and building software products from scratch, you know that stuff breaks (for example because of bugs or server crashes). Software is intangible, so it doesn’t break like glass does. But even though it’s intangible, software can become old and even the bits can rot. It needs maintenance and care for it to run for years on end. A notable exception is writing apps, and shipping the tablets as well.

Kernel backups for Debian security updates

Two of our most important machines, both running Debian Stable, failed to boot after a routine kernel security update. In both cases, the security update itself was not the cause of the boot failure, but merely triggered latent problems (for the full story, see below under The problem with the snapshot machine). Also in both cases, it would have been a great help if we could have booted the old kernel/initrd.

What your framework never told you about SQL injection protections

We’ve discovered that SQL injection is to this day not a fully solved problem, even in most popular frameworks. In this post, we’ll explain how these frameworks fail at escaping parts of a query, culminating in the discovery of a critical vulnerability in the popular Laravel framework which affects a large percentage of applications. Let’s start with an innocent example, which provides the starting point of our journey. This is a typical simple use case: a filterable, sortable list.

Just some days at the office

Customer Changes Requirement just before the deadline

devops 1

Non standardized environment

devops 2

Managers testing a new feature

devops 3

Typical Monday Morning

devops 4

Git push –force

devops 5

Fixing bugs on live production server

devops 6

The aftermath of a rushed release

devops 7

Thinking about targets for next year

devops 8

Realizing I ran the command on the wrong server

devops 9

OpenSSL::SSL::VERIFY_NONE

devops10

It is a quick change…

devops11

Pre-Coffee Deploy

devops12

Realizing that accidentally deleted code was already pushed

devops13

Rapid Cordova application development

Developing Apache Cordova (aka Phonegap) applications tends to be rather painful: the long waits while compiling an application for Android and uploading it to the device (or emulator) leads to unacceptable round trip times, especially for applications with lots of large content files (images, videos). In this blog post we’ll explain how we develop Cordova applications directly in the desktop web browser, and announce an open source release of a RequireJS module that helps making this a little easier for us.

Passing variables from server to client using RequireJS

It’s quite common to pass variables from server to client. Common uses are bootstrapping data, syncing config setting etc. Consider the following scenario: a single page app where a user logs in and refreshes the current page. You want the user to still be logged in. The most commonly used practise is to put it in a script tag: <script type="text/javascript"> var userId = 1; </script> This way you introduce a global variable userId.

FuelPHP released version 1.7

FuelPHP 1.7 has been released. A pretty late post, but it’s been crazy busy the past few weeks. One important feature used immediately: PATCH request are now supported.

Using Trello for our Development Workflow

This is a repost of an article I wrote a couple of weeks ago, which featured another project as an example, but we received a request to change some aspects of the original story. We decided the quick fix was to pull the original site and rewrite it with other examples. At Code Yellow we are experimenting with Trello for structuring the development process. In essence Trello is a tool developed to manage lists in any way you want.

Migration transactions

Transactions are extremely useful…except when dealing with migrations. Read about implicit commits to understand why transactions in migrations aren’t that useful.

Coding style guide

Introduction The 2 most used languages at Code Yellow are PHP Javascript PHP has PHP-FIG which is becoming more commenly accepted. Javascript has idiomatic. For all projects we apply these rules: General First check: PSR-1 PSR-2 Use single quotes for strings where possible: PHP $stringA = ‘this is a string’; $stringB = ‘this is another string ’ . $blah . ‘ test’; Javascript var stringA = ‘this is a string’, stringB = ‘this is a string ’ + blah + ‘ test’; First start with properties, then functions.

FuelPHP released version 1.6.1

Joy to the world! FuelPHP 1.6.1 has been released FuelPHP released a new version.

Mostly minor improvements from the 1.6 with some backported functionallity from the 1.7 branch.

Elementary OS Luna Beta 2 Released

rpi 1

Almost all of the desktops and laptops at Code Yellow are running Elementary OS and today Beta 2 of this operating system has been released. Some of our machines are running daily updates so in a sense they already were up to date to the latest version. Read more about the new beta on the Elementary home.

And of course a linux classic (from XKCD ):

xkcd.com/149

FuelPHP released version 1.6

Today the kind people at FuelPHP released a new version of their framework, promising some big changes and improvements. The official introduction of Composer is perhaps the most noteworthy of the changes. Composer has been the main reason to choose for FuelPHP as the core framework of our web applications, since it gives us the ease of combining our own packages into the stable FuelPHP framework. Keep an eye on our blog for a more detailed overview of the amazing features of composer.

Raspberry Pi prototyping development

At Code Yellow we use the Raspberry Pi for quick prototyping. The Debian based Raspbian image, available at the Raspberry Pi Download section is easy to use and little extra knowledge of Linux is required. The device is mainly used to build fully working prototypes with other hardware, in order to simulate and test the concept of embedded hardware which interacts with our web applications. USB support is excellent and with the power of Python we are able to develop quickly some concepts.

The ideal development image

To take full advantage of the Proxmox development servers we decided to make a fresh development image with all settings preset, so when you start a project, you only have to load a fresh image into a Virtual Private Server and go off developing great things. In this post we would like to share with you the default settings and configuration of the setup: Ubuntu server 12.04 (minimalistic install) MySQL DVCS: Mercurial and Git PHP 5.

New development and test environment installed

Today some new servers arrived at Code Yellow HQ! The boxes were obsolete at a data center of one of our customers so we got 4 nice pieces of hardware to set up a new development environment. The new development environment consists of 4 dedicated boxes, all loaded into a Proxmox cluster. For easy deployment we use a development image of Ubuntu server 12.04 with some tweaks. For each project it is easy to setup a new VPS, load the code from our repositories hosted at Bitbucket and get on with developing and testing.