Postgres 16 Backup Restore Failed

[luchfirearmland]

Hi! Was trying out this package thinking this will make my local development easier by just restoring the backups from production. When I did try, this error popped out. Does this mean that we are not using "pg_restore" by default?

[stefanzweifel]

No, the package does not use the pg_restore binary.
It uses the psql-binary.

I personally don't use Postgres and don't have any experience with it, so I don't know right now, what the right approach here would even be.

We could obviously update the code to use the pg_restore-binary, but I would need some more information from you for this:

• The error message mentions "the input is a PostgreSQL custom-format dump". Are you using spatie/laravel-backup to create the backups?
• Have you set any special configuration regarding how you create a backup?
• Is the Postgres version on your production and local machine the same?

[luchtech]

Hey! The Postgres version I used is v16. It's a Docker setup from local to production. I stored the Postgres backup to S3 then restored it locally via S3 too. I've just finished it earlier. Here's my Action class.

RestoreDatabaseBackupAction.php

<?php

namespace App\Actions\BackupRestore;

use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\Process;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use Lorisleiva\Actions\Concerns\AsAction;
use ZipArchive;
use function Laravel\Prompts\error;
use function Laravel\Prompts\select;
use function Laravel\Prompts\info;

class RestoreDatabaseBackupAction
{
    use AsAction;

    public function handle()
    {}

    public string $commandSignature = 'db:restore';

    public function asCommand(Command $command): void
    {
        // Step 1: Choose from the available backups
        $disk = Arr::first(config('backup.backup.destination.disks'));
        $backups = Storage::disk($disk)->allFiles();
        $local_backups = Storage::disk('local-backup');
        $backup = select('Which backup do you want to restore?', $backups);
        info("Backup to restore: $backup");

        // Step 2: Download the backup zip file
        info("Downloading backup: $backup");
        $contents = Storage::disk($disk)->get($backup);
        $local_backups->put($backup, $contents);
        info("Downloaded backup: $backup");

        // Step 3: Extract zip file
        info("Extracting backup: $backup");
        $this->unzip($backup);
        info("Extracted backup: $backup");

        // Step 4: Execute "pg_restore" on files that end in ".sql"
        if ($sql = collect($local_backups->allFiles('restore'))
            ->filter(fn ($file) => Str::endsWith($file, '.sql'))
            ->first()
        ) {
            info("Importing dump file: $sql");
            $this->restore($local_backups->path($sql));
            info("Imported dump file: $sql");
        }
        else {
            error('No SQL file found.');
        }

        // Step 5: Delete backup file
        $local_backups->delete($backup);
        $local_backups->deleteDirectory('restore');
    }

    public function unzip(string $backup)
    {
        $password = config('backup.backup.password');
        $destination = 'restore';
        $disk = Storage::disk('local-backup');

        // Delete "restore" directory if existing
        if ($disk->exists($destination)) {
            $disk->deleteDirectory($destination);
        }

        // Create the "restore" directory
        $disk->makeDirectory($destination);

        $zip = new ZipArchive();

        if ($zip->open($disk->path($backup))) {
            if ($password && !$zip->setPassword($password)) {
                $zip->close();
                return false;
            }

            if (!$zip->extractTo($disk->path($destination))) {
                $zip->close();
                return false;
            }

            $zip->close();

            return true;
        }

        return false;
    }

    public function restore(string $sql_path): void
    {
        $command = sprintf(
            'pg_restore --verbose --clean --no-acl --no-owner --no-owner -h %s -p %s -U %s -d %s %s',
            escapeshellarg(config('database.connections.pgsql.host')),
            escapeshellarg(config('database.connections.pgsql.port')),
            escapeshellarg(config('database.connections.pgsql.username')),
            escapeshellarg(config('database.connections.pgsql.database')),
            escapeshellarg($sql_path),
        );

        Process::forever()
            ->env(['PGPASSWORD' => config('database.connections.pgsql.password')])
            ->run($command);
    }
}

[luchfirearmland]

No customizations done on my end. Just followed whatever's on the Spatie Backup docs re Postgres.

[stefanzweifel]

Thanks for the feedback. I think we can then probably just use pg_restore or at least make the binary used in Postgres configurable.

I just checked and I've used Postgres 15 to test the package locally. So that might be the problem. 😂
And unfortunately #6 is still open. I hope I can fix this as well soon.

---

But great to hear that you could solve this with your own Action here.