Tool to compare db schemas?

[Chris Hoover]

Does anyone know of a good tool to compare db schemas? I have over 200 db's
that should all have identical schemas and have been asked to report on the
differences.

Thanks,

Chris


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[CoL]

hi,

Post by Chris Hoover
Does anyone know of a good tool to compare db schemas? I have over 200 db's
that should all have identical schemas and have been asked to report on the
differences.

if you looking for a program, check
http://ems-hitech.com/pgsqlutils/index.phtml#pgcomparer

but you can write your own scripts to compare schemas, even in plpgsql
too with different servers (using dblink)

C.

[Robert Treat]

Post by Chris Hoover
Does anyone know of a good tool to compare db schemas? I have over 200 db's
that should all have identical schemas and have been asked to report on the
differences.

http://pgdiff.sourceforge.net/
http://gborg.postgresql.org/project/pgdiff/projdisplay.php

If you run across any others please lmk, I think I'm going to need this
for a project in the next few days.

Robert Treat

[Chris Gamache]

When I run an query (that I have identified as expensive, and non-critical), I
would like to be able to take its priority down a few notches to allow the
standard every-day tasks of the DB to run unhindered. As it stands, postmasters
elbow each other out for processor time, and it seems like one expensive query
can bring other DB functions to a crawl.

Hardware : Dual P4 Xeon 2.8GHz; 1 GB RAM; ULTRA320 RAID 10 with an ext3
filesystem
PostgreSQL 7.4.2

What can be done to allow for smarter preemption? Could I do anything at the OS
level to throttle that particular postmaster's process? I'm running (IMO) a
balanced config, but there's always room for improvement. Its that oddball
query that comes around once every so often that causes the problem.

CG




__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[scott.marlowe]

Post by Chris Gamache
When I run an query (that I have identified as expensive, and non-critical), I
would like to be able to take its priority down a few notches to allow the
standard every-day tasks of the DB to run unhindered. As it stands, postmasters
elbow each other out for processor time, and it seems like one expensive query
can bring other DB functions to a crawl.
Hardware : Dual P4 Xeon 2.8GHz; 1 GB RAM; ULTRA320 RAID 10 with an ext3
filesystem
PostgreSQL 7.4.2
What can be done to allow for smarter preemption? Could I do anything at the OS
level to throttle that particular postmaster's process? I'm running (IMO) a
balanced config, but there's always room for improvement. Its that oddball
query that comes around once every so often that causes the problem.

It is inadvisable to change priority of backends as that could lead to
deadlocks in certain situations I believe.

Have you read:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

yet? It may just be that you don't have enough shared_buffers or whatnot.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Chris Gamache]

Post by scott.marlowe
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
yet? It may just be that you don't have enough shared_buffers or whatnot.

Absolutely. I have a copy on my desk, and one under my pillow at night. :)
PostgreSQL profiling is a bit of an art. I've been a settings-tweaker since I
can remember. I feel like my config is pretty well balanced. Its that giant,
oddball-query that throws the proverbial wrench in the gears. The guide is
quick to warn that "too much" in any particular setting can be as bad as "not
enough" ... That's not to say that someone else, with more expertise than I,
couldn't look at what I have as far as settings and choose some better ones.

The query_priority idea sounds like a promising idea. For queries that you
/know/ will be expensive (or that you know you want to have run lickety-split),
it'd do the trick. The next logical step would be an "intelligent" query
priority engine. However, that might require some ESP (the X-Files kind) ...
How do the other big RDBMS's do it?

CG




__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Gaetano Mendola]

Post by Chris Gamache
What can be done to allow for smarter preemption? Could I do anything at the OS
level to throttle that particular postmaster's process? I'm running (IMO) a
balanced config, but there's always room for improvement. Its that oddball
query that comes around once every so often that causes the problem.

You can basically renice the process that is performing the query.
See 'man nice' for details.

Regards
Gaetano Mendola

[Tom Lane]

Post by Gaetano Mendola
You can basically renice the process that is performing the query.

However, that's unlikely to do anything very pleasant, since you'll have
priority-inversion problems. "nice" has no idea when the process is
holding a lock that someone else wants ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Gaetano Mendola]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:

| Gaetano Mendola <***@bigfoot.com> writes:
|
|>You can basically renice the process that is performing the query.
|
|
| However, that's unlikely to do anything very pleasant, since you'll have
| priority-inversion problems. "nice" has no idea when the process is
| holding a lock that someone else wants ...

That can be true, however in order to have a priority-inversion problem
I think are necessary 3 different level of priority, you have carefully
choose the postmaster and good value of nice in order to have it happen.

I was wandering about do the same work done with vacuum ( the sleep
trick each n records) in order to slow some expensive but not crucial
queries:

test> set query_delay = 10; <-- 10 ms
test> select * from <very expensive query >;




Regards
Gaetano Mendola








-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAmZuP7UpzwH2SGd4RAvVxAKCfvQDk2CkdcC2dCFtgg7nLzf7qTwCgt8/w
F0zVE0HYoI9lb9l7u9qwZIo=
=/mFq
-----END PGP SIGNATURE-----


---------------------------(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

[scott.marlowe]

Post by Gaetano Mendola
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
|
|>You can basically renice the process that is performing the query.
|
|
| However, that's unlikely to do anything very pleasant, since you'll have
| priority-inversion problems. "nice" has no idea when the process is
| holding a lock that someone else wants ...
That can be true, however in order to have a priority-inversion problem
I think are necessary 3 different level of priority, you have carefully
choose the postmaster and good value of nice in order to have it happen.
I was wandering about do the same work done with vacuum ( the sleep
trick each n records) in order to slow some expensive but not crucial
test> set query_delay = 10; <-- 10 ms
test> select * from <very expensive query >;

I like that idea. Make it more like a query_priority and let the system
figure out delays though.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html