Postgres Pro
Downloads
Home   >   mailing lists

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

A compare and/or sync. database structure?

From
"durumdara@gmail.com"
Date:
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/

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

From
Guillaume Lelarge
Date:
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

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

From
"durumdara@gmail.com"
Date:
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/

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

From
Sualeh Fatehi
Date:
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