Creating a PostgreSQL Database Diagram
Not for the first time, I was trying this morning to create a database diagram from a PostgreSQL database on my Windows laptop. Unfortunately I was unable to find anything useful online, so after succeeding in what I wanted to do, I decided to share it with the world on this blog 😉 Especially since it was remarkably easy to achieve.
So below you can find a description on how to create a database diagram from an existing PostgreSQL database in MS Visio 2007 in 3 simple steps.
1) Installing the PostgreSQL ODBC driver : psqlODBC
Before you can connect to your PostgreSQL database from Visio, you need to install the PostgreSQL ODBC-driver. Simply download the latest psqlODBC driver from http://www.postgresql.org/ftp/odbc/versions/msi/ and run the installer.
2) Creating a User DSN
Then you will need to create a new User DSN (Data Source Name) in Windows to refer to in Visio. Go to Control Panel -> Administrative tools -> Data sources (ODBC) and add a new User DSN.
After filling in the correct parameters I suggest you click the test-button to check everything is OK. If so, you can proceed to the last step.
3) Configuring MS Visio
Open Visio2007, create a new ‘Database Model Diagram’ and choose “Reverse Engineer …” from the ‘Database menu’. Select the ODBC Generic Driver …
Then click “Setup …” to get the dialog in which you need to select ‘PostgreSQL Unicode’ again and of course the User DSN you just created :
Click OK and Next to continue and after filling in the correct username and password, you will be given a few straightforward options for the diagram creation. Your diagram should then appear after only a few moments.
That’s it, you can add some Visio markup as you like, for instance by colouring some important tables, like I did in the example. Good luck !