PGDiff – PostgreSQL schema diff

PGDiff compares the schema between two PostgreSQL  databases and generates alter statements to be manually run against the second database to make them match.

 

Download files here : Here
Extract the files to your bin folder in pgadmin; default  (C:\ChemoLIMS\data\bin)

 
 
 
How to use:

 

1.Open command prompt.

2.Change directory to pgadmin bin by typing(copy) the following. Your pgadmin directory may not the same as below.

cd C:\\ChemoLIMS\\data\\bin
[image_with_text image=’https://docs.chemoinformatics.com/wp-content/uploads/2020/06/1.png’ title=” title_color=” title_tag=”] [/image_with_text]

3. Use the following example to find the different between two tables

pgdiff -U postgres -W postgres -H localhost -P 5432 -D chemolims_hrc -S public -O “sslmode=disable” -u postgres -w postgres -h localhost -p 5432 -d chemolims_raub -s public -o “sslmode=disable” COLUMN >C:\Users\AhmadRaimi\Desktop\column.txt

 

[image_with_text image=’https://docs.chemoinformatics.com/wp-content/uploads/2020/06/2.png’ title=” title_color=” title_tag=”] [/image_with_text]

Options	Explanation
-V, –version	prints the version of pgdiff being used
-?, –help	displays helpful usage information
-U, –user1	first postgres user
-u, –user2	second postgres user
-W, –password1	first db password
-w, –password2	second db password
-H, –host1	first db host. default is localhost
-h, –host2	second db host. default is localhost
-P, –port1	first db port number. default is 5432
-p, –port2	second db port number. default is 5432
-D, –dbname1	first db name
-d, –dbname2	second db name
-S, –schema1	first schema name. default is * (all non-system schemas)
-s, –schema2	second schema name. default is * (all non-system schemas)
-O, –option1	first db options. example: sslmode=disable
-o, –option2	second db options. example: sslmode=disable

 
 
Basically you only need to change the text in these color, bracket come from example above;
 
Green = The main database name (chemolims_hrc)
Magenta = The database you going to compare (chemolims_raub)
Black = Type of compare (column) ; refer below
Purple = Where you going to save the scripts. Change the name before .txt if you going to save multiple comparison. Otherwise it will overwrite
( > C:\Users\AhmadRaimi\Desktop\column.txt )

 
 
Comparison option :

1. SCHEMA
2. ROLE
3. SEQUENCE
4. TABLE
5. COLUMN
6. INDEX
7. VIEW
8. FOREIGN_KEY
9. FUNCTION
10. TRIGGER
11. OWNER
12. GRANT_RELATIONSHIP
13. GRANT_ATTRIBUTE

 
 
As per our Chemolims database, it recommended to compare TABLE followed by COLUMN and lastly FUNCTION.
The rest are optional.