A compare and/or sync. database structure?

Started by Durumdaraover 16 years ago4 messagesgeneral

durumdara@gmail.com

over 16 years ago

Hi!

I wanna ask that which pg_dump command line needed if I want to compare
two databases?

I have many DBs, but all of them have two versions a development (local)
DB and the online (web) DB.
When I want to realize a new function, I develop it locally, and later I
sync. it into online DB.

But it is hard: I easily miss some things... :-(

Because of that I want to write some tool that can compare databases.
In DBISAM world I can do it easily, because DBISAM tables very-very near
to Delphi DataSets, and I can compare tables/indexes with Delphi
comparations.

In Firebird it was harder, because if must compare tables, and later other
objects (generators) too... :-(

In PostgreSQL I have two way to compare DBs.

1.) pg_dumps, and textdiff.
2.) get schema (table struct, indexes, etc) from all, and compare them.

The first version is need user SQLs, the second can create alter/create
SQLs automatically...

So please help me with your experience: what is the best solution, what is
the possible problem that make mistakes with this plan, and how to realize
it easily?

Thanks for your help:
dd

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Guillaume Lelarge

guillaume@lelarge.info

over 16 years ago

In reply to: Durumdara (#1)

Re: A compare and/or sync. database structure?

Le mercredi 5 août 2009 à 10:13:44, durumdara@gmail.com a écrit :

[...]
So please help me with your experience: what is the best solution, what is
the possible problem that make mistakes with this plan, and how to realize
it easily?

You can try check_postgres.pl Perl script. The same_schema action seems to be
what you need.

Here is a little example:

guillaume@laptop:~$ createdb db1
guillaume@laptop:~$ psql -c "CREATE TABLE t1(id integer);" db1
CREATE TABLE
guillaume@laptop:~$ psql -c "CREATE TABLE t2(id integer, c text);" db1
CREATE TABLE
guillaume@laptop:~$ createdb db2
guillaume@laptop:~$ psql -c "CREATE TABLE t1(id integer);" db2
CREATE TABLE

So, db1 with two tables and db2 with one only.

guillaume@laptop:~$ LANG=C check_postgres.pl --action same_schema --dbname db1
--dbname2 db2
POSTGRES_SAME_SCHEMA CRITICAL: DB "db1 => db2" Databases were different. Items
not matched: 1 | time=0.01 Table in 1 but not 2: public.t2

It works. Now I add the missing table:

guillaume@laptop:~$ psql -c "CREATE TABLE t2(id integer, c text);" db2
CREATE TABLE
guillaume@laptop:~$ LANG=C check_postgres.pl --action same_schema --dbname db1
--dbname2 db2
POSTGRES_SAME_SCHEMA OK: DB "db1 => db2" Both databases have identical items |
time=0.01

Works too. Works great actually :)

It works also with the other objects of the database.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

durumdara@gmail.com

over 16 years ago

In reply to: Guillaume Lelarge (#2)

Re: A compare and/or sync. database structure?

Hi!

Thanks...

Sorry, but this is not good solution for me, because of the webserver is
not opened (port) to the net, and I must compare local, a web dbs.

So I try to find a solution, that can create a "map" from structure in
XML, text, etc., and I can get it with ftp and compare it...

First I search for a simple tool that can compare dbs in textual way...

dd

On Wed, 05 Aug 2009 10:49:53 +0200, Guillaume Lelarge
<guillaume@lelarge.info> wrote:

Le mercredi 5 août 2009 à 10:13:44, durumdara@gmail.com a écrit :

[...]
So please help me with your experience: what is the best solution, what
is
the possible problem that make mistakes with this plan, and how to
realize
it easily?

You can try check_postgres.pl Perl script. The same_schema action seems
to be
what you need.

Here is a little example:

guillaume@laptop:~$ createdb db1
guillaume@laptop:~$ psql -c "CREATE TABLE t1(id integer);" db1
CREATE TABLE
guillaume@laptop:~$ psql -c "CREATE TABLE t2(id integer, c text);" db1
CREATE TABLE
guillaume@laptop:~$ createdb db2
guillaume@laptop:~$ psql -c "CREATE TABLE t1(id integer);" db2
CREATE TABLE

So, db1 with two tables and db2 with one only.

guillaume@laptop:~$ LANG=C check_postgres.pl --action same_schema
--dbname db1
--dbname2 db2
POSTGRES_SAME_SCHEMA CRITICAL: DB "db1 => db2" Databases were different.
Items
not matched: 1 | time=0.01 Table in 1 but not 2: public.t2

It works. Now I add the missing table:

guillaume@laptop:~$ psql -c "CREATE TABLE t2(id integer, c text);" db2
CREATE TABLE
guillaume@laptop:~$ LANG=C check_postgres.pl --action same_schema
--dbname db1
--dbname2 db2
POSTGRES_SAME_SCHEMA OK: DB "db1 => db2" Both databases have identical
items |
time=0.01

Works too. Works great actually :)

It works also with the other objects of the database.

Regards.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

sualeh.fatehi@gmail.com

over 16 years ago

In reply to: Durumdara (#1)

Re: A compare and/or sync. database structure?

The free, open-source SchemaCrawler for SQL Server tool is desinged to
do just this. You can take human-readable snapshots of the schema and
data, for later comparison. Comparisons are done using a standard diff
tool such as WinMerge. SchemaCrawler outputs details of your schema
(tables, views, procedures, and more) in a diff-able plain-text format
(text, CSV, or XHTML). SchemaCrawler can also output data (including
CLOBs and BLOBs) in the same plain-text formats.

SchemaCrawler is available at SourceForge:
http://schemacrawler.sourceforge.net/

Sualeh Fatehi