weslinkde/laravel-postgres-tools

Some handy helper function for PostgresSQL Databases

v0.5.5 2025-10-13 07:34 UTC

Requires

• php: ^8.1
• illuminate/contracts: ^10.0|^11.0|^12.0
• laravel/prompts: ^0.1.15|^0.2|^0.3
• spatie/laravel-db-snapshots: ^2.6
• spatie/laravel-package-tools: ^1.19.0

Requires (Dev)

• larastan/larastan: ^2.0
• laravel/pint: ^1.0
• nunomaduro/collision: ^7.0|^8.0
• orchestra/testbench: ^8.0|^9.0|^10.0
• pestphp/pest: ^2.0|^3.0
• pestphp/pest-plugin-arch: ^2.0|^3.0
• pestphp/pest-plugin-laravel: ^2.0|^3.0
• phpstan/extension-installer: ^1.4
• phpstan/phpstan-deprecation-rules: ^1.0|^2.0
• phpstan/phpstan-phpunit: ^1.0|^2.0
• spatie/laravel-ray: ^1.39

MIT

• Dominik Lenz
• Jan Hallekamp

• laravel
• Weslinkde
• laravel-postgres-tools

README

This package provides some tools to make working with postgres easier. It comes with a command to create a snapshot of your database and a command to restore a snapshot. We created this package to handle huge databases (+16GB). Big thanks to Spatie for their great packages, especially the laravel-db-snapshots package, which we use to create snapshots. You can also create new databases, drop existing ones or clone them.

Installation

You can install the package via composer:

composer require weslinkde/laravel-postgres-tools

You can publish the config file with:

php artisan vendor:publish --tag="laravel-postgres-tools-config"

This is the contents of the published config file:

return [
    /*
     * The name of the disk on which the snapshots are stored.
     */
    'disk' => 'snapshots',

    /*
     * The connection to be used to create snapshots. Set this to null
     * to use the default configured in `config/databases.php`
     */
    'default_connection' => 'pgsql',

    /*
     * The directory where temporary files will be stored.
     */
    'temporary_directory_path' => storage_path('app/laravel-db-snapshots/temp'),

    /*
     * Only these tables will be included in the snapshot. Set to `null` to include all tables.
     *
     * Default: `null`
     */
    'tables' => null,

    /*
     * All tables will be included in the snapshot expect this tables. Set to `null` to include all tables.
     *
     * Default: `null`
     */
    'exclude' => null,

    /*
     * These are the options that will be passed to `pg_dump`. See `man pg_dump` for more information.
     */
    'addExtraOption' => env('PG_DUMP_OPTIONS', '--no-owner --no-acl --no-privileges -Z 3 -Fc'),

    /*
     * The number of jobs pg_restore should use to restore the snapshot.
     */
    'jobs' => env('PG_RESTORE_JOBS', 4),
];

Performance Tuning for Large Databases

For databases larger than 16GB, consider these optimizations:

Compression Level (-Z)

• -Z 9 (Maximum): Slow, but smallest files (~5-10% smaller than Z1)
• -Z 3 (Recommended): 3-5x faster than Z9 with minimal size difference
• -Z 1 (Fast): Fastest compression, good for very large DBs

# Set in your .env file
PG_DUMP_OPTIONS="--no-owner --no-acl --no-privileges -Z 1 -Fc"

Parallel Restore Jobs

The package supports parallel restoration using pg_restore --jobs:

# Set in your .env file
PG_RESTORE_JOBS=8  # Use CPU cores - 2 for large databases

Recommended values:

• Small DBs (<1GB): 1-2 jobs
• Medium DBs (1-10GB): 4 jobs
• Large DBs (10GB+): 4-8 jobs

Storage Considerations

For cloud storage (S3, etc.), the package automatically streams files to local temp directory during restore to avoid memory issues.

Usage

To create a snapshot (which is just a dump from the database), run:

php artisan weslink:snapshot:create my-first-dump

Giving your snapshot a name is optional. If you don't pass a name, the current date time will be used:

# Creates a snapshot named something like `2017-03-17 14:31`
php artisan weslink:snapshot:create

Maybe you only want to snapshot a couple of tables. You can do this by passing the --table multiple times or as a comma separated list:

# Both commands create a snapshot containing only the posts and users tables:
php artisan weslink:snapshot:create --table=posts,users
php artisan weslink:snapshot:create --table=posts --table=users

You may want to exclude some tables from snapshot. You can do this by passing the --exclude multiple times or as a comma separated list:

# create snapshot from all tables excluding the users and posts
php artisan weslink:snapshot:create --exclude=posts,users
php artisan weslink:snapshot:create --exclude=posts --exclude=users

Note: if you pass --table and --exclude in the same time it will use --table to create the snapshot, and it'd ignore the --exclude

After you've made some changes to the database, you can create another snapshot:

php artisan weslink:snapshot:create my-second-dump

To load a previous dump issue, this command:

php artisan weslink:snapshot:load my-first-dump

To load a previous dump to another DB connection (but the driver has to be pgsql):

php artisan weslink:snapshot:load my-first-dump --connection=connectionName

A dump can be deleted with:

php artisan weslink:snapshot:delete my-first-dump

You can create new databases with:

php artisan weslink:database:create my-new-database

And you can drop existing databases with:

php artisan weslink:database:drop my-old-database

Note: This action is irreversible. It will drop the database, on production it will ask you for your confirmation.

It is also possible to clone an existing database with:

php artisan weslink:database:clone my-old-database my-new-database

It will create a new database for you. If the new database already exists, it won't do anything.

Events

For convenience, we're using the events from Spaties package. There are several events fired that can be used to perform some logic of your own:

• Spatie\DbSnapshots\Events\CreatingSnapshot: will be fired before a snapshot is created
• Spatie\DbSnapshots\Events\CreatedSnapshot: will be fired after a snapshot has been created
• Spatie\DbSnapshots\Events\LoadingSnapshot: will be fired before a snapshot is loaded
• Spatie\DbSnapshots\Events\LoadedSnapshot: will be fired after a snapshot has been loaded
• Spatie\DbSnapshots\Events\DeletingSnapshot: will be fired before a snapshot is deleted
• Spatie\DbSnapshots\Events\DeletedSnapshot: will be fired after a snapshot has been deleted

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security Vulnerabilities

Please review our security policy on how to report security vulnerabilities.

Credits

• Dominik Lenz
• All Contributors

License

The MIT License (MIT). Please see License File for more information.