Laravel db

Database tools for Laravel on PostgreSQL: copy a production database into a local Docker stack, with the personal data optionally masked; make, list and stream spatie/laravel-backup archives; restore a backup into production for disaster recovery.

The work is done by artisan commands, and a host script, vendor/bin/db-sync, pipes them together over ssh and docker compose from the developer's machine. Anonymization is off by default and is chosen at each copy with --anonymize.

Requirements: PHP 8.3+, Laravel 12 or 13, PostgreSQL.

• pg_dump and psql in the application images (production and local), at the major version of the PostgreSQL server and pinned: pg_dump refuses a server newer than itself. On Alpine: apk add postgresql18-client. psql must be one of the August 2025 minor releases or later (17.6, 16.10, 15.14, 14.19, 13.22, or any 18): the loader starts each load with \restrict, which makes psql refuse every meta-command of a dump (\! runs a shell command). An older psql stops the load with an explicit message and changes nothing.
• On the developer's machine: ssh, docker compose, and age to read encrypted backups. pv is optional and shows the progress of a transfer.
• spatie/laravel-backup ^9.2 or ^10 for the backup commands and the --backup mode.
• league/flysystem-aws-s3-v3 for backup disks on S3.
• The PHP extension zlib where db:restore or a gzip dump is read, and zip where db:import reads a spatie/laravel-backup archive.

pg_dump and psql get the connection of the application (host, port, database, username and password, with its url merged in) through the PGHOST, PGPORT, PGDATABASE, PGUSER and PGPASSWORD environment variables. The sslmode of the connection is not passed on. The two tools inherit the environment of the container, so PGSSLMODE and the other PGSSL* variables set there apply to them.

Installation

composer require un-dev-suisse/laravel-db
php artisan vendor:publish --tag=db-config

Do not install it with --dev: the server commands (db:dump, db:backup:list, db:backup:stream, db:restore) run in production. The service provider is discovered by Laravel, and registers its commands in the console only. The backup commands exist when spatie/laravel-backup is installed.

The published file may keep only the keys it changes. Every key is a whole: a key the published file declares replaces the default value and never mixes with it, so a truncate list that extends the default repeats the default tables.

Safety

• The copy writes only to the local database. db:import refuses to run when app.env is production, and on a database listed in protected_databases (testing by default). db:restore refuses a protected database too.
• The disaster-recovery restore (--restore-production) refuses without a terminal, asks to retype the name of the production database, and the server compares what was typed with its own database name.
• A load is one transaction. db:import and db:restore commit only when the end of the dump has arrived: a cut stream, a failed statement or a failed scrub leaves the target as it was.
• The live dump is an ssh stream: no dump file is written on the server.
• Neither the package nor the host script prints a row value. The ssh target and the paths live in the local .env.
• ssh and the age private key stay on the developer's machine, and no S3 access is needed there: the server reads the backup disks. Only the zip password (DB_SYNC_ZIP_PASSWORD) reaches the local container, by name through docker compose exec -e.

The host script

Run it from the root of the project, on the developer's machine:

vendor/bin/db-sync                                 # live dump of production (default)
vendor/bin/db-sync --anonymize                     # the same, with db.anonymize applied
vendor/bin/db-sync --backup                        # from the newest backup
vendor/bin/db-sync --backup=2026-10-26-03-00-00    # from this backup
vendor/bin/db-sync --list                          # the backups of the server
vendor/bin/db-sync --restore-production=<id>       # restore a backup into PRODUCTION

--anonymize goes with the live dump and --backup. A second mode, or --anonymize with --list or --restore-production, prints the usage and exits with 2. --help prints the usage and the settings.

Settings

The settings come from the environment, else from the DB_SYNC_* lines of ./.env. They belong in the local, unversioned .env.

• .env is parsed line by line, never sourced or evaluated: a value is data, never a command. Only the DB_SYNC_* names above are read. Quotes and a trailing comment are dropped, nothing is expanded, and a variable set in the environment wins over the file.
• The login shell of the server must be POSIX sh or bash: the commands travel as one string that the shell parses, every value quoted.
• Several processes share the terminal (ssh, age, docker compose). Rely on ssh-agent or ControlMaster, and on an age identity without a passphrase.

Restoring a backup into production

