Thread: Comparing 2 databases

On Tue, Dec 29, 2009 at 11:43:58AM -0500, akp geek wrote:
>    Hi all -
>
>              I have postgres running on 2 servers. one production and one
>    testing. What would be the best way to compare the 2 database, so find out
>    the differences? Can you please advice?
>
>    regards

That depends on what you mean by "compare". check_postgres[1] has a schema
comparison action you can use.

[1] http://bucardo.org/wiki/Check_postgres

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

On Tue, Dec 29, 2009 at 03:21:18PM -0500, akp geek wrote:
>    thanks for the repsonse. I appreciate it. are there any limitations on
>    using this one?  Means that we have to the same user on both databases and
>    same passwords.
>
>    I have used the command following way
>
>      check_postgres.pl --action=same_schema -H 172.xxxx  -p 1550
>    --db=myProdDB  --dbuser=prodUser  --dbpass=prodPwd  --dbhost2=172.xxxxx
>    --db=testDB  --dbuser=testUser  --dbpass=testPwd  --verbose >
>    difference.txt
>
>    what happend was , it complained about the password, then I tried
>    replacing the testPwd with prodPwd, then it started executing. but it
>    prompted for password for testuser. that's where I got confused

You might try a pgpass file[1] and skip providing the passwords on the command
line.

>    One question I have is, is there an option to specify schema also

Check the docs under BASIC FILTERING[2].  You can tell it to ignore objects
with certain names, or to include only those objects with the given names.

[1] http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html
[2] http://bucardo.org/check_postgres/check_postgres.pl.html#basic_filtering

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Also check this out Very interesting – it can compare data between the DBs (tables/views). Check this out –

http://www.zidsoft.com/

http://www.zidsoft.com/screenshots.html

Thanks

Deepak

Le 29/12/2009 21:21, akp geek a écrit :
> thanks for the repsonse. I appreciate it. are there any limitations on using
> this one?  Means that we have to the same user on both databases and same
> passwords.
>
> I have used the command following way
>
>   check_postgres.pl --action=same_schema -H 172.xxxx  -p 1550
> --db=myProdDB  --dbuser=prodUser  --dbpass=prodPwd  --dbhost2=172.xxxxx
> --db=testDB  --dbuser=testUser  --dbpass=testPwd  --verbose > difference.txt
>
> what happend was , it complained about the password, then I tried replacing
> the testPwd with prodPwd, then it started executing. but it prompted for
> password for testuser. that's where I got confused
>

You give --dbuser and other options twice. You should probably do this:

check_postgres.pl --action=same_schema \
  -H 172.xxxx -p 1550 \
  --db=myProdDB --dbuser=prodUser  --dbpass=prodPwd
  --dbhost2=172.xxxxx \
  --db2=testDB  --dbuser2=testUser --dbpass2=testPwd \
  --verbose > difference.txt



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

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