README

Rebuild a Laravel developer's local database from production in one command. Extends spatie/laravel-db-snapshots with the three pieces every project otherwise reimplements: a driver-aware dump sanitizer, a sync orchestrator, and the token-protected internal API the source server exposes.

snapshot:sync --fresh
  → trigger a fresh dump on production (internal API)
  → download the .sql.gz (or .sql.zst)
  → snapshot:sed  (strip directives that reject on a local client)
  → load: psql, in one transaction (PostgreSQL)
          snapshot:load --drop-tables --force --stream (MySQL/MariaDB)

Supports PostgreSQL and MySQL/MariaDB.

On the source side it also keeps those snapshots off-site: snapshot:backup copies them to any Flysystem disk (S3/Spaces, Google Drive, a NAS over sftp), retention goes by age instead of file count, snapshot:backup-check raises an event when a copy goes stale, and snapshot:drill proves the off-site copy actually restores. See Off-site backup & retention.

Why

spatie/laravel-db-snapshots dumps and loads, but a dump made by a production pg_dump / mysqldump rejects on a developer's locally-installed client — psql meta-commands (\restrict, SET transaction_timeout), owner/grant lines, or MySQL DEFINER= clauses that need SUPER. This package strips those, and adds the download side so the whole "copy prod down to local" loop is one command instead of copy-pasted per project.

Install

composer require phattarachai/db-snapshot-sync-laravel
php artisan db-snapshot-sync:install

The installer publishes the config, writes the env keys (generating a token), and prints the source-side snippets you paste into config/db-snapshots.php, config/filesystems.php, config/database.php (dump exclusions) and the scheduler. It requires spatie/laravel-db-snapshots and a snapshots filesystem disk on both ends.

Commands

snapshot:sed {name?} {--latest} {--connection=} {--driver=} — sanitize a snapshot on the disk so it re-imports cleanly. Postgres mutates in place; MySQL writes a separate .sanitized.sql.gz (.sanitized.sql.zst for a zstd snapshot). Without --connection/--driver it uses the rules for the engine named in the dump's header, falling back to the default connection's driver.
snapshot:sync {--fresh} {--source=production} {--connection=} {--no-load} {--keep-raw} — pull the latest snapshot from a source, sanitize, and load into the local DB. Runs only in the environments listed in db-snapshot-sync.sync.allowed_environments.
snapshot:backup, snapshot:prune {--days=} {--dry-run}, snapshot:backup-check, snapshot:drill {--disk=} {--connection=}: off-site backup, local retention, staleness check and restore drill. See Off-site backup & retention.

Start with a dry run once the source URLs are set:

php artisan snapshot:sync --no-load     # download + sanitize, no DB clobber
php artisan snapshot:sync               # full sync from production

Cross-engine and multi-connection sync

By default snapshot:sync sanitizes for, and loads into, the app's default connection. When a source's engine differs from that — say production is still MySQL while the app's local default has moved to PostgreSQL — name the local connection the dump belongs to, either per run:

php artisan snapshot:sync --source=production --connection=mysql

or once, on the source in config/db-snapshot-sync.php (the plain-string form keeps working):

'sources' => [
    'production' => [
        'url' => env('DB_SNAPSHOT_SYNC_PROD_URL'),
        'connection' => 'mysql',
    ],
    'dev' => env('DB_SNAPSHOT_SYNC_DEV_URL'),   // PG → PG, default connection
],

A source URL is a base URL, so it may carry a path: a source that serves the API under /api (/api/internal/snapshots) is 'url' => 'https://example.com/api'.

--connection wins over the source's connection, which wins over database.default. The connection's driver picks the sanitizer (so a MySQL dump gets its DEFINER= lines stripped even when the default is pgsql), and the name is passed to snapshot:load --connection.

Engine guard. Before sanitizing, the sync reads the dump's header (-- MySQL dump / -- MariaDB dump vs -- PostgreSQL database dump, plain, gzip or zstd). If it names a different engine from the target connection's driver, the sync stops with an error — nothing is sanitized or loaded, and the download stays on the disk. Without this, snapshot:load --drop-tables would drop every table on the wrong database first and only then fail on the foreign SQL. A dump whose header names neither engine is let through. snapshot:sed --connection/--driver applies the same check.

Loading into a non-default connection in-process. spatie's Snapshot::load($connection) calls DB::setDefaultConnection($connection) and never restores it, and a pg_dump leaves search_path = '' on the connection it ran through. snapshot:sync puts the caller's default connection back and purges the target connection after the load, so a command that calls snapshot:sync and then keeps querying sees its own default and a fresh session. If you call spatie's snapshot:load --connection=… directly, do the same:

