Quantcast

Comparing 2 databases

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Comparing 2 databases

Bala Venkat
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing 2 databases

Joshua Tolley
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

signature.asc (204 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing 2 databases

Bala Venkat
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 <[hidden email]> wrote:
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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAks6USQACgkQRiRfCGf1UMOvoQCgm5R9XioQ8mKcw2sDkYtW8SbO
k3gAn3jDp/xhzHjQkE0O2MCHVcYrQlLL
=dwE1
-----END PGP SIGNATURE-----


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing 2 databases

Joshua Tolley
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

signature.asc (204 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing 2 databases

dm.aeqa

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 <[hidden email]> 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] 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAks6oNcACgkQRiRfCGf1UMOQVgCghRaU2VCwyXNg0KbkqI/FhA9J
xpoAn2RJRSmJmbgybRytNjo0ZiPNruL4
=Lk0m
-----END PGP SIGNATURE-----


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing 2 databases

Guillaume Lelarge-3
In reply to this post by Bala Venkat
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Comparing 2 databases

sualeh.fatehi
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Loading...