Thread: Database schema & data synchronizer software for PostgreSQL?
Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Thank you, -- Best Regards, Csaba Együd
On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: > Hi, > I'd like to ask your suggestions about a reliable admin software > which is able to compare two dabases and generate a schema > synchrinizer script. There is no such thing, and there is no prospect of there ever being such a thing, because the database does not contain enough information to create this automatically. The problem exists at the organizational level, and needs to be solved there. > It would be nice to be able to generate data synchronization script > for only the selected tables, and other features. Yes, you should definitely do that and store the scripts to do it in your source code management system along with all the rest of the deploy and upgrade scripts. They can't be generated automatically either. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tuesday 20 January 2009 10:44:06 David Fetter wrote: > On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: > > Hi, > > I'd like to ask your suggestions about a reliable admin software > > which is able to compare two dabases and generate a schema > > synchrinizer script. > > There is no such thing, and there is no prospect of there ever being > such a thing, because the database does not contain enough information > to create this automatically. The problem exists at the > organizational level, and needs to be solved there. > While I would agree that these tools can't solve organizational problems, they do exist: http://pgdiff.sourceforge.net/ http://apgdiff.sourceforge.net/ http://www.dbsolo.com/ http://sqlmanager.net/en/products/postgresql/dbcomparer there are others too... -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com
On Tue, Jan 20, 2009 at 09:51:25PM -0500, Robert Treat wrote: > On Tuesday 20 January 2009 10:44:06 David Fetter wrote: > > On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: > > > Hi, > > > I'd like to ask your suggestions about a reliable admin software > > > which is able to compare two dabases and generate a schema > > > synchrinizer script. > > > > There is no such thing, and there is no prospect of there ever > > being such a thing, because the database does not contain enough > > information to create this automatically. The problem exists at > > the organizational level, and needs to be solved there. > > > > While I would agree that these tools can't solve organizational > problems, they do exist: > > http://pgdiff.sourceforge.net/ > http://apgdiff.sourceforge.net/ > http://www.dbsolo.com/ > http://sqlmanager.net/en/products/postgresql/dbcomparer > > there are others too... There exist tools which can transform an empty database schema into another. They fail at the real life use case of changing a schema that already has data in it because they can't tell a changed column from one that's been dropped and another added, so I stand by my original contention :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
>-----Original Message----- >From: David Fetter [mailto:david@fetter.org] >Sent: Tuesday, January 20, 2009 4:44 PM >To: Csaba Együd >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Database schema & data synchronizer software for >PostgreSQL? > >On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: >> Hi, >> I'd like to ask your suggestions about a reliable admin software >> which is able to compare two dabases and generate a schema >> synchrinizer script. > >There is no such thing, and there is no prospect of there ever being >such a thing, because the database does not contain enough information >to create this automatically. The problem exists at the >organizational level, and needs to be solved there. > >> It would be nice to be able to generate data synchronization script >> for only the selected tables, and other features. > >Yes, you should definitely do that and store the scripts to do it in >your source code management system along with all the rest of the >deploy and upgrade scripts. They can't be generated automatically >either. > David, I see your points and generally can agree with, but there is a level which can be automated - I mean a mechanic comparison. Of course the result sync script must be tested before applying in production environment. These tools can/could save a lot of time. In my opinion the result, this way or that way, would be the same: a version migration or sync script to attach to the upgrade package. I think the difference is that I do not have to maintain a db script during the development to keep it up to date. I simply concentrate on the task not the administration. I may be wrong... Up to now I've been doing it the manual way but it makes me really non-effective. Thank you, -- Csaba
>-----Original Message----- >From: Robert Treat [mailto:xzilla@users.sourceforge.net] >Sent: Wednesday, January 21, 2009 3:51 AM >To: pgsql-general@postgresql.org >Cc: David Fetter; Csaba Együd >Subject: Re: [GENERAL] Database schema & data synchronizer software for >PostgreSQL? > >On Tuesday 20 January 2009 10:44:06 David Fetter wrote: >> On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: >> > Hi, >> > I'd like to ask your suggestions about a reliable admin software >> > which is able to compare two dabases and generate a schema >> > synchrinizer script. >> >> There is no such thing, and there is no prospect of there ever being >> such a thing, because the database does not contain enough information >> to create this automatically. The problem exists at the >> organizational level, and needs to be solved there. >> > >While I would agree that these tools can't solve organizational problems, they >do exist: > >http://pgdiff.sourceforge.net/ >http://apgdiff.sourceforge.net/ >http://www.dbsolo.com/ >http://sqlmanager.net/en/products/postgresql/dbcomparer Robert, Thank you for your suggestions. I will glace at them. -- Csaba
On Jan 20, 2009, at 11:27 PM, Együd Csaba wrote: >> -----Original Message----- >> From: Robert Treat [mailto:xzilla@users.sourceforge.net] >> Sent: Wednesday, January 21, 2009 3:51 AM >> To: pgsql-general@postgresql.org >> Cc: David Fetter; Csaba Együd >> Subject: Re: [GENERAL] Database schema & data synchronizer software >> for >> PostgreSQL? >> >> On Tuesday 20 January 2009 10:44:06 David Fetter wrote: >>> On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: >>>> Hi, >>>> I'd like to ask your suggestions about a reliable admin software >>>> which is able to compare two dabases and generate a schema >>>> synchrinizer script. >>> >>> There is no such thing, and there is no prospect of there ever being >>> such a thing, because the database does not contain enough >>> information >>> to create this automatically. The problem exists at the >>> organizational level, and needs to be solved there. >>> >> >> While I would agree that these tools can't solve organizational >> problems, > they >> do exist: >> >> http://pgdiff.sourceforge.net/ >> http://apgdiff.sourceforge.net/ >> http://www.dbsolo.com/ >> http://sqlmanager.net/en/products/postgresql/dbcomparer > > Robert, > Thank you for your suggestions. I will glace at them. > -- Csaba > dbsolo does a decent job. I think they main thing here is to check for inconsistencies and see what they are, rather then a tool that takes over the administrative task. We all understand David's point of view, but it can't hurt to have a tool in place that can verify the consistency of both schema's. Ries
>dbsolo does a decent job.
>I think they main thing here is to check for inconsistencies and see
>what they are, rather
>then a tool that takes over the administrative task.
>
>We all understand David's point of view, but it can't hurt to have a
>tool in place that can verify the consistency of both schema's.
>
>Ries
>
Ries,
I meant definitely this, thanks.
DB Solo is really does a nice job. I found only two minor problems in the
sync script:
1. It put a create trigger... statement before it created the
trigger function itself -
moved to the end of the script
2. I tried to alter column a time column to timestampz which
resulted in an error
message.
I corrected these two things and the database is OK now. I'm absolutely
happy with this level of automation.
Thank you all for your suggestions,
--Csaba
On Wed, Jan 21, 2009 at 05:18:57AM +0100, Együd Csaba wrote: > >From: David Fetter [mailto:david@fetter.org] > >On Tue, Jan 20, 2009 at 03:03:33PM +0100, Csaba Együd wrote: > >> Hi, > >> I'd like to ask your suggestions about a reliable admin software > >> which is able to compare two dabases and generate a schema > >> synchrinizer script. > > > >There is no such thing, and there is no prospect of there ever > >being such a thing, because the database does not contain enough > >information to create this automatically. The problem exists at > >the organizational level, and needs to be solved there. > > > >> It would be nice to be able to generate data synchronization > >> script for only the selected tables, and other features. > > > >Yes, you should definitely do that and store the scripts to do it > >in your source code management system along with all the rest of > >the deploy and upgrade scripts. They can't be generated > >automatically either. > > David, > I see your points and generally can agree with, but there is a level > which can be automated - I mean a mechanic comparison. That level isn't terribly high, and in my experience, it is not a worthwhile endeavor because it does not solve the actual problem. > Of course the result sync script must be tested before applying in > production environment. These tools can/could save a lot of time. No. What saves time is getting your development and deployment processes to the point where you're not needing to figure out what's happened. Instead, you'll be doing database changes *only* with scripts, which you'll test, etc., etc., rather than trying to reverse engineer your own stuff. Reverse engineering is what you do, and then only in an emergency, to *others'* software, not *yours.* > In my opinion the result, this way or that way, would be the same: a > version migration or sync script to attach to the upgrade package. > I think the difference is that I do not have to maintain a db script > during the development to keep it up to date. I simply concentrate > on the task not the administration. I may be wrong... You're right, in that you're wrong on this. You need to take your development process in hand and then keep it so. > Up to now I've been doing it the manual way but it makes me really > non-effective. What's *really* ineffective is continuing as you've been doing. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate