Horton

Thread

  1. disk backups

    mikeo <mikeo@spectrumtelecorp.com> — 2000-06-28T16:00:46Z

    hi, 
  would someone be so kind as to comment on restoring a database from
file backups?  for instance, if i backed up the postgres files, once 
the database was down, and there was a subsequent problem, a file lost
or a disk crash maybe, would restoring the disk from the filebackup 
cause any consistency problems with postgres?  
please assume that all the files on the disk were postgres files 
and the backup reflected that.

how about trying to restore just one or two files after an accidental 
deletion, maybe?  again from the disk file backup instead of from a
pg_dump.  

how about using those same file backups to move a database to another,
similar setup machine?  

thanks,
    mikeo

  2. Re: disk backups

    Stephen Lawrence <logart@mail.com> — 2000-06-28T16:13:18Z

    I think the way most people do that is to have pg_dump or pg_dumpall make a
dump of the databases into the postgres directory each nite. That way, when
you do a full disk backup, the database dumps would be backed up as well.

----- Original Message -----
From: "mikeo" <mikeo@spectrumtelecorp.com>
Subject: [GENERAL] disk backups


> hi,
>   would someone be so kind as to comment on restoring a database from
> file backups?

  3. Re: disk backups

    Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-06-29T13:23:02Z

    Stephen Lawrence Jr. wrote:
> 
> I think the way most people do that is to have pg_dump or pg_dumpall make a
> dump of the databases into the postgres directory each nite. That way, when
> you do a full disk backup, the database dumps would be backed up as well.

Is there a better way? Here pg_dumping the DB takes over half an hour
(mainly because pg_dump chews all available memory). It would be nicer
if we knew that tarring it up would work also...

> ----- Original Message -----
> From: "mikeo" <mikeo@spectrumtelecorp.com>
> Subject: [GENERAL] disk backups
> 
> > hi,
> >   would someone be so kind as to comment on restoring a database from
> > file backups?

-- 
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

  4. Re: disk backups

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-29T15:03:11Z

    Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
> Is there a better way? Here pg_dumping the DB takes over half an hour
> (mainly because pg_dump chews all available memory).

pg_dump shouldn't be a performance hog if you are using the default
COPY-based style of data export.  I'd only expect memory problems
if you are using INSERT-based export (-d or -D switch to pg_dump).
For now, the answer is "don't do that" ... at least not on big tables.

This could be fixed in either of two ways:

1. recode pg_dump to use DECLARE CURSOR and FETCH to grab table contents
in reasonable-size chunks (instead of with an all-at-once SELECT);

2. add an API to libpq that allows a select result to be retrieved
on-the-fly rather than accumulating it in libpq's memory.

The second is more work but would be more widely useful.

However, it's not been much of a priority, since insert-based data
export is so slow to reload that no sensible person uses it for
big tables anyway ;-)

			regards, tom lane

  5. Re: disk backups

    Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-06-30T15:15:58Z

    Tom Lane wrote:
> 
> Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
> > Is there a better way? Here pg_dumping the DB takes over half an hour
> > (mainly because pg_dump chews all available memory).
> 
> pg_dump shouldn't be a performance hog if you are using the default
> COPY-based style of data export.  I'd only expect memory problems
> if you are using INSERT-based export (-d or -D switch to pg_dump).
> For now, the answer is "don't do that" ... at least not on big tables.

Aha! Thanks for that! Last time I asked here nobody answered...
So it only happens with an INSERT based export, didn't know
that (though I can't see why there would be a difference...)

Yes, we are using -D, mainly because we've had "issues" with
the COPY based export, ie, it won't read the resulting file
back. Admittedly this was a while ago now and I havn't checked 
since.

The data in question was a long text field containing (long)
snippets of HTML. Quotes, backslashes, tabs, newlines, etc,
the works. The insert style dump never had a problem...

> This could be fixed in either of two ways:
> 
> 1. recode pg_dump to use DECLARE CURSOR and FETCH to grab table contents
> in reasonable-size chunks (instead of with an all-at-once SELECT);
> 
> 2. add an API to libpq that allows a select result to be retrieved
> on-the-fly rather than accumulating it in libpq's memory.
> 
> The second is more work but would be more widely useful.

I was thinking to write my own version of pg_dump that would
do that but also allow specifying of ordering constraint, ie,
clustering. Maybe it would be better to just switch to the 
other output format...

> 
> However, it's not been much of a priority, since insert-based data
> export is so slow to reload that no sensible person uses it for
> big tables anyway ;-)