$default = DB::getDefaultConnection();

try {
    Artisan::call('snapshot:load', ['name' => $name, '--connection' => 'mysql', '--stream' => true, '--force' => true]);
} finally {
    DB::setDefaultConnection($default);
    DB::purge('mysql');
}

The source side is unchanged: the internal API dumps the source app's own default connection.

The source-side API

Set DB_SNAPSHOT_SYNC_API=true (and the shared INTERNAL_API_TOKEN) on production/UAT to expose:

Method	Route	Purpose
GET	`/internal/snapshots`	list servable snapshots
POST	`/internal/snapshots`	create a fresh one synchronously (`--fresh`)
GET	`/internal/snapshots/latest`	stream the newest download

EnsureInternalToken 404s the whole group in local and checks a hash_equals bearer token otherwise; the routes carry throttle:5,1. The consumer sends the matching token from its .env.

How a PostgreSQL snapshot is loaded

A pgsql target is loaded with psql, not spatie's snapshot:load. spatie splits the dump into statements in PHP and treats a backslash inside a '…' literal as an escape, but pg_dump writes with standard_conforming_strings = on, where a backslash is literal. A value such as I\'ve (dumped as 'I\''ve') throws its quote tracking off. Everything after it folds into one trailing statement that never ends in ;, and spatie discards that without an error. The load then reports success with every later table empty.

psql is pg_dump's own parser. snapshot:sync streams the dump into it with ON_ERROR_STOP=1, inside one transaction that holds both the table drop and the restore. COMMIT is sent only after the whole file has been read and it ends with pg_dump's -- PostgreSQL database dump complete trailer. A failed statement, a read error or a truncated download therefore exits non-zero and rolls back to the database as it was. Connection settings reach psql as PG* environment variables, so the password never appears in the process list.

This needs the PostgreSQL client on the machine running the sync. Set DB_SNAPSHOT_SYNC_PSQL when psql is not on PATH (e.g. /opt/homebrew/opt/libpq/bin/psql). Of the load flags, only drop-tables applies to a pgsql load. MySQL/MariaDB still goes through snapshot:load.

Configuration

config/db-snapshot-sync.php covers the disk name, the compression format, token, source URLs, load flags, the psql binary, the per-driver sanitizer rules (add a prefix/sed expression when a new dump quirk appears), and the API's reject-list (never serve a schema-only baseline or a .sanitized. intermediate).

dump.exclude_table_data lists framework caches and transient queues (cache, sessions, jobs, pulse_*, telescope_*, …) whose data is skipped when the source builds a sync snapshot — the schema is still dumped, and a project's own rollback snapshots are untouched. This keeps the dev copy small; real domain tables are always included. The Postgres sanitizer streams the dump line-by-line, so a multi-GB snapshot sanitizes without loading the whole file into memory.

