Schema comparison tool

The database comparer tool is one of EMS better products.

http://www.sqlmanager.net/
http://www.sqlmanager.net/products/postgresql/dbcomparer

Jeff Amiel <jamiel@istreamimaging.com> wrote:

I'm looking specifically for a tool to help compare 2 database schemas
(nominally, production and development) and generate the appropriate SQL
(that can be later executed) to bring the to schema's into sync.

Option1 :
pg_dump -s �firstdatabasename > first
pg_dump -s seconddatabasename > second
diff first second
(obviously wont generate SQL to remedy the differences)

Option2:
pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php)
(old abandoned perl project that doesn't work...)

Option 3:
pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php)
(old abandoned TCL project which requires AOLServer to run???)

Option 4:
EMS PostgreSQL DB Comparer
(http://www.sqlmanager.net/products/postgresql/dbcomparer)
Slick tool...fairly cheap ($69)...doesnt let you exclude certain
specific tables/sequences/databases, but otherwise, pretty functional.

What does the rest of the postgreSQL admin community do in order to
"bring-live" database schema changes from their development environment
to production? �Are there other options/techniques out there?

Any help would be appreciated.

Jeff

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
� �(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

On Tue, 2005-02-15 at 15:01 +0000, Jeff Amiel wrote:

I'm looking specifically for a tool to help compare 2 database schemas
(nominally, production and development) and generate the appropriate SQL
(that can be later executed) to bring the to schema's into sync.

[snip]

What does the rest of the postgreSQL admin community do in order to
"bring-live" database schema changes from their development environment
to production?

The project I'm working on at the moment has a very dynamic schema.
Most of our software releases have some requirement to tweak columns or
add new tables, etc. We nominally use Power Designer for managing our
schema but in reality, the main thing we use it for is drawing the
pretty schema diagrams (and it's not even very good at that).

Obviously, before any schema change can be rolled out to production, the
precise method which will be used to apply the change in production must
be tested in development and staging. Therefore, each software release
includes some number of schema patch files which must be applied in
order.

We have a Perl script for applying patches which basically just feeds
each .sql file to psql (we find the psql \set macro functionality useful
in the patches) in order. If a patch is applied successfully, we update
a row in a config table in the database to indicate the last patch
applied. When we re-run apply_patches.pl, it looks at the config item
to see what patch-level the database is up to and then apply each of the
newer patches.

Another part of the puzzle is that our regression test suite includes a
schema 'test'. This basically just uses the first technique you
outlined - the test creates one database from the Power Designer
generated model and uses pg_dump -s on it; then creates another database
from the original unpatched schema, applies all the patches and dumps it
too. If a diff on the dumps reveals no differences then the test
passes, otherwise the test fails - which usually means we need to update
the Power Designer model. (The test script has to do a bit of
reordering and whitespace normalising to avoid false positives).

Although GUI design tools can be seductive, our developers seem to be
more comfortable with using vi on a .sql file. Usually what they want
to do is "exactly like this other thing but with this minor difference"
which will always be easier in vi than in a GUI tool.

So in summary, we build our schema patches manually. And we use our
manual processes to drive the use of our GUI designer tool.

If you want to try our apply patches script, I've dropped a copy here:

http://wellington.pm.org/archive/postgresql/apply_patches.tar.gz

Cheers
Grant

I keep scripts for drop/creation or replacement of each type of object,
and a shell script to run them in order. I only make changes in the
scripts. They can be run one at a time if only one type of object has
changed or been created. I keep backups of data only, and have an
intermediate step in the shell script that loads data after tables are
created and before the other objects are created.

The scripts are in a revision control system, so if I have to restore
old data, I use the old schema.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

Jeff Amiel <jamiel@istreamimaging.com> 02/15/05 7:01 AM >>>

I'm looking specifically for a tool to help compare 2 database schemas
(nominally, production and development) and generate the appropriate SQL

(that can be later executed) to bring the to schema's into sync.

Option1 :
pg_dump -s firstdatabasename > first
pg_dump -s seconddatabasename > second
diff first second
(obviously wont generate SQL to remedy the differences)

Option2:
pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php)
(old abandoned perl project that doesn't work...)

Option 3:
pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php)
(old abandoned TCL project which requires AOLServer to run???)

Option 4:
EMS PostgreSQL DB Comparer
(http://www.sqlmanager.net/products/postgresql/dbcomparer)
Slick tool...fairly cheap ($69)...doesnt let you exclude certain
specific tables/sequences/databases, but otherwise, pretty functional.

What does the rest of the postgreSQL admin community do in order to
"bring-live" database schema changes from their development environment
to production? Are there other options/techniques out there?

Any help would be appreciated.

Jeff

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Wed, 16 Feb 2005, Ian Harding wrote:

I keep scripts for drop/creation or replacement of each type of object,

Jeff Amiel <jamiel@istreamimaging.com> 02/15/05 7:01 AM >>>

I'm looking specifically for a tool to help compare 2 database schemas
(nominally, production and development) and generate the appropriate SQL

(that can be later executed) to bring the to schema's into sync.

Jeff,

Check out subversion, the replacement for cvs. You can keep your schemas
in the repository, modify and compare, merge changes to the head and all
sorts of fun things. Subversion is also for documents.

Here're the docs: <http://svnbook.red-bean.com/&gt; and here are the sources:
<http://subversion.tigris.org/&gt;.

Have fun,

Rich

--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

There is a program called pgdiff.. but I could not really make it work and it
seems to be quiet old and unmaintained: http://pgdiff.sourceforge.net/

Martin

Agreed, the EMS product really is a slick tool. On another front, somewhere
on my todo list is to add database diffing abilities to phppgadmin. There's
a multiple db connection patch floating around that looks like it will be a
help; if I can clear my schedule up enough I might get back to this.

Robert Treat

On Tuesday 15 February 2005 16:31, Bradley D. Snobar wrote:

The database comparer tool is one of EMS better products.

http://www.sqlmanager.net/
http://www.sqlmanager.net/products/postgresql/dbcomparer

Jeff Amiel <jamiel@istreamimaging.com> wrote:

I'm looking specifically for a tool to help compare 2 database schemas
(nominally, production and development) and generate the appropriate SQL
(that can be later executed) to bring the to schema's into sync.

Option1 :
pg_dump -s  firstdatabasename > first
pg_dump -s seconddatabasename > second
diff first second
(obviously wont generate SQL to remedy the differences)

Option2:
pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php)
(old abandoned perl project that doesn't work...)

Option 3:
pgdiff (http://gborg.postgresql.org/project/pgdiff/projdisplay.php)
(old abandoned TCL project which requires AOLServer to run???)

Option 4:
EMS PostgreSQL DB Comparer
(http://www.sqlmanager.net/products/postgresql/dbcomparer)
Slick tool...fairly cheap ($69)...doesnt let you exclude certain
specific tables/sequences/databases, but otherwise, pretty functional.

What does the rest of the postgreSQL admin community do in order to
"bring-live" database schema changes from their development environment
to production?  Are there other options/techniques out there?

Any help would be appreciated.

Jeff

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at
http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

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

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL