Thread: Re: 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
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/> and here are the sources: <http://subversion.tigris.org/>. Have fun, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
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
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