Thread: pg_dumplo, thanks :) (fwd)
Hi,
this is not first letter about pg_dumplo which I head. What add pg_dumplo
to contrib or main tree?
Karel
---------- Forwarded message ----------
Date: Wed, 05 Apr 2000 11:51:53 -0400
From: CTN Production <reaster@comptechnews.com>
To: zakkr@zf.jcu.cz
Subject: pg_dumplo, thanks :)
Your pg_dumplo program looks to be very useful so far. I've tested it
and have had no trouble with PostgreSQL 7.0 beta. I can now easily make
a script that runs pg_dumplo and pg_dump to create a directory
containing a full dump of a database. Another script does a full
restore of a database. Very nice. I used "pg_dump -vof database.dump
database", which makes it dump the OIDs too since I use the OIDs of
records instead of serials.
Thanks. Wonder why this kind of utility is not part of the official
distribution? You might consider posting your pg_dumplo program on
http://www.freshmeat.net/ so that people can find it and get you some
more recognition! :)
How large of databases have you used pg_dumplo on? I hope that it can
handle things when the database gets large.
Robert B. Easter
reaster@comptechnews.com
> this is not first letter about pg_dumplo which I head. What add pg_dumplo
> to contrib or main tree?
I probably haven't been paying attention. Have we heard about
pg_dumplo? Have you posted it so we can see it?
There is no fundamental problem including a utility like this in the
main tree or the contrib/ area, but tell us more about it and show us
the code! :)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Thu, 6 Apr 2000, Thomas Lockhart wrote:
> > this is not first letter about pg_dumplo which I head. What add pg_dumplo
> > to contrib or main tree?
>
> I probably haven't been paying attention. Have we heard about
> pg_dumplo? Have you posted it so we can see it?
Yes. I send information about it twice (or more) to some PG lists....
(Users which use it know it from PG lists only. I nowhere annonced it.)
> There is no fundamental problem including a utility like this in the
> main tree or the contrib/ area, but tell us more about it and show us
> the code! :)
Well, pg_dumplo is in attache. It is really simple program and now is not
prepared for dirtribution (it needs a little changes). I can change and work
on this, but I need motivation :-)
And Peter, I know and I agree that standard PG tree is not good space for
all interfaces and for all tools based on PG, but LO is PG feature and we
haven't backup tool for LO.
Karel
At 01:37 PM 4/6/00 +0000, Thomas Lockhart wrote: >> this is not first letter about pg_dumplo which I head. What add pg_dumplo >> to contrib or main tree? > >I probably haven't been paying attention. Have we heard about >pg_dumplo? Have you posted it so we can see it? > >There is no fundamental problem including a utility like this in the >main tree or the contrib/ area, but tell us more about it and show us >the code! :) If it runs as a separate utility, there's no way for it to guarantee a dump consistent with the previous run of pg_dump, right? While this is OK, one of the great things about 6.5 is the fact that pg_dump now makes a consistent dump, you don't have to tear down all your users before doing a backup. So wouldn't it be better to fold pg_dumplo into pg_dump? - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
On Thu, 6 Apr 2000, Don Baccus wrote:
> If it runs as a separate utility, there's no way for it to guarantee
> a dump consistent with the previous run of pg_dump, right?
If you dump your tables via pg_dump and promptly you dump LO via
pg_dumplo, IMHO you not have problem with DB consistency. In table-dump
is in columns OID which use LO-dump index.
> So wouldn't it be better to fold pg_dumplo into pg_dump?
Yes. If I good remember, anyone plan rewrite pg_dump. Or not? If not, I can
rewrite it, because I very need good backup tools (I have important large
databases (with LO too)).
Karel
On Thu, 6 Apr 2000, Peter Mount wrote:
> In the past I had thought of writing something similar as an example for
> JDBC (dump the LO's into a zip file). The thing I couldn't fathom (and
> now I'm saying this, it's probably a simple thing to do), was the
> restore. How do you create an lo with a specific oid?
Very good question. IMHO is not method (in standard API) how create LO with
a specific oid. The pg_dumplo during LO-dump import rewrite (UPDATE) your old
oid in defined column. Yes, you must not use LO's oid as join key between
tables or save LO's oid to the others columns than you defined in pg_dumplo
command line.
The TOAST is deliverance from this limitation.
Karel
At 06:17 PM 4/6/00 +0200, Karel Zak wrote: > >On Thu, 6 Apr 2000, Don Baccus wrote: > >> If it runs as a separate utility, there's no way for it to guarantee >> a dump consistent with the previous run of pg_dump, right? > > If you dump your tables via pg_dump and promptly you dump LO via >pg_dumplo, IMHO you not have problem with DB consistency. Folks who have popular web sites with a world-wide audience don't have the traditional early-morning "quiet periods", etc that local databases tend to enjoy. Since my group of folks are distributing a web toolkit for general use, I tend to think in very general terms and any solution we distribute wants to be very general, as well. In the vast majority of cases, you're right that the odds would be low of a problem cropping up in reality, but the odds aren't zero unless you knock out all other db uses while dumping. For our toolkit, I don't really care because we have our own BLOB-ish hack for storing photos, word documents, etc using some SQL and AOLserver driver magic I wrote, and these are pg_dumpable. My main reason for bringing up the point was: >> So wouldn't it be better to fold pg_dumplo into pg_dump? and you seem to agree: >Yes. If I good remember, anyone plan rewrite pg_dump. Or not? If not, I can >rewrite it, because I very need good backup tools (I have important large >databases (with LO too)). So I think we're on the same wavelength. Since you've conveniently made a post that reached my mailbox right after a query from someone working on our toolkit port from Oracle to PG, did you know that in Oracle to_char formatting chars don't have to be upper case? In other words something like "to_char(sysdate, 'yyyy-mm-dd')" formats sysdate rather than ignore the formatting characters. Turns out the toolkit we're porting from Oracle almost always uses upper case, but not always and one of our gang just ran into this earlier this morning while porting over one of the toolkit module... BTW, I can't begin to tell you how much easier our porting job is due to the existence of to_char... - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 06:42 PM 4/6/00 +0200, Karel Zak wrote: > >On Thu, 6 Apr 2000, Peter Mount wrote: > >> In the past I had thought of writing something similar as an example for >> JDBC (dump the LO's into a zip file). The thing I couldn't fathom (and >> now I'm saying this, it's probably a simple thing to do), was the >> restore. How do you create an lo with a specific oid? > > Very good question. IMHO is not method (in standard API) how create LO with >a specific oid. The pg_dumplo during LO-dump import rewrite (UPDATE) your old >oid in defined column. Yes, you must not use LO's oid as join key between >tables or save LO's oid to the others columns than you defined in pg_dumplo >command line. > > The TOAST is deliverance from this limitation. We could actually deliver ourselves from this limitation absent TOAST, if we wanted, by using something other than the OID as the key for the created LO item. In fact, this is sorta what I did for my BLOB-ish AOLserver hack for our web toolkit, but I don't use the actual lo code for a variety of reasons. But I looked at it pretty thoroughly... Since TOAST's on the horizon, I didn't have any real motivation or interest in working up a less restrictive lo implementation and don't think there's any real reason to do so. But, LO's dependence on OIDs is an implementation artifact that's not at all necessary. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> At 06:17 PM 4/6/00 +0200, Karel Zak wrote: > > > >On Thu, 6 Apr 2000, Don Baccus wrote: > > > >> If it runs as a separate utility, there's no way for it to guarantee > >> a dump consistent with the previous run of pg_dump, right? > > > > If you dump your tables via pg_dump and promptly you dump LO via > >pg_dumplo, IMHO you not have problem with DB consistency. > > Folks who have popular web sites with a world-wide audience don't have > the traditional early-morning "quiet periods", etc that local databases > tend to enjoy. Since my group of folks are distributing a web toolkit > for general use, I tend to think in very general terms and any solution > we distribute wants to be very general, as well. How do you get around vacuum downtime? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Since you've conveniently made a post that reached my mailbox right after > a query from someone working on our toolkit port from Oracle to PG, did you > know that in Oracle to_char formatting chars don't have to be upper case? > > In other words something like "to_char(sysdate, 'yyyy-mm-dd')" formats > sysdate rather than ignore the formatting characters. Turns out the > toolkit we're porting from Oracle almost always uses upper case, but > not always and one of our gang just ran into this earlier this morning > while porting over one of the toolkit module... Doesn't the upper/lower affect how the result displays. I think that is a cool effect. > > BTW, I can't begin to tell you how much easier our porting job is due > to the existence of to_char... Great. That is new to 7.0. We like ports _from_ Oracle. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Thu, 6 Apr 2000, Bruce Momjian wrote:
> > Since you've conveniently made a post that reached my mailbox right after
> > a query from someone working on our toolkit port from Oracle to PG, did you
> > know that in Oracle to_char formatting chars don't have to be upper case?
> >
> > In other words something like "to_char(sysdate, 'yyyy-mm-dd')" formats
> > sysdate rather than ignore the formatting characters. Turns out the
> > toolkit we're porting from Oracle almost always uses upper case, but
> > not always and one of our gang just ran into this earlier this morning
> > while porting over one of the toolkit module...
>
> Doesn't the upper/lower affect how the result displays. I think that is
> a cool effect.
Thanks Don. I tomorrow check it and comperate it with Oracle and if is here
a problem I fix it. In stable 7.0 it will right.
PG's to_char() is based on upper case. Hmm, but it is not easy, it must be
case sensitive for some format-pictures (like to_char(now(), 'Day') and for
to_char(now(), 'yyyy') is upper/lower without effect. I fix it and add this
feature to internal to_char's parser.
Karel
At 02:05 PM 4/6/00 -0400, Bruce Momjian wrote: >How do you get around vacuum downtime? People wait...I guess the point is we want to avoid as much downtime as possible. Before 6.5 came out with a consistent pg_dump utility, I was prepared to knock down the site nightly for backups. The appearance of consistent pg_dumps was a welcome surprise, what can I say? :) I posed the question because my assumption was that it wouldn't be that hard to roll it into pg_dump if it works well and is reliable, and that this would be desirable. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 02:06 PM 4/6/00 -0400, Bruce Momjian wrote: >> In other words something like "to_char(sysdate, 'yyyy-mm-dd')" formats >> sysdate rather than ignore the formatting characters. Turns out the >> toolkit we're porting from Oracle almost always uses upper case, but >> not always and one of our gang just ran into this earlier this morning >> while porting over one of the toolkit module... > >Doesn't the upper/lower affect how the result displays. I think that is >a cool effect. Not in Oracle, AFAIK. I'm not enough of an Oracle nerd to know for sure, actually, I'm helping port this stuff from Oracle so I can avoid using it! (in particular, paying for it) In the current PG implementation, lower case strings aren't recognized as format strings at all, apparently... >> BTW, I can't begin to tell you how much easier our porting job is due >> to the existence of to_char... > >Great. That is new to 7.0. Yeah, we know that...actually one of our crew wrote a to_char using embedded Tcl for 6.5, but having to_char built-in is nice. > We like ports _from_ Oracle. Well...you've got about 150 more folks using PG 7.0 beta2 than you would without it... - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> At 02:05 PM 4/6/00 -0400, Bruce Momjian wrote: > > >How do you get around vacuum downtime? > > People wait...I guess the point is we want to avoid as much downtime > as possible. Before 6.5 came out with a consistent pg_dump utility, > I was prepared to knock down the site nightly for backups. The > appearance of consistent pg_dumps was a welcome surprise, what can > I say? :) > > I posed the question because my assumption was that it wouldn't be > that hard to roll it into pg_dump if it works well and is reliable, > and that this would be desirable. Sure. Of course, TOAST changes all that. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
At 08:40 PM 4/6/00 +0200, Karel Zak wrote: > PG's to_char() is based on upper case. Hmm, but it is not easy, it must be >case sensitive for some format-pictures (like to_char(now(), 'Day') and for >to_char(now(), 'yyyy') is upper/lower without effect. I fix it and add this >feature to internal to_char's parser. If you have specific test cases where you're not sure if Oracle's case sensitive or not, let me know - I have ready access to an Oracle installation. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Bruce Momjian wrote: > Don Baccus wrote: > > Folks who have popular web sites with a world-wide audience don't have > > the traditional early-morning "quiet periods", etc that local databases > How do you get around vacuum downtime? I'll attempt to field that one, as I am helping a little with the port of this same toolkit, and have been using PostgreSQL in moderate intranet/light internet production for two and a half years (since 6.1.1 -- scary thought). I vacuum nightly, at semi-random times around my quietest times, which are around 3-4 AM EDT. While 6.[1234] were pretty hokey around those times, like locking out readers during vacuum... but 6.5.x drastically improved the situation, to where I have not seen any error returns or noticeable delays during vacuum times -- but, then again, I don't have very many accesses during that time. Now if a continuous vacuuming storage manager could be built... I can see conceptually how one would go about it, I am nowhere near confortable trying to do it myself. However, the list of 7.1 things todo already is staggering -- several major projects all at once. IMHO, those major projects should be tackled before relatively minor ones are. In particular, once the fmgr redesign is done, the separate Alpha patches may get to be retired. The WAL stuff is essential for good recoverability, large tuples have been on nearly everyone's wish list for a very long time, and lack of outer joins are a hindrance, particularly when porting a web toolkit from Oracle :-). Although, CONNECT BY would be nice for Oracle porting :-) In any case, the PostgreSQL team's progress from 6.1.1 to now is more than impressive. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Applied to /contrib. If we don't need it with TOAST, we can remove or modify it. > > On Thu, 6 Apr 2000, Thomas Lockhart wrote: > > > > this is not first letter about pg_dumplo which I head. What add pg_dumplo > > > to contrib or main tree? > > > > I probably haven't been paying attention. Have we heard about > > pg_dumplo? Have you posted it so we can see it? > > Yes. I send information about it twice (or more) to some PG lists.... > (Users which use it know it from PG lists only. I nowhere annonced it.) > > > There is no fundamental problem including a utility like this in the > > main tree or the contrib/ area, but tell us more about it and show us > > the code! :) > > Well, pg_dumplo is in attache. It is really simple program and now is not > prepared for dirtribution (it needs a little changes). I can change and work > on this, but I need motivation :-) > > And Peter, I know and I agree that standard PG tree is not good space for > all interfaces and for all tools based on PG, but LO is PG feature and we > haven't backup tool for LO. > > Karel > Content-Description: [ application/x-gzip is not supported, skipping... ] -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Mon, 12 Jun 2000, Bruce Momjian wrote:
> Applied to /contrib. If we don't need it with TOAST, we can remove or
> modify it.
>
Thanks. Well, it is good motivation for me --- I will continue on
development on this program.
IMHO we must support LO after TOAST implementation too. Some
large applications use LO and crossing to TOAST not will at once.
How idea is for LO in TOASTed PG --- will LO internal use TOAST
and as API current open/close/etc.? Or nothing will changed in LO?
Karel