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]http://bucardo.org/wiki/Check_postgres 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

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

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

Thanks once again

Regards

On Tue, Dec 29, 2009 at 1:57 PM, Joshua Tolley <eggyknap@gmail.com> wrote:

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]http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html 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]http://bucardo.org/check_postgres/check_postgres.pl.html#basic_filtering. 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

On Tue, Dec 29, 2009 at 4:37 PM, Joshua Tolley <eggyknap@gmail.com> wrote:

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