Thread: disk backups
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
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?
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/
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
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/
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
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/
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 |/
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/
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 |/