Thread: [GENERAL] Would you add a --dry-run to pg_restore?

Edmundo Robles <edmundo@sw-argos.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

> On Aug 2, 2017, at 9:02 AM, Edmundo Robles <edmundo@sw-argos.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
alwaysi 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,
becausejust  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
tobe "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
actuallycame 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
andso 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.
>

On Wed, Aug 02, 2017 at 12:10:37PM -0500, Edmundo Robles wrote:

> 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"

In fact, this already exists:

    pg_restore --file=commands.sql your-backup

Then read commands.sql.

It will tell you what is going to happen but not actually do it.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Wed, Aug 2, 2017 at 7:10 PM, Edmundo Robles <edmundo@sw-argos.com> wrote:
>
> I  imagine   pg_restore can  execute  the instructions on dump but  don't  write on disk.   just like David said:
"tellme what is going to happen but don't actually do it" 


IIRC pg_restore does not execute SQL fully. It just passes the
commands to the server when in text mode, like psql, and builds some
commands and send them to the server to execute when in custom/tar
mode. I doubt it has the ability to validate the contents of the dump.

>> Edmundo Robles <edmundo@sw-argos.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"

I also think if he is piping the dump must be text mode, I seem to
recall custom format needs seekable files, but not too sure about tar,
it should not. In this case, as previously suggested, a simple gunzip
-t is enough to verify backup file integrity, but checking internal
correctness is very difficult ( as it may even depend on server
configuration, i.e., needing some predefined users / locales /
encodings ).

Francisco Olarte.

On 02/08/17, Steve Atkins (steve@blighty.com) wrote:
> > On Aug 2, 2017, at 9:02 AM, Edmundo Robles <edmundo@sw-argos.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.
...

> 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)

A couple of extra steps you can add to avoid a full restore (which is
best) is to do a file hash check as part of the verification, and do
something like add a token to the database just before dumping, then
verify that. We do something like this:

rory:~/db$ gpg -d dump_filename.sqlc.gpg | \
           pg_restore -Fc --data-only --schema audit | \
           grep -A 1 "COPY audit"

output >
    COPY audit (tdate) FROM stdin;
    2017-04-25

Cheers
Rory

On Wed, Aug 2, 2017 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Edmundo Robles <edmundo@sw-argos.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.

The vast majority of my restore issues are dependency problems (for
example, postgis extension not being present).  A distant second place
would be pg_restore's inability to do things in the proper order or
gaps in the dump feature itself (for example, a cast between two built
in types, at least back in the day).

A good reasonable test for all of those cases with the current tools
is to do a schema only restore (which should not take long in most
cases).   If you get past that step, there is an exceptionally high
probability that the restore will succeed sans some controllable
factors like running out of space.

There are some rare known considerations that could a data load to
fail.  For example, a unique index on floating point can dump but not
load if two binary differentiated values render to the same string.
I've never seen this in practice however.  So I'd argue to just use
that (schema only) feature for pre-load verification if you're
paranoid.

merlin