

 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




Thu, 6 Apr 2000 13:02:45 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
pg_dumplo, thanks :) (fwd)
>  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

Thu, 06 Apr 2000 13:37:25 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: pg_dumplo, thanks :) (fwd)
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
Thu, 6 Apr 2000 15:49:15 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: pg_dumplo, thanks :) (fwd)
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 Service and other goodies at
  http://donb.photo.net.

Thu, 06 Apr 2000 07:20:19 -0700
Don Baccus <dhogaza@pacifier.com>
Re: pg_dumplo, thanks :) (fwd)

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


Thu, 6 Apr 2000 18:17:49 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: pg_dumplo, thanks :) (fwd)
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 Service and other goodies at
  http://donb.photo.net.

Thu, 06 Apr 2000 10:33:11 -0700
Don Baccus <dhogaza@pacifier.com>
Re: pg_dumplo, thanks :) (fwd)
> 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, Pennsylvania 19026

Thu, 6 Apr 2000 14:05:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dumplo, thanks :) (fwd)
> 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, Pennsylvania 19026

Thu, 6 Apr 2000 14:06:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dumplo, thanks :) (fwd)

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




Thu, 6 Apr 2000 20:40:54 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: pg_dumplo, thanks :) (fwd)
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 Service and other goodies at
  http://donb.photo.net.

Thu, 06 Apr 2000 11:46:40 -0700
Don Baccus <dhogaza@pacifier.com>
Re: pg_dumplo, thanks :) (fwd)
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 Service and other goodies at
  http://donb.photo.net.

Thu, 06 Apr 2000 11:50:25 -0700
Don Baccus <dhogaza@pacifier.com>
Re: pg_dumplo, thanks :) (fwd)
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 Service and other goodies at
  http://donb.photo.net.

Thu, 06 Apr 2000 11:58:06 -0700
Don Baccus <dhogaza@pacifier.com>
Re: pg_dumplo, thanks :) (fwd)
> 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, Pennsylvania 19026

Thu, 6 Apr 2000 14:58:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dumplo, thanks :) (fwd)
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

Thu, 06 Apr 2000 15:14:01 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: pg_dumplo, thanks :) (fwd)
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, Pennsylvania 19026

Mon, 12 Jun 2000 00:01:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dumplo, thanks :) (fwd)
Also, I backed out our createtable/lock pg_shadow changes until we
decide how to handle that.


> 
> 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, Pennsylvania 19026

Mon, 12 Jun 2000 00:02:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
pg_shadow change

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


Mon, 12 Jun 2000 10:44:07 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: pg_dumplo, thanks :) (fwd)

On Mon, 12 Jun 2000, Bruce Momjian wrote:

> Also, I backed out our createtable/lock pg_shadow changes until we
> decide how to handle that.
> 

 OK. 

					Karel


Mon, 12 Jun 2000 11:30:59 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: pg_shadow change