--restore-production=<id> replaces the content of the production database with a backup. The restore is not scrubbed. Find the id with vendor/bin/db-sync --list.

Before it, on the server:

1. Put the application in maintenance mode: php artisan down.
2. Stop the queue workers. The reset waits for the locks they hold on the tables.

Then run the script from a terminal. It prints which backup goes to which host, asks to type the name of the production database, and restores. Afterwards php artisan up and start the workers again.

• The tables are locked from the start of the restore until it is committed: the reset takes its locks before the archive is read, so nothing else reads or writes the database while the archive is transferred to the server, checked and loaded. A restore that fails or is interrupted (SIGINT, SIGTERM) leaves the database as it was.
• db:restore reads zip archives only. The CRC32 of the dump in the archive is verified before the restore commits; plain SQL and gzip carry none, so they are refused with exit code 1 and the database is left as it was.
• db:restore reads the archive as a stream: nothing of it is written to the server's disk. It refuses, with exit code 1 and the database left as it was, an archive with a zip password (age protects the backups), an archive whose entries carry their sizes after their data, and an archive that does not hold exactly one stored or deflated .sql or .sql.gz dump under db-dumps/. The archives of spatie/laravel-backup written without a zip password meet these conditions.
• The restore resets the target: every schema is dropped (DROP SCHEMA … CASCADE, except pg_* and information_schema), the large objects are deleted, public is created again, and the archive is loaded. The lines of the archive that GRANT, REVOKE, ALTER DEFAULT PRIVILEGES or change an owner (OWNER TO) are skipped. After a restore, public belongs to the connecting role and nothing is granted on it to PUBLIC: a PUBLIC usage grant on a schema, or any custom privilege on a schema, table or sequence, is not restored. Grant it again if the application relies on it. The connecting role must be allowed to drop every schema of the database and to create public, and must own the large objects of the database, which the reset deletes with lo_unlink.

What the import changes

db:import loads the dump into the default connection of the local application, then runs the scrubs in the same transaction, before the commit: truncate, encrypted, with --anonymize the anonymize entries, and the refresh of the materialized views.

truncate, encrypted and anonymize act on the tables of the public schema only, and so does the gate db:anonymize:check: a table of another schema is neither scrubbed nor reported.

