10

I've recently found a need to work with a PostgreSQL database. As I'm completely new to PostreSQL, I'd like to have a nice GUI for it, much like one for MySQL.

It must:

  • Support tunneling over SSH
  • Support listing databases
  • Support running queries

Is there an app that does this?

7

At work I am using DB Visualizer. This is a database manipulation tool that supports a large number of database systems, including PostgreSQL. It is written in Java, which means it is cross-platform compatible. I have experience with the free Linux version of it, but the website states it supports the 3 major OS-es.

Here is how it fits your requirements:

Support tunneling over SSH

According to the features list, it does support SSH tunneling for known_hosts and has private key support (see Connection Management section)

Support listing databases

It does support listing and browsing databases and database infrastructure.

Support running queries

It has Query window that allows executing queries and examining the results

Still there are some drawbacks to it, I'll list the few I am considering important and the ones I have personally rated as downsides:

  • It is commercial, although free version with limited functionality is available. That free version seems to support all items of your list though.

  • Built-in support only for some of the database engines. You may require to manually import database JDBC drivers for commercial servers, it is possible with the GUI though. PostgreSQL is supported by default, so this will not bother you.

  • It runs on Java, so you need to have a Java runtime pre-installed, or download the heavier executable which has an embedded JRE. Having Java involved could reflect on the memory consumption of your machine, thus affecting the computer performance.

  • Using the free version, I find it too basic and feature-stripped (by the time writing of this post), compared to pgAdmin, MySQL Workbench and quite far behind Microsoft SQL Server Management Studio - the other database tools I have experience working with. In other words - do not expect too much from it. Your list of requirements is basic enough as well, so I assume you can do without luxuries. Remember, though, I use the free version on Linux, so your experience with it on a Mac will hardly be any worse, and I am not complaining at all.

  • The syntax highlighting may not support all the language features. I had the "pleasure" of working a lot with IBM Informix using this software and the syntax highlighting of keywords and even specific dialect constructs was missing or incorrect, but bear in mind that Informix is not well supported in general. I cannot recall similar issues with PostgreSQL, but it does not mean there could not be any.

  • The free version limits the number of open query windows to ONE! This seemed a serious drawback at first, but it fairly compensates with back and forward navigation, that allow you to change the query to any recently open file. Also, DbVisualizer can launch multiple instances, which with a different query in each window.

  • The free version goes real bad here. It allows you to execute only one single statement per query!!! This can become problem for complex scripts with strict sequential logic and specific syntax. For instance, was unable to successfully call MySQL stored procedure definition script due to statement delimiters messing up.

If you are looking for a free tool that can work with a variety of database engines, possibility for custom support of unknown or commercial vendors (by manually providing the drivers in their JDBC-standard implementation), simple user interface and a reason to install JRE on your machine, then behold the almighty DB Visualizer!

Besides being a little mocky at times in my description, I find this software valuable and suitable for nearly all of my needs. Now, let me list some of the advantages I really appreciate:

  • The free version is enough for database access, browsing and querying.

  • Being Java based, it requires JDBC drivers to work with different database engines. Since JDBC is a standard in Java software development, implementations exist for almost all database engines, meaning you could use this software towards any database engine out there.

  • Supports JDBC urls for connecting to the server. For Java folks this means they can just copy-paste from the application configuration and connect to the database without playing with the connection wizard.

  • It allows you to save database connections (even with the free version) and re-use them. Also, it has the ability to duplicate a connection - useful if you work with multiple databases with small differences in the connection string (like IP, port, or database name) as it saves you from repeating the connection setup process.

  • As of version 8.0.9 I did not experience a single crash of the application, and I am happily having a few instances open and running for days at work (Prior versions used to "forget" where the built-in JDBC drivers reside on a random basis and the only way to fix this was to manually browse them for the application, which is frustrating at first).

  • It does not pop-out annoying upgrade windows, purchase offers or other marketing stuff.

| improve this answer | |
8

pgAdmin III, the "official" GUI client for PostgreSQL is available for OSX, too.

At least the Windows version supports SSH tunneling so I'm pretty sure the OSX version does so, too. Listing databases and running queries are standard features for any database frontend so those features are supported, too.

| improve this answer | |
  • 3
    Personally I wouldn't recommend pgadmin3. It is notoriously buggy with a tricky UI. – StackExchange What The Heck May 27 '14 at 18:04
  • Anything to back that claim? – ThiefMaster May 27 '14 at 22:19
  • 1
    I have to concur, some versions of pgAdmin are buggy, at least on Mac OS X. The current version 1.22.0 for Postgres 9.5 crashes if you select a font in Preferences > Query tool > Query editor > Font. And today it crashed when dragging a SQL query editor window to reposition. Versions for Postgres 9.4 worked well for me, but pre-9.4 ones proved buggy. – Basil Bourque Jan 28 '16 at 4:52
  • pgAdmin 3 is also buggy on Microsoft Windows, the dev team (at least part of it) doesn't seem to be that interested in user feedback, and pgAdmin 4 is even worse. – Franck Dernoncourt Aug 20 '16 at 19:54
  • I would also add that pgAdmin 3 is especially crashy when using SSH tunnels. – Franck Dernoncourt Sep 2 '16 at 19:21
2

Postico

Egger Apps released Postico, “A Modern PostgreSQL Client for OS X”. Available on directly and on the Apple Mac App Store. This is a Mac-only app.

Both SSL and SSH can be used for secure connections.

Supports PostgreSQL 8.0, 8.4, 9.1, 9.2, 9.3, 9.4, and 9.5.

The earlier generation of this app is now free-of-cost, PG Commander. Postico is the successor of PG Commander.

| improve this answer | |
2

You may like my app SQLPro for Postgres (App Store).

The app supports the features you requested (SSH Tunnelling, listing databases and running queries).

It also includes some nice features such as:

  • Auto-completion (sometimes called intellisense).
  • Exporting and import data.
  • A multi-tabbed based editor (run multiple queries at once).

There is a seven day trial available via the website. If you purchase via our webstore, use the promo code STACK25 to save 25%. If you are a student, I can happily provide a free license key, you just need to find my email address.

Disclaimer: I'm the developer.

| improve this answer | |
1

You can use DBeaver

  • gratis, cross-platform, portable version available
  • Support tunneling over SSH -> ok
  • Support running queries -> ok
  • Support listing databases -> ok, you need to check "show nondefault databases"

enter image description here

Example:

enter image description here

Note that you cannot explore non-default databases:

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.