Thread: how robust are custom dumps?

how robust are custom dumps?

From
Willy-Bas Loos
Date:
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?

Cheers,

WBL

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: how robust are custom dumps?

From
Andreas Kretschmer
Date:
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°

Re: how robust are custom dumps?

From
Vick Khera
Date:
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.

Re: how robust are custom dumps?

From
Thom Brown
Date:
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

Re: how robust are custom dumps?

From
Willy-Bas Loos
Date:
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.

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

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?

Cheers,

WB

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: how robust are custom dumps?

From
Magnus Hagander
Date:
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/

Re: how robust are custom dumps?

From
Willy-Bas Loos
Date:
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.

WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: how robust are custom dumps?

From
Guillaume Lelarge
Date:
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


Re: how robust are custom dumps?

From
Willy-Bas Loos
Date:
great stuff!
was that already in it?
i'd plea for adding recognition of gzipped data too..

cheers,

WBL

On Wed, Apr 25, 2012 at 11:05 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
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




--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth