Re: Would you add a --dry-run to pg_restore?

> On Aug 2, 2017, at 9:02 AM, Edmundo Robles <edmundo(at)sw-argos(dot)com> wrote:
>
> I mean, to verify the integrity of backup i do:
>
> gunzip -c backup_yesterday.gz | pg_restore -d my_database && echo "backup_yesterday is OK"
>
> but my_database's size, uncompresed, is too big more than 15G and sometimes i have no space to restore it, so always i must declutter my disk first.
>
> By the way i have programmed backups on many databases so, i must check the integrity one by one deleting the database to avoid disk space issues. By the way the restores takes too long time an average of 1 hour by backup.
>
> Will be great to have a dry run option, because the time to verify reduces a lot and will save space on disk, because just execute with no write to disk.

If the gunzip completes successfully then the backups weren't corrupted and the disk is readable. They're very likely to be "good" unless you have a systematic problem with your backup script.

You could then run that data through pg_restore, redirecting the output to /dev/null, to check that the compressed file actually came from pg_dump. (gunzip backup_yesterday.gz | pg_restore >/dev/null)

The only level of checking you could do beyond that would be to ensure that the database was internally self-consistent and so truly restorable - and to do that, you'll need to restore it into a real database.

You could do an intermediate check by restoring into a real database with --schema-only, I guess.

As an aside, pg_dump with custom format already compresses the dump with gzip, so the additional gzip step may be redundant. You can set pg_dump's compression level with -Z.

Cheers,
Steve

>
> if pg_restore have a dry option i will do:
>
> (gunzip -c mydata.gz | pg_restore -d mydata --dry && echo "mydata0 is ok")&
> (gunzip -c my_other_data.gz | pg_restore -d my_other_data --dry && echo "my_other_data is ok")&
> (gunzip -c my_another_data.gz | pg_restore -d my_another_data --dry && echo "my_another_data is ok")&
> wait
>
>
> and the time to verify only will take 1 hour instead of 3 hours.
>