README

Laravel package for incremental PostgreSQL database synchronization from remote servers via SSH tunnel.

Supports incremental sync, full refresh (DROP + CREATE), automatic backups, foreign key dependency resolution, and self-referencing table handling.

Requirements

• PHP 8.2+
• Laravel 10, 11, or 12
• PostgreSQL
• pg_dump and psql CLI tools available on the local machine
• artemyurov/laravel-autossh-tunnel for SSH tunnel management

Installation

composer require artemyurov/laravel-incremental-db-sync --dev

Publish the configuration file:

php artisan vendor:publish --tag=db-sync-config

Configuration

The configuration file config/db-sync.php defines sync connections:

return [
    'default' => env('DB_SYNC_CONNECTION', 'production'),
    'batch_size' => env('DB_SYNC_BATCH_SIZE', 10000),

    'backup' => [
        'path' => env('DB_SYNC_BACKUP_PATH', storage_path('app/db-sync/backups')),
        'keep_last' => env('DB_SYNC_BACKUP_KEEP_LAST', 5),
    ],

    'connections' => [
        'production' => [
            'tunnel' => env('DB_SYNC_TUNNEL', 'remote_db'),
            'source' => [
                'driver' => 'pgsql',
                'database' => env('DB_SYNC_REMOTE_DATABASE'),
                'username' => env('DB_SYNC_REMOTE_USERNAME'),
                'password' => env('DB_SYNC_REMOTE_PASSWORD'),
            ],
            'target' => env('DB_SYNC_TARGET_CONNECTION', 'pgsql'),
            'excluded_tables' => [
                'telescope_entries',
                'sessions',
                'cache',
                'jobs',
            ],
        ],
    ],
];

Each connection defines:

You can define multiple connections (e.g. production, staging) and switch between them using the --sync-connection option.

Commands

db-sync:pull — Incremental Synchronization

Analyzes differences between remote and local databases, then applies only the changes (DELETE + UPSERT).

php artisan db-sync:pull

Features:

• Compares record counts and updated_at timestamps to detect changes
• Automatically rebuilds tables with changed structure
• Resolves foreign key dependencies for correct sync order
• CASCADE RECHECK: re-syncs child tables after parent deletions
• Syncs views and resets sequences

Options:

db-sync:clone — Full Clone

Drops all tables and recreates them from the remote server. Use this for a clean start.

php artisan db-sync:clone

Options:

db-sync:restore — Restore from Backup

Restore local database from a previously created backup.

# List available backups
php artisan db-sync:restore --list

# Interactive backup selection
php artisan db-sync:restore

# Restore specific backup file
php artisan db-sync:restore backup_2025-01-15_120000.sql.gz

Options:

How It Works

Incremental Sync (db-sync:pull)

1. Opens SSH tunnel to the remote server
2. Creates a local backup
3. Analyzes each table: compares row counts, max IDs, and updated_at timestamps
4. Detects tables with changed structure (columns added/removed/modified)
5. Rebuilds changed tables (DROP + CREATE + import data)
6. For unchanged structure: runs DELETE phase (removes records missing from remote), then UPSERT phase (inserts new / updates modified records)
7. CASCADE RECHECK: if parent table had deletions, re-checks child tables
8. Syncs views and resets auto-increment sequences

Full Clone (db-sync:clone)

1. Opens SSH tunnel to the remote server
2. Creates a local backup
3. Dumps schema from remote using pg_dump
4. Drops all local tables and recreates from dump
5. Copies all data from remote in batches
6. Resets sequences

Foreign Key Handling

The package builds a dependency graph from foreign key constraints and uses topological sorting to determine the correct order for:

• Inserts: parent tables first (parents-first order)
• Deletes: child tables first (children-first order)

Self-referencing tables (e.g. categories with parent_id) are handled via recursive CTEs.

Architecture

The package uses an adapter pattern for database operations:

DatabaseAdapterInterface
└── PgsqlAdapter          # PostgreSQL implementation

Key services:

Docker & DDEV

This package requires SSH tunnels to work. For Docker and DDEV setup (SSH agent forwarding, autossh installation), see the Docker & DDEV section in laravel-autossh-tunnel documentation.

Add postgresql-client to your Dockerfile for schema operations (pg_dump, psql).

Important: The pg_dump version must be >= the PostgreSQL server version. Debian base images ship with older versions (e.g. Bookworm includes PG 15), so you may need the official PostgreSQL APT repository:

# If the Debian pg_dump version matches your server — this is enough:
RUN apt-get update && apt-get install -y postgresql-client

# If the server is newer (e.g. PG 18 on Bookworm) — add the pgdg repository:
RUN curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc -o /usr/share/keyrings/pgdg.asc \
    && echo "deb [signed-by=/usr/share/keyrings/pgdg.asc] http://apt.postgresql.org/pub/repos/apt bookworm-pgdg main" \
       > /etc/apt/sources.list.d/pgdg.list \
    && apt-get update && apt-get install -y --no-install-recommends postgresql-client-18

Adding Database Drivers

To support a new database (e.g. MySQL), implement DatabaseAdapterInterface and register it in BaseDbSyncCommand::resolveAdapter().

License

MIT