How pg_dump broke a Laravel job on Serverless SQL

Three nights in a row, a Laravel job that copies files to a backup bucket failed at 01:40. Its error named a table that was clearly there: relation "cache_locks" does not exist. Looking at the table in the morning showed nothing wrong with it. The cause sat in a different job that ran at the same moment, and in the way Scaleway Serverless SQL shares connections.

This post walks through what happened, why, and how to keep a database dump from reaching your application's queries. It applies to any Laravel application that runs pg_dump against a Serverless SQL Database while it serves requests or works its queue, whether it runs on Brewless or not.

Why does Laravel say a table does not exist when it is there?#

Because PostgreSQL could not find it in the schemas the session looks in. A query such as delete from "cache_locks" names no schema, so PostgreSQL looks in the schemas of the session's search_path. When that list is empty, no table is found, and the error says it does not exist.

Laravel sets search_path to public when it opens a connection ('search_path' => 'public' in config/database.php). That is a setting of the session, made once. On a database you connect to directly, it stays for as long as the connection does.

What does a connection pooler change?#

A Serverless SQL Database is reached through a connection pooler: a component between the clients and the database that keeps a few server sessions open and hands them to whichever client sends a query. Scaleway documents what that means for session settings: a SET search_path is shared across clients and leads to unwanted effects.

So the session a query lands in is not necessarily the one Laravel set up. It can be one another client changed.

What does pg_dump have to do with it?#

pg_dump empties the search_path of its own session before it reads the database, so that every name it writes in the dump is qualified with its schema. That is the right thing for a dump. Behind a pooler, though, the session with the empty search_path goes back to the pool when pg_dump is done with it, and the next client to get it is your application.

In the case above, the backup command put two kinds of jobs on the queue at 01:40: one that dumps each database with pg_dump, and one that copies files. The file job takes a unique lock in Laravel's database cache, and releases it when it is done. That release ran in a session pg_dump had used, and failed on cache_locks.

Note

The lock error also hid the job's real error, and the mail the job sends when it fails: Laravel releases the unique lock before it calls the job's failed() method. One broken session took both the job and the alarm about it.

How do you keep a dump out of your application's sessions?#

Let pg_dump connect as a database user of its own. A pooler such as PgBouncer keeps a pool per user and database, so the sessions of the dump never go to the application, and the other way around. Scaleway does not document how its own pooler groups sessions; the user of its own is the change that does not depend on it being otherwise.

On Scaleway Serverless SQL, a database user is an IAM principal:

1. Create an IAM application for the backup, in the project of the database.
2. Give it a policy with the permission set ServerlessSQLDatabaseReadOnly, which allows SELECT. A dump only reads.
3. Make an API key for it. The application's ID is the user name; the key's secret is the password (Scaleway: manage permissions).
4. Give the dump those two values instead of the application's own, for example as PGUSER and PGPASSWORD of the pg_dump process.

$process = new Process(
    ['pg_dump', '--format=custom', '--no-owner', '--no-privileges', '--file='.$target, '--dbname='.$database],
    null,
    [
        'PGHOST' => $connection['host'],
        'PGPORT' => (string) $connection['port'],
        // A user of its own: its sessions stay apart from the application's.
        'PGUSER' => config('backup.dump_username'),
        'PGPASSWORD' => config('backup.dump_password'),
        'PGSSLMODE' => 'require',
    ],
);

Warning

Copying the application's DB_USERNAME and DB_PASSWORD into the dump's settings changes nothing: the dump then shares the application's pool again. It has to be a different user.

A read-only user is also the safer choice on its own merits: a backup credential that leaks cannot change or delete anything.

Where do the two values go on Brewless?#

They are secrets, so they go to the environment's variables in your own Secret Manager, never in brewless.yml:

brewless env:pull production
# add the two lines to .env.production
brewless env:push production

The client finds the application in the brewless.yml of the folder you run it in. Run both commands in the folder of the application the variables are for. See Environment variables for how revisions and pushes work.

What else on a pooled database depends on session settings?#

Anything an application or a tool sets with SET for its session can travel the same way: the search_path, the time zone, and other run-time parameters. Scaleway notes that these can also be lost when the database scales or goes idle. Two habits keep an application safe:

• Do not rely on a setting made once for the session. Write times with their offset rather than trusting the session's time zone.
• Keep tools that change session settings, such as pg_dump, on a database user of their own.