Well, there's slow and there's something that works. But thanks
for clearing this up because it's not mentioned anywhere...
-- 
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

  6. Re: disk backups

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-30T16:21:10Z

    Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
> Tom Lane wrote:
>> pg_dump shouldn't be a performance hog if you are using the default
>> COPY-based style of data export.  I'd only expect memory problems
>> if you are using INSERT-based export (-d or -D switch to pg_dump).

> Aha! Thanks for that! Last time I asked here nobody answered...
> So it only happens with an INSERT based export, didn't know
> that (though I can't see why there would be a difference...)

COPY uses a streaming style of output.  To generate INSERT commands,
pg_dump first does a "SELECT * FROM table", and that runs into libpq's
suck-the-whole-result-set-into-memory behavior.  See nearby thread
titled "Large Tables(>1 Gb)".

> Yes, we are using -D, mainly because we've had "issues" with
> the COPY based export, ie, it won't read the resulting file
> back. Admittedly this was a while ago now and I havn't checked 
> since.

IIRC that's a long-since-fixed bug.  If not, file a bug report so
we can fix whatever's still wrong...

> I was thinking to write my own version of pg_dump that would
> do that but also allow specifying of ordering constraint, ie,
> clustering. Maybe it would be better to just switch to the 
> other output format...

Philip Warner needs alpha testers for his new version of pg_dump ;-).
Unfortunately I think he's only been talking about it on pghackers
so far.

			regards, tom lane

  7. Re: disk backups

    Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-07-01T13:34:57Z

    Tom Lane wrote:
> COPY uses a streaming style of output.  To generate INSERT commands,
> pg_dump first does a "SELECT * FROM table", and that runs into libpq's
> suck-the-whole-result-set-into-memory behavior.  See nearby thread
> titled "Large Tables(>1 Gb)".

Hmm, any reason why pg_dump couldn't do a 

SELECT * from table where 1 = 0

to get the column names and then do a COPY and reformat
the output...

> > Yes, we are using -D, mainly because we've had "issues" with
> > the COPY based export, ie, it won't read the resulting file
> > back. Admittedly this was a while ago now and I havn't checked
> > since.
> 
> IIRC that's a long-since-fixed bug.  If not, file a bug report so
> we can fix whatever's still wrong...

It may be fixed, but we're still running an old version of
postgres, so it may not be fixed in the version we're using...

> Philip Warner needs alpha testers for his new version of pg_dump ;-).
> Unfortunately I think he's only been talking about it on pghackers
> so far.

What versions does it work on?

-- 
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

  8. Re: disk backups

    Philip Warner <pjw@rhyme.com.au> — 2000-07-02T02:26:11Z

    At 23:34 1/07/00 +1000, Martijn van Oosterhout wrote:
>
>> Philip Warner needs alpha testers for his new version of pg_dump ;-).
>> Unfortunately I think he's only been talking about it on pghackers
>> so far.
>
>What versions does it work on?
>

6.5.x and 7.0.x.

Which version are you running? 


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

  9. Re: disk backups

    Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-07-02T13:36:45Z

    Philip Warner wrote:
> 
> At 23:34 1/07/00 +1000, Martijn van Oosterhout wrote:
> >
> >> Philip Warner needs alpha testers for his new version of pg_dump ;-).
> >> Unfortunately I think he's only been talking about it on pghackers
> >> so far.
> >
> >What versions does it work on?
> >
> 
> 6.5.x and 7.0.x.
> 
> Which version are you running?

Some version that came with redhat (6.5.x) (i didn't
install this machine). I'll grab it and see if it 
works....
-- 
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

  10. Re: disk backups

    Philip Warner <pjw@rhyme.com.au> — 2000-07-03T01:07:43Z

    At 23:36 2/07/00 +1000, Martijn van Oosterhout wrote:
>
>Some version that came with redhat (6.5.x) (i didn't
>install this machine). I'll grab it and see if it 
>works....

I'll need to put the 6.5 version on the FTP site first...

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/