Highly Available Postgres with Laravel

PHP and MySQL go together like peas and carrots. This is because they both let you get away with a lot of crap, like pretending numbers and strings are interchangeable.

However, some people (within the PHP world) are rebels against the mainstream. These people choose PostgreSQL - the database that’s so hip, no one even knows how to pronounce it.

For those who are PG-curious, let’s see how to setup Laravel with a PostgreSQL cluster on Fly.io.

A Laravel App

To start, we’ll create a new Laravel application. As I usually do, I’ll also include Breeze to quickly scaffold a working authentication system.

Since this authentication system involves adding users to a database, that’ll give us what we need for a useful demonstration.

# Create an app
composer create-project laravel/laravel pgsql
cd pgsql
composer require laravel/breeze --dev

# Install Breeze
# It's interactive: I chose blade, no dark mode, and pest
php artisan breeze:install

# Infect our app with javascript
npm install
npm run build

# Test it out
php artisan serve

I didn’t bother setting up a database just yet, so attempting to register a user will fail for now. That’s what PostgreSQL is for!

Launch Your App on Fly.io

Let’s run our app on Fly.io. Again, no database will be available yet.

# From folder pgsql, where our app is
fly launch

# Choose your options...then deploy

PostgreSQL on Fly

Fly PostgreSQL is selling cars. Fly.io builds it, sells it, and gives you the keys. There’s a maintenance plan (backups), but you need to know how to drive and maintain it yourself. What does that mean? Well, the first page you see in the docs is labeled This Is Not Managed PostgreSQL. Check out the details on what is and is not done for you.

The first paragraph here mentions that we use Stolon to cluster together multiple PostgreSQL instances.

This is nice because you get Highly Available PostgreSQL, without having to setup this stuff yourself.

Stolon has a few things going on:

1. A Proxy to ensure you’re talking to the Primary database for writes
2. A Keeper to run alongside PostgreSQL and share data about that instance
3. A Sentinel to register and track the PostgreSQL instances in the cluster

As a user of Fly PostgreSQL, you just need to decide how many replicas you want, and then use the hostname provided to connect to your PostgreSQL instance.

Setup a PostgreSQL Cluster

Let’s create a Postgres database in Fly.io, and then connect to it.

# I named it "larapg"
# I used the same region as my app
# I selected a production config (primary / replica created, in one region)
fly pg create

The “production” options create 2 pgsql instances and sets up replication between them (within one region). They’ll auto-fail over if something goes wrong.

These 2 instances have 80gb volumes attached to them by default.

Check Your PostgreSQL

The easiest way to poke around your new database instance is to run:

# Start a psql shell
fly pg connect -a larapg

You can run all your favorite psql commands, like \l to list databases or \c to connect to a database.

Note that no databases were created for you just yet!

Attach a Database

Let’s create a database for our application to use.

There’s a handy command to “attach” a Fly.io app to your Postgres database. The fly pg attach command will:

1. Create a new database and user
2. Set a handy DATABASE_URL environment variable in your attached Fly app

# Attach database "larapg" to our 
# Laravel app named "lara-psql-example"
fly pg attach larapg --app lara-psql-example

If we head into our app, we can see the DATABASE_URL environment variable is present!

DATABASE_URL=postgres://lara_psql_example:some_password@top2.nearest.of.larapg.internal:5432/lara_psql_example?sslmode=disable

Use the Database in Laravel

We next need to configure our Laravel application to talk to this database.

Laravel happens to listen for a DATABASE_URL environment variable. In fact, if you just start using your Laravel application, you’ll see it already connects to Postgres:

Laravel correctly generates a Postgres connection, even though the default connection is mysql (peas and carrots). As a result, Laravel attempts to set the encoding to something MySQL specific - good old utf8mb4. PostgreSQL has always supported 4-byte UTF8 (otherwise known as UTF8), and doesn’t understand the mb4 part. To fix that, we just need to set Laravel’s default connection to pgsql.

Update fly.toml to do so:

[env]
  APP_ENV = "production"
  LOG_CHANNEL = "stderr"
  LOG_LEVEL = "info"
  LOG_STDERR_FORMATTER = "Monolog\\Formatter\\JsonFormatter"
  DB_CONNECTION="pgsql"

We just added DB_CONNECTION there! Once we fly deploy, our connection to Postgres will start to work.

While we’re here, lets setup some migrations. We could use a release_command. However, I’m going to go straight to a Laravel-specific startup script. We’ll create a script to run artisan migrate anytime we spin up our application in Fly.io (during deploys, etc).

Create file .fly/scripts/migrate.sh and add:

#!/usr/bin/env bash

/usr/bin/php /var/www/html/artisan migrate --force

Once that’s created, deploy the app and we’ll see if it works!

fly deploy

Our logs will show that the migrations were run: https://d.pr/i/S1z2DL

We’re able to register users now! Our data is in the database: https://d.pr/i/jEPKKW

Voilà, we now have a HA Postgres cluster to use with our Laravel applications!

What Else?

Be sure to review the Postgres docs to see all the fancy things you can do (backup, scale, update, multi-region support, etc).

My personal favorite things are adding a TimescaleDB plugin for time series data, and figuring out read-only replicas.