# How pg_dump broke a Laravel job on Serverless SQL

A nightly backup job failed with 'relation does not exist' on a table that was there. The cause: pg_dump and a connection pooler sharing session settings.

## In short

- Scaleway Serverless SQL puts a connection pooler in front of the database, and a session setting one client makes can reach another client.
- pg_dump empties the search_path of its own session, so a Laravel query that lands in that session afterwards finds no table without a schema in front of it.
- The symptom is a "relation does not exist" error on a table that exists, at the moment a dump runs.
- Giving pg_dump a database user of its own keeps its sessions apart from those of the application, because a pooler such as PgBouncer keeps a pool per user.
- Copying the application's DB_USERNAME and DB_PASSWORD for the dump does not help, because the dump then shares the application's pool again.

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](https://www.scaleway.com/en/docs/serverless-sql-databases/reference-content/known-differences.md).

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

| | Direct connection | Behind a pooler |
| --- | --- | --- |
| Who owns the server session | Your connection | Whoever the pooler gives it to |
| A `SET search_path` you make | Stays with you | Can reach another client |
| A `SET search_path` another client makes | Never reaches you | Can reach you |

## 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](https://www.scaleway.com/en/docs/serverless-sql-databases/how-to/manage-permissions.md)).
4. Give the dump those two values instead of the application's own, for example as `PGUSER` and `PGPASSWORD` of the `pg_dump` process.

```php app/Services/DatabaseDump.php
$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`:

```bash
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](https://docs.brewless.eu/running/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](https://www.scaleway.com/en/docs/serverless-sql-databases/reference-content/known-differences.md). 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.

## Frequently asked questions

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

PostgreSQL looks up a table without a schema in front of it in the schemas of the session's search_path. When that list is empty, it finds no table at all. On a pooled database such as Scaleway Serverless SQL, another client can have emptied it in the session your query lands in.

### Does pg_dump change the search_path?

Yes. pg_dump sets the search_path of its own session to an empty list before it reads the schema, so every name it writes is qualified. On a database behind a pooler, that setting can stay on the server session after pg_dump is done with it.

### Which permission does a pg_dump user need on Scaleway Serverless SQL?

An IAM application with the permission set ServerlessSQLDatabaseReadOnly in the project of the database, which allows SELECT. Its ID is the database user name, and the secret key of an API key of that application is the password.

### Can I use the application's own database user for backups?

You can, but on a pooled database the dump then shares the application's sessions, and its empty search_path can reach the application's queries. A user of its own keeps the two apart.

Source: https://test.brewless.eu/blog/pg-dump-serverless-sql-search-path