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.

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