How to check difference between two databases in PostgreSQL?
68
Is it possible to compare two databases with identical structure?
Let say that I have two databases DB1 and DB2 and I want to check if there is a difference in data between them.
postgresql schema-compare
edited Oct 3 '18 at 8:36
lospejos
1,45621426
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
add a comment |
68
Is it possible to compare two databases with identical structure?
Let say that I have two databases DB1 and DB2 and I want to check if there is a difference in data between them.
postgresql schema-compare
edited Oct 3 '18 at 8:36
lospejos
1,45621426
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
See also stackoverflow.com/q/2178907/453605
– Marcello Nuccio
Nov 19 '15 at 16:42
dbForge Data Compare for PostgreSQL solves such issues.
– Devart
Oct 24 '17 at 9:07
add a comment |
68
68
68
30
Is it possible to compare two databases with identical structure?
Let say that I have two databases DB1 and DB2 and I want to check if there is a difference in data between them.
postgresql schema-compare
edited Oct 3 '18 at 8:36
lospejos
1,45621426
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
Is it possible to compare two databases with identical structure?
Let say that I have two databases DB1 and DB2 and I want to check if there is a difference in data between them.
postgresql schema-compare
postgresql schema-compare
edited Oct 3 '18 at 8:36
lospejos
1,45621426
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
edited Oct 3 '18 at 8:36
lospejos
1,45621426
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
edited Oct 3 '18 at 8:36
lospejos
1,45621426
edited Oct 3 '18 at 8:36
lospejos
1,45621426
edited Oct 3 '18 at 8:36
lospejos
1,45621426
1,45621426
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
asked Jan 26 '11 at 12:54
senninsennin
2,84982443
2,84982443
See also stackoverflow.com/q/2178907/453605
– Marcello Nuccio
Nov 19 '15 at 16:42
dbForge Data Compare for PostgreSQL solves such issues.
– Devart
Oct 24 '17 at 9:07
add a comment |
See also stackoverflow.com/q/2178907/453605
– Marcello Nuccio
Nov 19 '15 at 16:42
dbForge Data Compare for PostgreSQL solves such issues.
– Devart
Oct 24 '17 at 9:07
See also stackoverflow.com/q/2178907/453605
– Marcello Nuccio
Nov 19 '15 at 16:42
See also stackoverflow.com/q/2178907/453605
– Marcello Nuccio
Nov 19 '15 at 16:42
dbForge Data Compare for PostgreSQL solves such issues.
– Devart
Oct 24 '17 at 9:07
dbForge Data Compare for PostgreSQL solves such issues.
– Devart
Oct 24 '17 at 9:07
add a comment |
7 Answers
7
active
oldest
votes
59
There are several tools out there:
Free Ones:
- pgquarrel: http://eulerto.github.io/pgquarrel
- apgdiff: http://apgdiff.com/
- Liquibase (Cross DBMS): http://www.liquibase.org
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra: https://migra.djrobstep.com/
Commercial:
- DB Comparer: http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua Data Studio: http://docs.aquafold.com/docs-diff-schema.html
- DB Solo: http://www.dbsolo.com/index.html (30 day trial)
- PostgresCompare: https://www.postgrescompare.com/
edited Jan 1 at 7:02
Neil Anderson
826618
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
3
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
11
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
2
It seems thatapgdiffdoes not support inherit tables well, and exception throws immediately when I trying to use it.WbSchemaDiffworks very well, surprise!
– smartwjw
Dec 30 '15 at 8:26
1
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
7
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
|
show 5 more comments
22
Try using pg_dump on both databases and diffing the files.
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
19
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
4
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
8
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations andpg_dumpis fine for that. If you see significant diffs withpg_dumpyou're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.
– sas
Jan 16 '18 at 8:16
add a comment |
8
Another free app:
DBeaver - you can select databases, tables, etc to compare with each other
answered Sep 21 '16 at 12:57
ikiiki
9112
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
add a comment |
3
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
Postgres Compare
Initially this is just schema (DDL) comparison but we will probably expand to data also. I believe this is a tool that a lot of shops require in order to move from their current RDBMS without having to also change how their development environments, operations etc work too.
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
1
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
add a comment |
2
The best tool which I ever seen
https://pythonhosted.org/Pyrseas/
Get dump from database A
dbtoyaml ...Generate migration from A => B
yamltodb ... [file generated in step 1]
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
add a comment |
0
I'm also looking for a tool to compare data in databases (in particular I was interested in comparing Redshift DB).
So far the best I found is https://www.dbbest.com/products/database-compare-suite/#close.
Unfortunately the free trial expires after one day.
answered Dec 27 '16 at 14:08
nicolanicola
573721
add a comment |
0
I recommend DB Best Database Compare Suite - trial version is only for 2 days, but it allows you to compare scheme and data.
Read more on official product website.
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f4804779%2fhow-to-check-difference-between-two-databases-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
7 Answers
7
active
oldest
votes
7 Answers
7
active
oldest
votes
active
oldest
votes
active
oldest
votes
59
There are several tools out there:
Free Ones:
- pgquarrel: http://eulerto.github.io/pgquarrel
- apgdiff: http://apgdiff.com/
- Liquibase (Cross DBMS): http://www.liquibase.org
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra: https://migra.djrobstep.com/
Commercial:
- DB Comparer: http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua Data Studio: http://docs.aquafold.com/docs-diff-schema.html
- DB Solo: http://www.dbsolo.com/index.html (30 day trial)
- PostgresCompare: https://www.postgrescompare.com/
edited Jan 1 at 7:02
Neil Anderson
826618
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
3
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
11
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
2
It seems thatapgdiffdoes not support inherit tables well, and exception throws immediately when I trying to use it.WbSchemaDiffworks very well, surprise!
– smartwjw
Dec 30 '15 at 8:26
1
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
7
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
|
show 5 more comments
59
There are several tools out there:
Free Ones:
- pgquarrel: http://eulerto.github.io/pgquarrel
- apgdiff: http://apgdiff.com/
- Liquibase (Cross DBMS): http://www.liquibase.org
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra: https://migra.djrobstep.com/
Commercial:
- DB Comparer: http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua Data Studio: http://docs.aquafold.com/docs-diff-schema.html
- DB Solo: http://www.dbsolo.com/index.html (30 day trial)
- PostgresCompare: https://www.postgrescompare.com/
edited Jan 1 at 7:02
Neil Anderson
826618
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
3
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
11
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
2
It seems thatapgdiffdoes not support inherit tables well, and exception throws immediately when I trying to use it.WbSchemaDiffworks very well, surprise!
– smartwjw
Dec 30 '15 at 8:26
1
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
7
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
|
show 5 more comments
59
59
59
There are several tools out there:
Free Ones:
- pgquarrel: http://eulerto.github.io/pgquarrel
- apgdiff: http://apgdiff.com/
- Liquibase (Cross DBMS): http://www.liquibase.org
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra: https://migra.djrobstep.com/
Commercial:
- DB Comparer: http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua Data Studio: http://docs.aquafold.com/docs-diff-schema.html
- DB Solo: http://www.dbsolo.com/index.html (30 day trial)
- PostgresCompare: https://www.postgrescompare.com/
edited Jan 1 at 7:02
Neil Anderson
826618
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
There are several tools out there:
Free Ones:
- pgquarrel: http://eulerto.github.io/pgquarrel
- apgdiff: http://apgdiff.com/
- Liquibase (Cross DBMS): http://www.liquibase.org
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra: https://migra.djrobstep.com/
Commercial:
- DB Comparer: http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua Data Studio: http://docs.aquafold.com/docs-diff-schema.html
- DB Solo: http://www.dbsolo.com/index.html (30 day trial)
- PostgresCompare: https://www.postgrescompare.com/
edited Jan 1 at 7:02
Neil Anderson
826618
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
edited Jan 1 at 7:02
Neil Anderson
826618
edited Jan 1 at 7:02
Neil Anderson
826618
edited Jan 1 at 7:02
Neil Anderson
826618
826618
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
answered Jan 26 '11 at 13:04
a_horse_with_no_namea_horse_with_no_name
299k46457549
299k46457549
3
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
11
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
2
It seems thatapgdiffdoes not support inherit tables well, and exception throws immediately when I trying to use it.WbSchemaDiffworks very well, surprise!
– smartwjw
Dec 30 '15 at 8:26
1
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
7
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
|
show 5 more comments
3
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
11
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
2
It seems thatapgdiffdoes not support inherit tables well, and exception throws immediately when I trying to use it.WbSchemaDiffworks very well, surprise!
– smartwjw
Dec 30 '15 at 8:26
1
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
7
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
3
3
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
+1 apgdiff works for me
– A-K
Jan 31 '14 at 15:53
11
11
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema.
– Amir Ali Akbari
Jan 6 '15 at 16:49
2
2
It seems that
apgdiff does not support inherit tables well, and exception throws immediately when I trying to use it. WbSchemaDiff works very well, surprise!– smartwjw
Dec 30 '15 at 8:26
It seems that
apgdiff does not support inherit tables well, and exception throws immediately when I trying to use it. WbSchemaDiff works very well, surprise!– smartwjw
Dec 30 '15 at 8:26
1
1
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure.
– aditsu
May 13 '16 at 13:56
7
7
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
-1. OP was asking about comparing data (records/rows) and you've mostly listed tools that compare structure, which OP explicitly stated was identical between the target databases, ergo no reason to compare them. Please clarify which ones actually do the requested thing.
– SeldomNeedy
Jun 21 '17 at 20:46
|
show 5 more comments
22
Try using pg_dump on both databases and diffing the files.
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
19
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
4
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
8
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations andpg_dumpis fine for that. If you see significant diffs withpg_dumpyou're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.
– sas
Jan 16 '18 at 8:16
add a comment |
22
Try using pg_dump on both databases and diffing the files.
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
19
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
4
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
8
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations andpg_dumpis fine for that. If you see significant diffs withpg_dumpyou're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.
– sas
Jan 16 '18 at 8:16
add a comment |
22
22
22
Try using pg_dump on both databases and diffing the files.
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
Try using pg_dump on both databases and diffing the files.
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
answered Jan 26 '11 at 12:56
Júlio SantosJúlio Santos
3,13212047
3,13212047
19
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
4
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
8
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations andpg_dumpis fine for that. If you see significant diffs withpg_dumpyou're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.
– sas
Jan 16 '18 at 8:16
add a comment |
19
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
4
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
8
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations andpg_dumpis fine for that. If you see significant diffs withpg_dumpyou're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.
– sas
Jan 16 '18 at 8:16
19
19
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.)
– Mike Sherrill 'Cat Recall'
Jan 31 '11 at 1:36
4
4
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
We don't. Better yet, I don't. Good point.
– Júlio Santos
Jan 31 '11 at 18:11
8
8
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking.
– Cem Güler
Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations and
pg_dump is fine for that. If you see significant diffs with pg_dump you're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.– sas
Jan 16 '18 at 8:16
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations and
pg_dump is fine for that. If you see significant diffs with pg_dump you're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs.– sas
Jan 16 '18 at 8:16
add a comment |
8
Another free app:
DBeaver - you can select databases, tables, etc to compare with each other
answered Sep 21 '16 at 12:57
ikiiki
9112
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
add a comment |
8
Another free app:
DBeaver - you can select databases, tables, etc to compare with each other
answered Sep 21 '16 at 12:57
ikiiki
9112
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
add a comment |
8
8
8
Another free app:
DBeaver - you can select databases, tables, etc to compare with each other
answered Sep 21 '16 at 12:57
ikiiki
9112
Another free app:
DBeaver - you can select databases, tables, etc to compare with each other
answered Sep 21 '16 at 12:57
ikiiki
9112
answered Sep 21 '16 at 12:57
ikiiki
9112
answered Sep 21 '16 at 12:57
ikiiki
9112
answered Sep 21 '16 at 12:57
ikiiki
9112
9112
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
add a comment |
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
Could you please explain better how to compare data from 2 databases with DBeaver?
– nicola
Nov 16 '16 at 11:30
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
As far as I know DBeaver only allows metadata comparison, not data comparison.
– nicola
Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option.
– ihebiheb
Mar 2 '18 at 9:46
add a comment |
3
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
Postgres Compare
Initially this is just schema (DDL) comparison but we will probably expand to data also. I believe this is a tool that a lot of shops require in order to move from their current RDBMS without having to also change how their development environments, operations etc work too.
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
1
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
add a comment |
3
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
Postgres Compare
Initially this is just schema (DDL) comparison but we will probably expand to data also. I believe this is a tool that a lot of shops require in order to move from their current RDBMS without having to also change how their development environments, operations etc work too.
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
1
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
add a comment |
3
3
3
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
Postgres Compare
Initially this is just schema (DDL) comparison but we will probably expand to data also. I believe this is a tool that a lot of shops require in order to move from their current RDBMS without having to also change how their development environments, operations etc work too.
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
Postgres Compare
Initially this is just schema (DDL) comparison but we will probably expand to data also. I believe this is a tool that a lot of shops require in order to move from their current RDBMS without having to also change how their development environments, operations etc work too.
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
answered Apr 21 '17 at 17:06
Neil AndersonNeil Anderson
826618
826618
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
1
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
add a comment |
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
1
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
Data is also very important. Schema alone isn't enough.
– Houman
May 17 '17 at 9:38
1
1
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them.
– Neil Anderson
Jun 2 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version
– Avantika Saini
Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody.
– Neil Anderson
Aug 23 '18 at 0:58
add a comment |
2
The best tool which I ever seen
https://pythonhosted.org/Pyrseas/
Get dump from database A
dbtoyaml ...Generate migration from A => B
yamltodb ... [file generated in step 1]
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
add a comment |
2
The best tool which I ever seen
https://pythonhosted.org/Pyrseas/
Get dump from database A
dbtoyaml ...Generate migration from A => B
yamltodb ... [file generated in step 1]
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
add a comment |
2
2
2
The best tool which I ever seen
https://pythonhosted.org/Pyrseas/
Get dump from database A
dbtoyaml ...Generate migration from A => B
yamltodb ... [file generated in step 1]
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
The best tool which I ever seen
https://pythonhosted.org/Pyrseas/
Get dump from database A
dbtoyaml ...Generate migration from A => B
yamltodb ... [file generated in step 1]
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
answered Sep 24 '17 at 16:39
Oleg TsarevOleg Tsarev
1417
1417
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
add a comment |
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project.
– andreav
Apr 4 '18 at 5:53
add a comment |
0
I'm also looking for a tool to compare data in databases (in particular I was interested in comparing Redshift DB).
So far the best I found is https://www.dbbest.com/products/database-compare-suite/#close.
Unfortunately the free trial expires after one day.
answered Dec 27 '16 at 14:08
nicolanicola
573721
add a comment |
0
I'm also looking for a tool to compare data in databases (in particular I was interested in comparing Redshift DB).
So far the best I found is https://www.dbbest.com/products/database-compare-suite/#close.
Unfortunately the free trial expires after one day.
answered Dec 27 '16 at 14:08
nicolanicola
573721
add a comment |
0
0
0
I'm also looking for a tool to compare data in databases (in particular I was interested in comparing Redshift DB).
So far the best I found is https://www.dbbest.com/products/database-compare-suite/#close.
Unfortunately the free trial expires after one day.
answered Dec 27 '16 at 14:08
nicolanicola
573721
I'm also looking for a tool to compare data in databases (in particular I was interested in comparing Redshift DB).
So far the best I found is https://www.dbbest.com/products/database-compare-suite/#close.
Unfortunately the free trial expires after one day.
answered Dec 27 '16 at 14:08
nicolanicola
573721
answered Dec 27 '16 at 14:08
nicolanicola
573721
answered Dec 27 '16 at 14:08
nicolanicola
573721
answered Dec 27 '16 at 14:08
nicolanicola
573721
573721
add a comment |
add a comment |
0
I recommend DB Best Database Compare Suite - trial version is only for 2 days, but it allows you to compare scheme and data.
Read more on official product website.
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
add a comment |
0
I recommend DB Best Database Compare Suite - trial version is only for 2 days, but it allows you to compare scheme and data.
Read more on official product website.
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
add a comment |
0
0
0
I recommend DB Best Database Compare Suite - trial version is only for 2 days, but it allows you to compare scheme and data.
Read more on official product website.
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
I recommend DB Best Database Compare Suite - trial version is only for 2 days, but it allows you to compare scheme and data.
Read more on official product website.
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
answered Oct 29 '18 at 8:55
1_bug1_bug
2,87522633
2,87522633
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f4804779%2fhow-to-check-difference-between-two-databases-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
See also stackoverflow.com/q/2178907/453605
– Marcello Nuccio
Nov 19 '15 at 16:42
dbForge Data Compare for PostgreSQL solves such issues.
– Devart
Oct 24 '17 at 9:07