Deploying Multi-region Laravel with Multi-region PostgreSQL

Show & Tell
1

I have successfully deployed a complex TALL stack Laravel App to multi regions with a multi-region PostgreSQL database. Here is how I did it.

Out of the box, Laravel supports splitting the read and write configuration of a database connection. Read about it here.

The FlyIO Multi-region PostgreSQL requires your apps to programmatically adjust the SQL reading and writing functions to allow the multi-region features to work correctly.

Deploy your multi-region database following these instructions.

Adjust the config/database.php file to match the flyIO documentation, its really simple, here is mine:

'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'read' => [
        'port' => '5433',
    ],
    'write' => [
        'port' => '5432',
    ],
    'sticky' => true,
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'search_path' => 'public',
    'sslmode' => 'prefer',
],

make a careful note to include the line 'sticky' => true, as this will allow Laravel to query the primary database directly after a write operation avoiding replication timing issues.

The DB_HOST env or secret should be the url provided when you attached your read replicas to your app using fly pg attach. Mine looks like this: DB_HOST = "top2.nearest.of.<db-app-name>.internal"

Essentially, setting the database config up like this allows Laravel to read from the nearest read replica but write to the primary when necessary.

And that’s it, so great that Laravel works natively with FlyIO Multi-region PostgreSQL :rocket:

A last note, we test locally and via github actions, when we setup a local or github actions postgres DB we cant use the approach above to connect. So we created a testing DB driver based on the standard pgsql driver in Laravel:::

        'pgsql_test_environment' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', 5432),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'search_path' => 'public',
            'sslmode' => 'prefer',
        ],

Hope this helps someone, Tom.

1 Like