This tool compares two PostgreSQL database and generate the script to adapt the target database.
It is based on the excellent pgdiff from Jon Carlson but, as pgdiff is in GO, it doesn't fit nicely in Microsoft oriented places.
Based on this tool, I kept the same order for analyzing diffs between databases. I added analysis for comments.
The order of analysis is :
- Group roles
- Schema
- Sequence
- Table
- Column
- Index
- View
- Comment
- Functions
- Foreign key
- Owner
- Grant
- Trigger
This tool doesn't analyse LOGIN roles because it's impossible to script the password. If you still need it, simply comment the dedicated section in the GroupRole class. Due to the way it's processed, it may need multiple runs to completely prepare the migration script.
PostgreSQL being what it is, dependencies between views are enforced and if you modify a view that's the source of another view, you will have to drop and recreate both view in the good order. As of now, ComparePG can find dependencies on 1 level and warns you when it find it with the message
--!!!! ATTENTION This view is used by other views !
--[List of views]
An executable version is available.
ComparePG.exe {parameter}=[value]... [Items]
SOURCE :
SRVR_S : server
BASE_S : database
PORT_S : port
SCHM_S : schema
LGN_S : login
PWD_S : password
TARGET :
SRVR_T : server
BASE_T : database
PORT_T : port
SCHM_T : schema
LGN_T : login
PWD_T : password
Items
Any of the caracter representing an item comparison. If none given ALL will be assumed.
g : group roles
s : schema
S : sequence
t : table
c : column
i : index
v : view
C : comment
f : functions
F : foreign key
o : owner
G : grant
T : trigger
ex :
ComparePG.exe SRVR_S=localhost BASE_S=bd1 PORT_S=5432 SCHM_S=public LGN_S=user1 PWD_S=***** SRVR_T=server BASE_T=bd1 PORT_T=5433 SCHM_T=public LGN_T=user1 PWD_T=*****