The input of db:import can be a plain SQL dump, a gzip stream or a spatie/laravel-backup zip archive that holds one db-dumps/*.sql or .sql.gz entry.

Truncate

Add the tables of the project that hold tokens, such as OTP codes or invitations, next to the default ones:

// config/db.php
'truncate' => [
    'sessions', 'cache', 'cache_locks', 'jobs', 'job_batches', 'failed_jobs', 'notifications',
    'password_reset_tokens', 'personal_access_tokens',
    'otp_codes', 'invitations',
],

The tables go into one TRUNCATE without CASCADE: tables that reference each other can be listed together, and a table outside the list that references one of them stops the import.

Encrypted columns

The production values of an encrypted cast are unreadable locally, as they were encrypted with another key. An entry maps a table to its columns, and a column to a sample. Each non-null row gets its own encryption of the sample under the local key, so a unique index on the column holds, and a null value stays null.

'encrypted' => [
    'bank_accounts' => ['iban' => 'CH93 0076 2011 6238 5295 7'],
    'users' => ['two_factor_secret' => null, 'preferences' => '{}'],
],

• The sample is the plain text the cast would encrypt. For encrypted and the scalar casts it is any string. For encrypted:array, encrypted:collection, encrypted:object, AsEncryptedArrayObject and AsEncryptedCollection it is a JSON string, because Laravel decodes the value after decrypting it: '[]' or '{}', never ''.
• A null sample sets the column to NULL, for a nullable column only.
• A non-null sample needs app.cipher set to aes-256-cbc and the pgcrypto extension, which is created for the import and dropped after it if the database lacked it. Null samples need neither.
• When the dump installs pgcrypto in a schema outside the default search_path (Supabase uses extensions), the import cannot call its functions: with a non-null sample it fails and rolls back, and the target is untouched.
• A table or column the database does not have is skipped.

Anonymization

--anonymize masks the columns of anonymize, a map of table to map of column to the name of an anonymizer. A null value stays null. Each table gets one UPDATE, in the import transaction.

'anonymizers' => [
    'iban' => App\Anonymizers\SwissIban::class,
],
'anonymize' => [
    'users' => ['email' => 'email', 'name' => 'name', 'phone' => 'null'],
    'counterparties' => ['notes' => 'text', 'iban' => 'iban'],
],

email, name and phone depend only on the primary key: the same row gives the same value on every import. email and name are unique per row. phone is the key itself, zero-padded to nine digits, when the key is a whole number of nine digits or fewer (written without leading zeros): such numbers are unique. For any other key (a UUID, a longer number) it is derived from a hash of the key, so two rows can in principle share a number. They need a table with a single-column primary key (an INCLUDE clause on the key is fine); any other table is refused. text, null and value: give equal values to different rows, so they do not suit a unique column.

A name in anonymizers takes precedence over a built-in one of the same name. An unknown name refuses the import before anything is read. A table or column the database does not have rolls the import back.

The scrubs are plain UPDATE and TRUNCATE statements that run after the dump is loaded. A materialized view stores the rows it was filled with from the production tables, so the import refreshes every populated materialized view (REFRESH MATERIALIZED VIEW, in creation order, so that a view built on another one is refreshed after it) as the last step of the same transaction, with or without --anonymize: a view shows the masked values, and none of the rows of a truncated table. A view dumped WITH NO DATA stays empty. Do not list a materialized view in truncate: PostgreSQL refuses to TRUNCATE a materialized view, and the import fails.

A custom anonymizer

A custom anonymizer implements UnDevSuisse\Db\Anonymizer and is built by the container. It returns the SQL expression that PostgreSQL evaluates for every non-null row:

namespace App\Anonymizers;

use Illuminate\Contracts\Database\Query\Expression as ExpressionContract;
use Illuminate\Database\Query\Expression;
use UnDevSuisse\Db\Anonymizer;

final class SwissIban implements Anonymizer
{
    public function expression(string $table, string $column): ExpressionContract
    {
        return new Expression("'CH00 0000 0000 ' || lpad({key}::text, 9, '0')");
    }
}

• $table and $column are plain names, not quoted: the expression quotes an identifier itself.
• The expression is a single value expression, placed after ELSE in a CASE.
• {key} stands for the quoted single-column primary key of the table. An expression built on it gives the same value for the same row on every import. A table without such a key is refused.

Checking the configuration

php artisan db:anonymize:check

Reads the base tables of the public schema of the default connection, and the models under model_paths. It never reads a row. It prints a Column | Problem table and exits with 1 when there is a row; otherwise it prints Every personal-looking column is covered. and exits with 0, so it serves as a CI gate on a migrated database.

A column that is personal in name only goes in not_personal as table.column. Views are not inspected.

Backups

With spatie/laravel-backup installed, the package adds the backup commands. They work on the destination disks of backup.backup.destination.disks. A disk on S3 needs league/flysystem-aws-s3-v3.

// config/filesystems.php
'backups-local' => ['driver' => 'local', 'root' => storage_path('backups')],
'backups-s3' => ['driver' => 's3', 'bucket' => env('BACKUP_S3_BUCKET'), /* … */],

// config/backup.php
'disks' => explode(',', env('BACKUP_DISK', 'backups-local')),

A local backup disk on the server must sit on a Docker volume, otherwise a recreated container loses its archives.

Every server command writes its data to stdout and its messages to stderr, and exits non-zero on failure: db:dump returns the exit code of pg_dump, db:backup that of backup:run, the others exit with 1.

A backup id is the name of its archive without .zip (<prefix>Y-m-d-H-i-s). The same id on two disks is two entries; the first disk of the configuration answers.

Schedule db:backup in the application, as any command.

Encrypted backups

Archives encrypted with age (files-storage.backups.age_recipients of un-dev-suisse/laravel-files-storage) leave the server encrypted. db-sync decrypts them with the local age private key, DB_SYNC_AGE_KEY, which never leaves the developer's machine. An archive encrypted with age that reaches db:import or db:restore without being decrypted is refused. An archive that has a zip password is opened by db:import with DB_SYNC_ZIP_PASSWORD; db:restore refuses it.

Development

docker compose run --rm php composer check   # pint, larastan, phpunit, shellcheck
docker compose down