dump.rows_per_insert (default 1000) batches the sync snapshot into multi-row INSERTs. laravel-db-snapshots forces --inserts because its loader restores through PDO (which can't stream COPY), so a large table otherwise restores one round-trip per row — a million-row table can take tens of minutes. Batching cuts that to a couple of statements' worth of work. Applies to the sync snapshot only; the committed baseline stays single-row so it keeps diffing line-by-line.

On PostgreSQL the exclusion is pg_dump's --exclude-table-data. MySQL/MariaDB's mysqldump has no per-table "schema but no data" flag (--ignore-table drops the schema too), so the sync dump runs in two passes: the main dump --ignore-tables the excluded tables, then a --no-data dump of just those tables (the ones that exist, checked in information_schema) is appended to the snapshot as a second gzip member (a second frame in a zstd snapshot, which every zstd reader handles). gunzip, zcat and spatie's snapshot:load --stream read the whole file; PHP's gzdecode() (spatie's non-streamed load) stops at the first member, so load a raw MySQL sync snapshot with --stream. snapshot:sync already does both: the MySQL sanitizer recompresses the file into a single member.

dump.rows_per_insert is PostgreSQL-only (it becomes --rows-per-insert); mysqldump's default --extended-insert already batches rows.

Compression: gzip or zstd

snapshot:create --compress writes gzip (.sql.gz) by default, as spatie does. Set DB_SNAPSHOT_SYNC_COMPRESSION=zstd (config compression.format) to write .sql.zst instead; it applies to the scheduled snapshot and to the one the API builds for --fresh alike. zstd matches repeats across the whole dump where gzip's 32 KB window cannot, so a pg_dump shrinks far more: one 4.17 GB dump came to 251 MB with gzip and 21 MB with zstd -10 -T0, in about a second.

Reading never depends on the setting. Every command sniffs a file's format from its magic bytes, so a disk holding both formats lists, serves, syncs, sanitizes, loads, backs up and drills either. The package extends spatie's SnapshotFactory, SnapshotRepository and Snapshot so that snapshot:create, snapshot:load (streamed or not), snapshot:list, snapshot:delete and snapshot:cleanup handle .sql.zst too; gzip and plain snapshots take spatie's own code path.

PHP has no built-in zstd, so the zstd CLI does the work: on the source, and on every machine that syncs a zstd snapshot (brew install zstd, apt install zstd). It is looked up on PATH, then in /opt/homebrew/bin, /usr/local/bin and /usr/bin, which covers a launchd job or php-fpm whose PATH is only /usr/bin:/bin. Set DB_SNAPSHOT_SYNC_ZSTD to point elsewhere. DB_SNAPSHOT_SYNC_ZSTD_LEVEL (default 10; above 19 adds --ultra) and DB_SNAPSHOT_SYNC_ZSTD_THREADS (default 0, every core) tune the writer. A corrupt or truncated .sql.zst fails the load or sanitize instead of loading part of it.

Sources with an incomplete TLS chain

Some edges serve the leaf certificate but omit an intermediate. A browser or system curl fetches the missing intermediate via the certificate's AIA extension, but PHP's cURL does not, so the sync download fails with cURL error 60: unable to get local issuer certificate. Point DB_SNAPSHOT_SYNC_CA_BUNDLE (config http.ca_bundle) at the missing intermediate's PEM (absolute, or relative to the app base path) and the client appends it to the system trust store for the download only — the chain still fully verifies. Leave it unset for ordinary sources.

Off-site backup & retention

spatie's snapshot:create writes to one disk, usually on the same machine as the database, and snapshot:cleanup --keep=N deletes by file count, so a burst of deploy restore points shortens the window. These four commands put a copy somewhere else and age both sides by time.

Command	Job	What it does
`snapshot:backup`	`BackupSnapshots`	uploads local snapshots to every target disk, keeps `daily/` + `weekly/`, prunes the targets
`snapshot:prune {--days=} {--dry-run}`	`PruneSnapshots`	deletes local snapshots older than `local_days` (replaces `snapshot:cleanup --keep`)
`snapshot:backup-check`	`CheckSnapshotBackup`	dispatches `SnapshotBackupStale` / `SnapshotBackupHealthy` per target
`snapshot:drill {--disk=} {--connection=}`	`RunRestoreDrill`	restores the newest off-site copy into a scratch database and compares row counts

Every command exits non-zero on failure (a target failed, a copy is stale, a drill failed), so a scheduler that alerts on failed commands covers it. The jobs (Phattarachai\DbSnapshotSyncLaravel\Jobs\…) run the same code; they go on DB_SNAPSHOT_SYNC_BACKUP_QUEUE when set, never retry, and BackupSnapshots / RunRestoreDrill allow an hour, so put them on a queue whose worker --timeout covers that instead of riding default.

Configure

// config/db-snapshot-sync.php
'backup' => [
    'disks' => ['spaces'],          // any disks from config/filesystems.php, one or more
    'path' => 'db',                 // prefix on each target; let the disk's root carry the app/env
    'local_days' => 14,             // snapshot:prune deletes local snapshots older than this
    'keep_min' => 3,                // ...but never below the newest 3 (local, and each target's daily/)
    'daily_days' => 14,             // {path}/daily/ keeps every snapshot this many days
    'weekly_weeks' => 8,            // {path}/weekly/ keeps the newest snapshot of each ISO week
    'stale_after_hours' => 26,      // snapshot:backup-check threshold
    'protect' => ['testing'],       // snapshot names never pruned and never uploaded
    'queue' => env('DB_SNAPSHOT_SYNC_BACKUP_QUEUE'),
],

On each target, snapshot:backup:

uploads every local .sql / .sql.gz / .sql.zst from the last daily_days that is not yet under {path}/daily/ with the same size. A night the box missed goes up on the next run, and a short copy left by an interrupted upload is replaced. Protected snapshots and anything in api.reject (.sanitized. intermediates) stay local. A file modified in the last 60 seconds is left for the next run, because spatie may still be copying it onto the disk.
copies the newest snapshot of each of the last weekly_weeks ISO weeks to {path}/weekly/{YYYY-Www}_{name}, and replaces the current week's copy when a newer snapshot lands. That copy is made on the target itself when the daily copy is there (server-side on S3/Spaces), so nothing is uploaded twice.
prunes daily/ by the copy's age on the target and weekly/ by the week in its name, never below keep_min on either.

Files are streamed (readStream / writeStream), never read into memory, and every object is written with private visibility explicitly, whatever the disk's own default. After each upload the target size is compared with the local one; a mismatch deletes the copy and fails that target. One target failing does not stop the others.

Target disks

The package needs no Flysystem adapter itself. Install the one your disk uses. Setting 'throw' => true makes a failure carry the adapter's own error message.

DigitalOcean Spaces / S3 (composer require league/flysystem-aws-s3-v3):

'spaces' => [
    'driver' => 's3',
    'key' => env('DO_SPACES_KEY'),
    'secret' => env('DO_SPACES_SECRET'),
    'region' => env('DO_SPACES_REGION'),
    'bucket' => env('DO_SPACES_BUCKET'),
    'endpoint' => env('DO_SPACES_ENDPOINT'),
    'root' => env('DO_SPACES_ROOT'),          // e.g. myapp/production
    'throw' => true,
],

Private visibility is sent as an object ACL. That works on Spaces and on S3 buckets with ACLs enabled. An AWS bucket set to "bucket owner enforced" (ACLs disabled) rejects it.

Google Drive (composer require masbug/flysystem-google-drive-ext). Register a driver in a service provider. For an unattended server, use a service account writing into a Shared Drive: a service account has no My Drive storage quota, so uploads into a plain folder fail, and teamDriveId is required. Add the service account's email to the Shared Drive as a Content manager.

use Google\Client;
use Google\Service\Drive;
use Illuminate\Filesystem\FilesystemAdapter;
use League\Flysystem\Filesystem;
use Masbug\Flysystem\GoogleDriveAdapter;

Storage::extend('google-drive', function ($app, array $config): FilesystemAdapter {
    $client = new Client;
    $client->setAuthConfig($config['service_account']);   // path to the key JSON, or its decoded array
    $client->addScope(Drive::DRIVE);

    $adapter = new GoogleDriveAdapter(new Drive($client), $config['root'] ?? null, [
        'teamDriveId' => $config['shared_drive_id'],
    ]);

    return new FilesystemAdapter(new Filesystem($adapter, $config), $adapter, $config);
});

'gdrive' => [
    'driver' => 'google-drive',
    'service_account' => env('GOOGLE_DRIVE_SERVICE_ACCOUNT'),   // e.g. /etc/myapp/gdrive-sa.json
    'shared_drive_id' => env('GOOGLE_DRIVE_SHARED_DRIVE_ID'),
    'root' => env('GOOGLE_DRIVE_ROOT'),                        // e.g. myapp, a folder in the Shared Drive
    'throw' => true,
],

Keep the callback self-contained: Laravel rebinds an extend closure to the FilesystemManager, so $this inside it is the manager, not your provider, and calling a private method of the provider from it fails.

google/apiclient pulls in every Google API's client (tens of thousands of files). Trim it to Drive in the app's composer.json, then run composer update google/apiclient-services:

"scripts": {
    "pre-autoload-dump": ["Google\\Task\\Composer::cleanup"]
},
"extra": {
    "google/apiclient-services": ["Drive"]
}

For a personal account without Workspace, an OAuth client with a refresh token works the same way (setClientId, setClientSecret, refreshToken instead of setAuthConfig), without teamDriveId.

The Drive adapter throws when asked to list a folder that does not exist yet, and Drive's search can take a moment to see a folder just created. snapshot:backup creates {path}/daily and {path}/weekly on a fresh target itself, and every command reads a missing folder as empty, so a new project needs no folders made by hand.

NAS over sftp (composer require league/flysystem-sftp-v3):

'nas' => [
    'driver' => 'sftp',
    'host' => env('NAS_SFTP_HOST'),
    'port' => (int) env('NAS_SFTP_PORT', 22),
    'username' => env('NAS_SFTP_USERNAME'),
    'privateKey' => env('NAS_SFTP_PRIVATE_KEY_PATH'),
    'root' => env('NAS_SFTP_ROOT'),           // e.g. /volume1/backups/myapp-production
    'throw' => true,
],

A mounted volume is a plain 'driver' => 'local' disk with 'root' on the mount.

Schedule

// routes/console.php
use Illuminate\Support\Facades\Schedule;

Schedule::command('snapshot:create', ['--compress'])->dailyAt('04:00');
Schedule::command('snapshot:backup')->dailyAt('04:03')->withoutOverlapping();
Schedule::command('snapshot:prune')->dailyAt('04:05');
Schedule::command('snapshot:backup-check')->hourly();

// Optional: prove the off-site copy restores, e.g. monthly, off-peak.
Schedule::command('snapshot:drill')->monthlyOn(1, '05:00');

snapshot:prune replaces snapshot:cleanup --keep=N. Drop the old line when you add it. If the dump takes longer than a couple of minutes, the 04:03 backup does not see it yet and it goes up a day late. In that case chain the backup onto the create (->then(fn () => Artisan::call('snapshot:backup'))).

Alerting

snapshot:backup-check reads the newest snapshot under {path}/daily/ on each target and dispatches one event per target. "Newest" here, in snapshot:drill and in the weekly pick goes by when the snapshot was taken: the Y-m-d_H-i-s stamp spatie puts in the name (read in app.timezone), or the file's modified time when the name has none. A target's modified time is only the upload time. Both implement SnapshotBackupChecked, so listen on that interface to get both. (A listener on their abstract parent class never fires: Laravel matches an event's interfaces, not its parent classes.)

Event	When	Properties
`SnapshotBackupHealthy`	newest copy within `stale_after_hours`	`disk`, `newest`, `newestAt`, `ageSeconds`, `ageHours()`, `staleAfterHours`
`SnapshotBackupStale`	older than that, no copy at all, or the target could not be read	the same, with `newest`/`ageSeconds` null when there is no copy, and `error` when unreadable

use Phattarachai\DbSnapshotSyncLaravel\Events\SnapshotBackupStale;

Event::listen(SnapshotBackupStale::class, function (SnapshotBackupStale $event): void {
    Log::critical('Off-site DB backup is stale', [
        'disk' => $event->disk,
        'newest' => $event->newest,
        'age_hours' => $event->ageHours(),
        'error' => $event->error,
    ]);
});

Restore drill

snapshot:drill downloads the newest daily copy from a target disk (--disk, default the first in backup.disks), so it proves the off-site copy and not the local one. It then:

creates <database>_restore_drill on the live connection's server (--connection, default the default connection), dropping a leftover from a killed run first;
loads the copy into it, with psql in one transaction on PostgreSQL (see How a PostgreSQL snapshot is loaded) and spatie's streamed loader on MySQL/MariaDB;
compares exact per-table row counts with the live database and prints the elapsed time and the tables that differ;
drops the scratch database in a finally, and dispatches SnapshotDrillCompleted with the DrillResult.

The live database is only read (row counts). Every write goes to the scratch database, and the drill refuses to load if the scratch connection does not resolve to it. The data never leaves the box, so the drill is meant to run on production. Row counts are taken now, after the snapshot, so small deltas are normal. The drill fails when a live table is missing from the restore, or has rows live but restored empty (tables in dump.exclude_table_data excepted), or when the download, the engine check or the load fails.

It needs:

the privilege to create a database. PostgreSQL: ALTER ROLE <app_user> CREATEDB;. MySQL: GRANT ALL ON `<database>_restore_drill`.* TO '<app_user>'@'<host>';. Extensions the dump creates must be ones that user may create (trusted extensions, on PostgreSQL 13+).
disk space for a second copy of the database on the DB server, plus the compressed download under storage/app/db-snapshot-sync-drill/ (deleted afterwards).
an off-peak slot. count(*) on every live table is a full scan of each.

Testing

composer test

License

MIT. See LICENSE.md.

ผู้พัฒนา

พัฒนาและดูแลโดย บริษัท ภัทรชัย อาร์ทิซาน จำกัด (Phattarachai Artisan) บริษัทที่ปรึกษาและพัฒนาเว็บ ที่เรียนรู้และแบ่งปันกับชุมชน Laravel แพ็กเกจนี้แบ่งปันให้ชุมชนนำไปใช้และต่อยอดได้อย่างอิสระ

ดูแพ็กเกจอื่นของเราได้ที่ phattarachai.dev/open-source และติดต่อเราได้ที่ phattarachai.dev

phattarachai / db-snapshot-sync-laravel

Package info

Security

Statistics