Re: from PG_DUMP to CVS
am 26.08.2004 12:43:20 von abief_ag_-postgresql
--- Philip Warner <__> wrote:
> At 08:04 PM 26/08/2004, Riccardo G. Facchini wrote:
> >Does somebody know of a script that does this job?
>
> No, but a very useful idea.
>
> Sounds like another dump format to me -- so long as a well-defined
> structure that is likely to remain invariant over versions can be
> used. A
> client uses a trivial script to dump functions, but not tables,
> triggers or
> anything else.
>
> The simplest format might be to use the database names as a level 1
> dir,
> schema names at level 2, and entry names at level 3, then create
> files for
> the 'definition', 'data' etc.
>
> It would be a little confusing when ACLs and constraints were dumped
> (eg.
> they would not appear under the tables). But it would be consistent,
> at least.
>
> Of course it would be impossible to restore from such a beast since
> we
> would be throwing away ordering -- I think.
>
[..]
The plan is not to have something to be restored, but something that
can be published on a CVS server, in order to see what has been
modified overtime... I'd love to have this beast running daily.
If you know of something even similar to what I'm looking for, let me
know.
regards,
Riccardo
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Re: from PG_DUMP to CVS
am 26.08.2004 13:48:37 von abief_ag_-postgresql
--- Philip Warner <__> wrote:
> At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote:
> >If you know of something even similar to what I'm looking for, let
> me
> >know.
>
> My thinking is to modify pg_dump to add a new output format, but I'd
> like
> to get some more feedback from others first, including yourself. Does
> what
> I specified before satisfy all your needs?
>
>
> ------------------------------------------------------------ ----
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 03 5330 3172 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp.mit.edu:11371 |/
>
>
yes, I think a specific format for pg_dump could be interesting.
From my point of view, I would like to get a directory structure like
this one:
/schemas
/
/.sql
/tables
/.sql
/views
/.sql
/functions
/.sql
/domains
/.sql
/types
/.sql
/aggregates
/.sql
/operators
/.sql
/priming
/.sql
/acls
/schemas
/
/.sql
/tables
/.sql
/views
/.sql
/functions
/.sql
/domains
/.sql
/types
/.sql
/aggregates
/.sql
/operators
/.sql
/languages
/.sql
either following the pgadmin3 style or, better still, the ems
postgresql manager output style (I believe is superior, even if not
OS).
My feeling is that a lot of the code for an output of this kind is
already in the pgadmi3 code, so it could be a matter of codebashing.
I wouldn't separate the functions from the triggers, but beware of the
function overloading, but that's a small issue compared to the task.
best regards,
=====
Riccardo G. Facchini
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: from PG_DUMP to CVS
am 26.08.2004 14:05:04 von abief_ag_-postgresql
--- Kenneth Gonsalves <__> wrote:
> On Thursday 26 August 2004 04:48 pm, Philip Warner wrote:
> > At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote:
> > >If you know of something even similar to what I'm looking for, let
> me
> > >know.
> >
> > My thinking is to modify pg_dump to add a new output format, but
> I'd like
> > to get some more feedback from others first, including yourself.
> Does what
> > I specified before satisfy all your needs?
>
> there is a utility that does something like this - it is mentioned in
> a
> thread on this list relating to surrogate keys - posted by Karsten
> Hilbert -
> about a month back - sorry cant be more specific, i've stored the
> link
> somewhere, cant find it
> --
> regards
> kg
>
> http://www.onlineindianhotels.net - fastest hotel search website in
> the world
> http://www.ootygolfclub.org
>
After searching throught the list, I assume you mean this link:
http://www.rbt.ca/autodoc/index.html
by Rod Taylor.
Looks promising, but still what I need is a proper CVS output, as I
need to review the changes made to the specific database structure.
thanks for the link,
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Auto-update script from dumps?
am 26.08.2004 15:06:53 von Joerg.Hessdoerfer
Hi!
What I'm looking for is something that handles DB schema upgrades. We're
building applications which require upgrades to the DB schema over time, so
when we upgrade our apps we need to modify the DB. For this, we're using SQL
scripts, which basically do incremental updates, e.g. from 1.2 to 1.3. So
when we upgrade FooBar Version 2.3, which uses DB schema 1.3 to Version 3.9,
which uses DB schema 1.9 we would run 6 scripts, and voila, we would have a
1.9 schema.
OK, sounds simple. But it's sometimes a lot of work to build the update
script, and easy to miss something. So I was just thinking whether there's a
tool or script out there that builds an update script from two different
schema dumps. Does anyone out there know of such a beast?
Greetings,
Jörg
--
Leading SW developer - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: from PG_DUMP to CVS
am 26.08.2004 19:06:32 von gsstark
"Riccardo G. Facchini" writes:
> After searching throught the list, I assume you mean this link:
> http://www.rbt.ca/autodoc/index.html
> by Rod Taylor.
>
> Looks promising, but still what I need is a proper CVS output, as I
> need to review the changes made to the specific database structure.
Well, CVS can still be useful even if the changes are all in one file. Look at
"cvs annotate" for example. And CVS diff would still give you useful
information.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: from PG_DUMP to CVS
am 27.08.2004 06:38:30 von Josh
Riccardo,
> Looks promising, but still what I need is a proper CVS output, as I
> need to review the changes made to the specific database structure.
If it's Perl, I'd be interested in contributing. I've long needed something
like this myself.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Re: from PG_DUMP to CVS
am 27.08.2004 13:15:08 von pjw
At 02:38 PM 27/08/2004, Josh Berkus wrote:
>If it's Perl, I'd be interested in contributing. I've long needed something
>like this myself.
My thinking at this stage is to try to get pg_dump/restore to produce the
output directly. Something like:
some-dbname/create.sql
some-dbname/drop.sql
some-dbname/econding.sql
...
some-dbname/some-schema/TABLE/sometable/create.sql
some-dbname/some-schema/TABLE/sometable/drop.sql
...
some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql
some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql
...
some-dbname/some-schema/ACL/some-table.sql
some-dbname/some-schema/ACL/some-function(int).sql
...
etc.
This would be easy. Question is, how useful would it be?
------------------------------------------------------------ ----
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: from PG_DUMP to CVS
am 27.08.2004 15:06:42 von abief_ag_-postgresql
That depends on the use you plan to... as a backup, useless, as a
documentary tool, very (at least, for what I need).
Our way of developing things is to set up a development box, and set up
the tables, functions, etc etc etc using a set of tools... the only
problem is that these do not provide any kind of versioning control. At
least, not one that the user will be able to understand or use.
The problem that we face right now is that we provide not only
versioning control, but web access to it!
CVS is agreed on (both parties like it), but we need a quick way to
convert the gazillions of tables, rules, views, functions, procedures,
etc etc etc to an output format that can be moved to the CVS and, if
necessity arouse, use the single files to perform maintenance on the
user's end.
This way, we are happy because we don't need to revise our way of
development and the user is happy because he sees what we're doing, and
he's able trace the changes.
One other good things is that if this is done the right way, almost no
human time is required to provide the info, and the tool can run daily.
regards,
Riccardo
--- Philip Warner <__> wrote:
> At 02:38 PM 27/08/2004, Josh Berkus wrote:
> >If it's Perl, I'd be interested in contributing. I've long needed
> something
> >like this myself.
>
> My thinking at this stage is to try to get pg_dump/restore to produce
> the
> output directly. Something like:
>
> some-dbname/create.sql
> some-dbname/drop.sql
> some-dbname/econding.sql
> ...
> some-dbname/some-schema/TABLE/sometable/create.sql
> some-dbname/some-schema/TABLE/sometable/drop.sql
> ...
> some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql
> some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql
> ...
> some-dbname/some-schema/ACL/some-table.sql
> some-dbname/some-schema/ACL/some-function(int).sql
> ...
>
> etc.
>
> This would be easy. Question is, how useful would it be?
>
>
>
> ------------------------------------------------------------ ----
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 03 5330 3172 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp.mit.edu:11371 |/
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Re: from PG_DUMP to CVS
am 27.08.2004 18:02:20 von Josh
Philip,
> My thinking at this stage is to try to get pg_dump/restore to produce the
> output directly. Something like:
Hey, you do what you want, of course. However, it seems to me that hacking
AutoDoc would be a *lot* less effort than hacking pg_dump.
Interestingly, though, I was talking to someone on IRC (Neil? Gavin?) some 6
months ago or so about hacking a "PSQL-FS" that is, an interface to the
*live* database which would look like a filesystem. Were this done, it
would be child's play to rsync it with an archive. Whomever it was didn't
seem to think it too challenging a task.
> some-dbname/create.sql
> some-dbname/drop.sql
> some-dbname/econding.sql
> ...
> some-dbname/some-schema/TABLE/sometable/create.sql
> some-dbname/some-schema/TABLE/sometable/drop.sql
Hmmm. I see a slightly different structure below the schema level:
some-dbname/some-schema/TABLES/sometable/create.sql
some-dbname/some-schema/TABLES/sometable/indexes.sql
some-dbname/some-schema/TABLES/sometable/constraints.sql
some-dbname/some-schema/TABLES/sometable/triggers.sql
some-dbname/some-schema/TABLES/sometable/rules.sql
some-dbname/some-schema/VIEWS/someview/create.sql
some-dbname/some-schema/VIEWS/someview/rules.sql
some-dbname/some-schema/FUNCTIONS/somefunction/param{codes}. sql
some-dbname/some-schema/TYPES/sometype/create.sql
some-dbname/some-schema/OPERATORS/someoperator/create.sql
However, the above is somewhat unfriendly to CVS, as one can't drop
directories in CVS and that would be entailed in the dropping of any objects.
An alternative would be:
some-dbname/some-schema/TABLES/sometable-create.sql
some-dbname/some-schema/TABLES/sometable-indexes.sql
some-dbname/some-schema/TABLES/sometable-constraints.sql
some-dbname/some-schema/TABLES/sometable-triggers.sql
some-dbname/some-schema/TABLES/sometable-rules.sql
some-dbname/some-schema/VIEWS/someview-create.sql
some-dbname/some-schema/VIEWS/someview-rules.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}. sql
some-dbname/some-schema/TYPES/sometype-create.sql
some-dbname/some-schema/OPERATORS/someoperator-create.sql
or even:
some-dbname/some-schema/TABLES/sometable.sql
some-dbname/some-schema/VIEWS/someview.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}. sql
some-dbname/some-schema/TYPES/sometype.sql
some-dbname/some-schema/OPERATORS/OPsomeoperator.sql
In this last, all dependant objects of, for example, a table (rules, triggers,
indexes, etc. ) would be rolled up into one file. It's this last version
that I personally favor.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: from PG_DUMP to CVS
am 28.08.2004 18:10:26 von pjw
At 02:02 AM 28/08/2004, Josh Berkus wrote:
>some-dbname/some-schema/TABLES/sometable.sql
>some-dbname/some-schema/VIEWS/someview.sql
>some-dbname/some-schema/FUNCTIONS/somefunction-param{codes} .sql
>some-dbname/some-schema/TYPES/sometype.sql
>some-dbname/some-schema/OPERATORS/OPsomeoperator.sql
>
>In this last, all dependant objects of, for example, a table (rules,
>triggers,
>indexes, etc. ) would be rolled up into one file. It's this last version
>that I personally favor.
Not sure I like it from the PoV of easily seeing what has changed.
Similarly:
>some-dbname/some-schema/TABLES/sometable/create.sql
>some-dbname/some-schema/TABLES/sometable/indexes.sql
>some-dbname/some-schema/TABLES/sometable/constraints.sql
combines all indexes into one file. It also has the disadvantage if being
impossible to construct from an existing dump file.
I'd like to be able to construct the structure from the information stored
in a dump file, without parsing SQL. OTOH, it might be nice to add some
more information to the dump file.
------------------------------------------------------------ ----
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match