Thread: how robust are custom dumps?

Willy-Bas Loos <willybas@gmail.com> wrote:

> Hi,
>
> Some 6 years ago, i had a bad experience with a custom dump. It wouldn't
> restore and my data was lost.
> I was a beginner then, and working under windows, and i wasn't on the mailing
> list yet.
> It was no critical data, we could build the database again, which was then
> easier than figuring out what exactly went wrong with the backup.
>
> Since then i have refused to trust custom dumps.
> But they do provide some very nice functionality, like restoring a single
> table.
>
> So my question is: what is your advice on custom dumps? Can i bet my life on
> them?

I think so, yes.

Early versions contains some errors with the correct order of objects
(dumping a view before the table -> error whilst restore ...)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Tue, Apr 24, 2012 at 11:17 AM, Willy-Bas Loos <willybas@gmail.com> wrote:
> So my question is: what is your advice on custom dumps? Can i bet my life on
> them?

Yes, I would and I do.

I've never had a failure as you describe, going back all the way to
Postgres 6.5-ish times.  Back then I did have full DB corruption every
so often, but the dumps always restored just fine on rebuild.

On 24 April 2012 16:17, Willy-Bas Loos <willybas@gmail.com> wrote:
> Hi,
>
> Some 6 years ago, i had a bad experience with a custom dump. It wouldn't
> restore and my data was lost.

What was the experience?  Is it possible you had specified a
compression level without the format set to custom?  That would result
in a plain text output within a gzip file, which would then error out
if you tried to restore it with pg_restore, but would be perfectly
valid if you passed the uncompressed output directly into psql.

How many times had you experienced the problem at the time?  Was is
repeatedly or just the one time?

--
Thom

On Wed, Apr 25, 2012 at 09:42, Willy-Bas Loos <willybas@gmail.com> wrote:
> On Tue, Apr 24, 2012 at 10:04 PM, Thom Brown <thom@linux.com> wrote:
>>
>> What was the experience?  Is it possible you had specified a
>> compression level without the format set to custom?  That would result
>> in a plain text output within a gzip file, which would then error out
>> if you tried to restore it with pg_restore, but would be perfectly
>> valid if you passed the uncompressed output directly into psql.
>
>
> yes, probably. I remember that it was a binary file, but i didn't know about
> the possibility of gzip in pg_dump.
> Possibly the 2 GB size limit for a FAT partition was exceeded, but that
> would have resulted in an error, so i would have known.

We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as
well, IIRC, specifically on Win32. Maybe you were hit by that one..


> i think it's time to restore my trust in the custom dumps. :)

Yes.


> i do have one suggestion.
> pg_restore only gives a user this feedback, when he makes this
> mistake:"pg_restore: [archiver] input file does not appear to be a valid
> archive".
>
> Would it be feasible for pg_restore to detect that it is a different pg_dump
> format and inform the user about it?

The main one you'd want to detect is plain I think - and I don't know
if we can reliably detect that. It could be just a generic textfile,
after all - how would we know the difference?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Wed, 2012-04-25 at 10:40 +0200, Willy-Bas Loos wrote:
> On Wed, Apr 25, 2012 at 9:51 AM, Magnus Hagander <magnus@hagander.net>wrote:
>
> > We used to have a bug/lackoffeature in pg_dump at the 2GB boundary as
> > well, IIRC, specifically on Win32. Maybe you were hit by that one..
>
> Yes, possibly. I didn't even know how to make a compressed plain dump, but
> that doesn't really plea my case :/
>
>
> > > i do have one suggestion.
> > > pg_restore only gives a user this feedback, when he makes this
> > > mistake:"pg_restore: [archiver] input file does not appear to be a valid
> > > archive".
> > >
> > > Would it be feasible for pg_restore to detect that it is a different
> > pg_dump
> > > format and inform the user about it?
> >
> >
> The main one you'd want to detect is plain I think - and I don't know
> > if we can reliably detect that. It could be just a generic textfile,
> > after all - how would we know the difference?
> >
>
>
>
> Well, on linux you could make pg_dump run /usr/bin/file on the file to see
> what kind it is. If it is gzipped, suggest that it might be a gzipped plain
> dump, if it is plain text, suggest that it might be a plain  dump (etc,
> also bzip2). That's all.
> You don't have to be sure that it is valid, just say a bit more than "does
> not appear to be a valid archive". Help a user in a bad situation.
>
> Only, i know that postgres runs on many platforms, so you probably can't
> run /usr/bin/file on all of those (or might not be installed on linux
> machine). So it probably should be part of pg_restore itself.
>

pg_restore will do so for plain backups on 9.2:

$ pg_dump b1 > b1.dump
$ pg_restore -d b2 b1.dump
pg_restore: [archiver] input file appears to be a text format dump.
Please use psql.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com