ER-Mapping with OpenShift, Getting Started with pgModeler - Archived

Greetings Shifters!

I am an unabashed PostgreSQL fanboi. I am also someone who likes to design databases visually (ER Diagrams) since I can’t visualize anything beyond two tables and their relationships without pictures.

I want tools capabable of round-tripping with the database–meaning that they generate tables (or SQL) for the database or generate a diagram with a database (or SQL) as the input. Finally, tools must run on Fedora Linux since that’s my Operating System of choice. Bonus points awarded if the tool is FOSS.

During some unspeakably dismal times, when Windows was my main operating system, I had a MSDN subscription and Visio (plus some hand manipulation to make it work with PostgreSQL). Then I experienced the frustration of Dia for diagramming. Sometimes it exported SQL and sometimes it didn’t.

Recently I was looking for a new tool while beginning a new application to run on OpenShift and I discovered a gold mine – pgModeler! It meets all my criteria AND it’s a joy to use. Today’s post covers some of its coolest parts and how to get pgModeler working on OpenShift.

Cool features

Here’s the list of cool things I like about pgModeler (above and beyond my criteria above):

  1. It is cross platform, running on Linux, Mac, and Windows

  2. It has an intuitive interface and with all the features I want.

  3. It comes precompiled so for a small fee I get software that just works while taking on the pain of compiling if I want.

  4. When you make a connection to a DB, it interrogates the DB to know what types are available, so it works with POSTGIS!!

Here is a diagram I made to handle an application called FlatFluffy I’m working on:

Example DB Design

As you can see there is an outside box representing the DB schema titled “public” (I know, I know, I should use a schema other than “public”). Inside the schema you see all the tables, columns with their data types, primary key references, and any indices defined on the table. It also defines the relationships between the tables and the cardinality. Another cool feature pgModeler provides is for many-to-many relationships it auto-magically creates the table for you.

Getting pgModeler to work with OpenShift

Ok, I am assuming you have installed pgModeler, installed the OpenShift rhc command line tools, and created an application on OpenShift with a PostgreSQL database. The binary version of pgModeler is so easy to install–unzip and run–nothing to cover there. If you don’t have the OpenShift rhc client tools installed and setup, walk through these steps first.

I wrote a post about using PostgreSQL 9.2. Once this is all set the steps are rather easy:

  1. Run rhc app show -a {appname} at the command line on your local machine. You should see this information about your DB connection:
postgresql-9.2 (PostgreSQL 9.2)
  -------------------------------
    Gears:          1 large
    Connection URL: postgresql://$OPENSHIFT_POSTGRESQL_DB_HOST:$OPENSHIFT_POSTGRESQL_DB_PORT
    Database Name:  flatfluffy
    Password:       XXXXXXXXXXXX
    Username:       ZZZZZZZZZZZZ

Note the Database Name, Password, and Username – you will need these later when you connect pgModeler to your database.

  1. Set up port forwarding using the rhc command line tools. You could always do this manually if you understand how to do port-forwarding over SSH. But, if you want the easy button, like I do, use the command line tools. At the command prompt enter:
 rhc port-forward {appname}

or if you are in the local git repository for your application you can just do:

 rhc port-forward

After running this you should see something like:

Forwarding ports ...
 
To connect to a service running on OpenShift, use the Local address
 
Service    Local                OpenShift
---------- --------------- ---- ------------------------------------------------
haproxy    127.0.0.1:8080   =>  127.8.46.2:8080
haproxy    127.0.0.1:8081   =>  127.8.46.3:8080
java       127.0.0.1:3528   =>  127.8.46.1:3528
java       127.0.0.1:4447   =>  127.8.46.1:4447
java       127.0.0.1:5445   =>  127.8.46.1:5445
java       127.0.0.1:8082   =>  127.8.46.1:8080
java       127.0.0.1:9990   =>  127.8.46.1:9990
java       127.0.0.1:9999   =>  127.8.46.1:9999
postgresql 127.0.0.1:53841  =>  XXXXXXXXXXXX-thesteve0.rhcloud.com:53841
 
Press CTRL-C to terminate port forwarding

If you see messages about “Address already in use…” this means you have already something attached to that port locally and port-forward looks for another local port to bind to.

So now all those ports on the localhost address point to the ports inside of our application. As you can see, port-forward has mapped the remote PostgreSQL to 127.0.0.1:53841. If we want to use a local desktop application to connect to our remote PostgreSQL instance, we use this address and the Username and Password we got from step 1. And that is exactly what we do in the next step.

  1. Fire up pgModeler and make the connection to your port-forwarded database. Nothing is there yet but this allows pgModeler to see what data types are available. If you want to use PostGIS or any other available PostgreSQL extension, please enable them before you connect. Please refer to this blog to turn on extensions.

After starting up pgModeler click on the setting button on the left side menu.

Home screen for pgModeler on OpenShift

Once the settings dialog comes up, a blank connection dialog is ready to go. I know the top box has a connection for localhost but you should ignore it. As you can see in the picture below you must fill it in with the information collected in steps 1 and 2.

connection screen in pgModeler on OpenShift

“Connection Alias” can be anything that helps you remember what this connection is for.

All the other fields I changed came from the rhc app show and rhc port-forward information.

Click the test button to see if pgModeler can connect and see the database.

If it can, then click the apply button which closes the dialog.

  1. Now click on the import button on the left menu of the main page. Then go ahead and select the connection you made in step 3. Click the connect button highlighted in red in the picture below:

using the connection in the import dialog on OpenShift with pgModeler

This connects to the database and queries the databases available. Choose the database for your application.

using the connection in the import dialog for pgModeler

pgModeler automatically queries the DB for all the information about the DB, like users and schemas. Select any of the information you want imported and click the import button. I leave all the default options and click import.

At this point you have a blank page, or if there were tables and relationships already in your database there is a diagram.

From this point on reference the manual to understand how to work with pgModeler.

  1. After editing your diagram, you can export your model directly to the DB, to a SQL file, or to a diagram by pushing the export button.

Wrap Up

If you love PostgreSQL and you love to diagram for database design then I hope I’ve made your day. If you don’t love PostgreSQL but like to diagram database designs then I am sorry I made you so jealous. Either way today’s post should also help you to understand how to use OpenShift’s use of port-forwarding to connect desktop applications to your database.

I hope you enjoyed the post and I look forward to seeing all the great PostgreSQL applications that come out of this. Feel free to ask me questions or send me example database diagrams.

Next Steps

Automatic Updates

Stay informed and learn more about OpenShift by receiving email updates.

Categories
OpenShift Online, PostgreSQL
Tags
,
Comments are closed.