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

I imagine pg_restore can execute the instructions on dump but don't
write on disk. just like David said: "tell me what is going to happen
but don't actually do it"

Regards.

On Wed, Aug 2, 2017 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Edmundo Robles <edmundo(at)sw-argos(dot)com> writes:
> > 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.
>
> > 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.
>
> What do you imagine a dry run option would do?
>
> If you just want to see if the file contains obvious corruption,
> you could do
>
> pg_restore file >/dev/null
>
> and see if it prints any complaints on stderr. If you want to have
> confidence that the file would actually restore (and that there aren't
> e.g. unique-index violations or foreign-key violations in the data),
> I could imagine a mode where pg_restore wraps its output in "begin" and
> "rollback". But that's not going to save any disk space, or time,
> compared to doing a normal restore into a scratch database.
>
> I can't think of any intermediate levels of verification that wouldn't
> involve a huge amount of work to implement ... and they'd be unlikely
> to catch interesting problems in practice. For instance, I doubt that
> syntax-checking but not executing the SQL coming out of pg_restore would
> be worth the trouble. If an archive is corrupt enough that it contains
> bad SQL, it probably has problems that pg_restore would notice anyway.
> Most of the restore failures that we hear about in practice would not be
> detectable without actually executing the commands, because they involve
> problems like issuing commands in the wrong order.
>
> regards, tom lane
>

--