OmniDB is an open source web tool designed specifically for managing relational databases with focus on simplicity and user interaction, but at the same time be fast, powerful and light on memory. OmniDB, at the moment, supports several different relational database systems such as PostgreSQL.
The latest release of OmniDB, version 2.3.0, has introduced ‘Debugger for PL/pgSQL functions’ – a much requested feature for OmniDB users.
If you’re a PostgreSQL user and want to extract the power of its functionalities through a simple GUI tool, here is Simon Riggs from 2ndQuadrant demonstrating how to run OmniDB on top of PostgreSQL 10.
What is OmniDB? It’s a GUI tool designed to access multiple databases and especially, it’s designed to access PostgreSQL, but it does access many different kinds [of databases].
I’m demonstrating OmniDB 2.2 which was out on 12th October 2017.
What we’re going to do, first of all, is connect through to the database.
If I just click quickly on the Select Connection icon, we will go straight through into the database.
I’m demonstrating this to you on a Mac application but the same interface would be accessible through web browser, if you choose to do it that way.
What I’m going to show to you first is that we’ve got access to a database here, that as you can see is Postgres 10 and it’s got a number of different objects within it. You can see that we’ve got a tree-view here that illustrates the different object types within the database. And things within the public schema such as Tables – displayed to you in the standard tree-view drop-down, so that allows us to see what’s going on within the database.
We can run queries against the database as well. If I type in a query here that we can use to demonstrate what’s possible:
Select count(*) from customers
This button here (RUN) as you can see runs the query. So there is one record returned. You can see the start time, the duration, and the output from that query.
What we can also do, inside this interface, is inspect things like individual trigger functions. We can actually edit those fairly easily by accessing the function itself on the interface, again fairly straightforward, fairly easy to use.
One of the things that we’ve added for Postgres 10 is the ability to access the Logical Replication features, which were one of the most important aspects of Postgres 10.
What I’d like to do now is just demonstrate to you how easy it is to use the Publication feature.
If we look at Publications, we can see that there isn’t actually any Publications recorded at this time. But if we choose to create a publication, then what we can do is actually edit together a piece of SQL, that will allow us to create a publication. So if we look at,
CREATE PUBLICATION customers_pub
FOR TABLE customers
There we go. That’s all we need to create a publication from OmniDB – again fairly simple.
The philosophy here is that instead of presenting you with lots of complex dialog boxes with tick boxes on, the concept is every time you want to perform some DDL action, it will actually present you with a template of the SQL and then you can actually learn to type in the SQL yourself.
It’s pretty much as quick as using a GUI interface so there’s no real difference in terms of speed.
What we end up here is SQL that you can actually cut and paste into other applications if you want to, so that’s quite an important aspect.
Another thing that we can do with OmniDB is look at Monitoring. One of the things we can do there is to access a number of different sessions that are running on the database.
Here [Monitoring –> Backends] you can see the types of things that are executing.
Finally the last feature of OmniDB is a Version Specific Documentation. You can see that quickly and easily takes us through to Postgres 10 documentation.
So that’s just a short overview really of what OmniDB can do.
What I’d like to point out here is that, you can actually access multiple different connections to multiple systems at the same time. For example, if you had a Postgres 9.6 system or Postgres 10 system then you’d be able to access each of those at the same time from the same interface.
What’s especially important and why we call it OmniDB is that it gives you access to multiple types of databases. For example, if you’re accessing MySql database or a SqlServer database or an Oracle database, you can do all of that from a single interface, which obviously makes managing different databases a lot easier than needing to switch between the individual tools. (if you’re switching between each of the different systems which provide multiple tools)
The overall purpose of Omni DB is to produce a single GUI tool that supports Postgres well, but also supports other databases.
Please use and enjoy OmniDB.
Thank you very much!