PostgreSQL Studio | Hacker News Reader

PostgreSQL Studio

postgresqlstudio.org

276 pointsmoreati94 comments

Official announcement from PostgreSQL.org: http://www.postgresql.org/about/news/1485/

OpenSCG is pleased to announce the release of PostgreSQL Studio 1.0. PostgreSQL Studio is an open-source, web-based GUI for PostgreSQL. It allows users to manage and query their database from a browser.

Version 1.0 of PostgreSQL Studio is distributed under the PostgreSQL License. Some of its key features include:

- Easy to use AJAX web interface

- Secure access over HTTPS

- Robust object wizards

- Query editor with syntax highlighting

- Cross platform and easy to install

You can find more information about PostgreSQL Studio at www.postgresqlstudio.org

JavaServer Pages? That's an interesting choice.. and will probably rule it out for a lot of people. I'm not running Java on my web server, sorry. Interesting that a PostgreSQL product licensed under PostgreSQL is using an Oracle technology. Edit - removed the MySQL reference.

Where do you see anything about MySQL?

I think he's just trying to make the Java->Oracle->MySQL connection. I don't see how using Java is in any way a vote of confidence for MySQL though, so the point seems moot.

Given your apparent hate for the JVM, I guess you only run C web servers?

> I'm not running Java on my web server, sorry.

This attitude is interesting to me, because I use Play Framework and Dropwizard quite heavily. What exactly is the objectionability of a JVM versus, say, running PHP-FPM?

Maybe the objection is running the JVM on the server just for this tool?

As opposed to running rails or PHP just for any particular application?

Well yes, personally I wouldn't install Rails or PHP on my servers just to run a database tool.

Ok, but I wonder what you will use for a web-based database administration tool...

You don't have to run this on your web or db or any other prod server.

Run it on a separate server or vm and point it at your db server.

http://www.postgresqlstudio.org/support/documentation/suppor...

> JavaServer Pages? That's an interesting choice..

It's a GWT app. Some of the most awesome web tools I've ever used were written in GWT. Far from holding that against it I would see it as a promising sign that's going to be a high quality piece of work.

For what it's worth, TeamPostgreSQL is also a GWT app. Also, GWT apps are not JSP based, they are pure HTML and JS for the client-side.

I don't think anyone thought they were jsp for the client side... but surely they need some form of server technology for the server side -- and jsp would be perfectly usable for that?

JSP allows mixing client-side (HTML) with server-side code. GWT, and most modern frameworks, use regular servlets as the backend, and some (other) type of templating system with better separation.

We make a similar thing: http://www.teampostgresql.com

AJAX/JavaScript powered rich web GUI for PostgreSQL. Looks like this took some inspiration from us too (which is great). TeamPostgreSQL is a side-project but pretty mature, having been around since 2010.

It seems it only works with Java 6 (at least according to the Mac installer), which is too bad, because I don't want yet another version of Java on my machines.

Is the source code publicly available?

I believe the installer packages bundle a JRE (which runs in isolation from your other Java installations) so that shouldn't be a problem.

Only the cross-platform archive assumes you have a Java (6+) installed. Off the top of my head I can't remember if backporting to 5 would be a possibility but considering that Java 5 is almost ten years old, it may be too much effort to be worth it.

The source code isn't public anymore than my dirty underwear is :) Seriously though, I might open source it if I thought anyone would be able to get anything out of it.

I downloaded the Mac version, ran the installer, and it immediately said it required Java 1.6 and launched a JRE 6 install.

    $ java -version
    java version "1.7.0_25"
    Java(TM) SE Runtime Environment (build 1.7.0_25-b15)
    Java HotSpot(TM) 64-Bit Server VM (build 23.25-b01, mixed mode)

Which one is the Mac version? Usability fail.

There's a dropdown ('Select download') with four options, one of which says 'Mac OSX Installer'. Can you explain what makes it unclear? Thanks in advance!

I see three download options, one for zip, one for tar.gz, and one for tar.bz. Mac installers are usually bundled in .dmg archives, or .pkg.

Ah, you are talking about the OP's site - this subthread was discussing TeamPostgreSQL: http://www.teampostgresql.com/download.jsp

Thank you, I will look into it. Note that you can still run the cross-platform version if you want to try it.

I use TeamPostgresSQL daily. PostgreSQL studio looks nice as well, haven't downloaded it yet.

Are you still actively developing TeamPG? It's pretty complete, but if it was open I'd chip in!

Yes, it is still being actively developed, at a slowish rate depending on how many other projects are going on at the same time. We release a new build around every two-three weeks, with minor enhancements and bug fixes.

Releasing the source is no big deal, but I would have to spend some time wrapping it up nicely to make it accessible to outsiders.

This image gives an idea of UI: http://www.openscg.com/wp-content/uploads/2013/10/pgstudio_s...

Thanks for the image link. At a quick glance, it reminded me of PHPMySQL, but I guess most, good, web-based admin UI's will have a similar layout and functionality.

We have a similar Html5/AJAX powered offering[1] as well though in addition to Postgres we also support a number of other database types (MySQL, Oracle, MS SQL Server). Looking forward to trying this out to see how it compares.

[1]: http://www.jackdb.com/

Note the above is not open source though it does have a crippled "free" option.

To clarify, our free cloud version is a complete product. The only difference between it and the paid plans is the number of simultaneous connections and the organization, team, and audit management features.

And the limit on the number of rows per query.

It does sound slightly crippled in that sense.

Just click on the 'Fetch More' button, and 50 more rows will be retrieved. For cloud based _free_ solutions, I haven't found anything better than this.

..and the site has gone down. Sounds fantastic, though- pgSQL has always been lacking in admin tools compared to MySQL.

From the URL of the screenshot linked to, it's a Wordpress installation.

A lazier sort of wag might point out that Wordpress runs on MySQL, but that would be a cheap shot unbecoming of our noble selves here at HN.

A more helpful sort might encourage the site owners to turn off the HTTP server, install WP Supercache or W3 Total Cache, then turn it back on.

Or just use a static site generator? It amazes me how many rarely updated sites use Wordpress when the people who run them are more than capable of using something like jekyl. I guess the themes ecosystem etc around Wordpress is just too big to pass up?

SSGs are good if you think of them in advance. In the short term, such as when one is unexpectedly on the HN front page, switching on caching is the biggest payoff.

Huh? There's the great free PGAdmin which is WAY better than any of the web-based tools, and it has built-in SSH tunneling so it's easy to use against a remote server.

This thing? http://www.pgadmin.org/

This is why I hate using Postgres despite it being such an amazing database internally.

PGAdmin is everything that's wrong with database tools rolled into an executable.

If you dont mind using eclipse, you can use 'toad' eclipse plugin, which is really good.

PGAdmin is unspeakably awful. I run a Mac and it was very painful going from the world of Sequel Pro (for MySQL) to the awful, awful, terrible pgAdmin.

I ended up buying NaviCat Essentials, which is considerably better but still not perfect.

What do you find to be wrong with it? It's always done the job well for me, whereas Sequel Pro, while a prettier face, and useable enough, manages to crash at least once a day.

PGAdmin crashes regularly, and in a very annoying (and stupid!) move blocks the UI for all the windows on some database operations e.g. adding indexes. It also blocks the UI when it's processing the results of a db query, which can be slow if there is a lot of data being returned. The interface is sort-of ok though.

Sad to hear that hasn't been fixed since I left a couple of years ago...

I don't see how that's "unspeakable bad, terrible, terrible". Latest versions crash a lot less and if you really need to use UI during long database queries you can run another instance of pgadmin. It's still very useful and powerful tool.

Well, SequelPro at least started adding support for Postgres[0], but it's hard to tell how far along it is. Most of the commits in that tree seem to be from last September, with a smattering from January and May of this year.

[0]: https://code.google.com/p/sequel-pro/source/browse/#svn%2Ftr...

I'd say it _almost_ has SSH tunneling built in. They're 95.418% there. It's still hardcoded to only use port 22. You can't specify another port. In my case, I have to hop through other machines and if I have SSHd running on my local box, I can't use their SSH tunneling.

Workaround, why not making the tunnel yourself?

ssh -L5555:127.0.0.1:5432 host

Then you have it on your local 5555.

I'll eventually try it out, but I have absolutely no idea how to go from this .war file to a running web server. (I see some .war files actually have a web server built in so you can just to java -jar, but not this one.)

The total absence of any information on the site about what it is you're downloading, what it's requirements are, or how to install it, is rather unhelpful. I downloaded the zip file just to find out what was inside it.

I haven't tried installing it yet, but in principle installing .war files can be very simple. You need an application server (such as Apache Tomcat), you log into the admin section of that in your browser, and somewhere in there there's a form where you just upload the .war file, and it gets automatically unpacked, installed, services started up, and so on.

Edit: I take it all back. Well, most of it. They have an install guide, which you can find via Support, Documentation.

Yeah, simple. And then the Java app tells you that you don't have the right version of Tomcat and do need at least an 0.0.1 update. (Yes, I was forced to run JIRA on Ubuntu 10.04 LTS)

Stupid Java environment. :-(

OK, this turned out to be not that hard, just completely unfamiliar for me (having last used Tomcat in 2002).

apt-get install tomcat7 tomcat7-admin; sudo $EDITOR /etc/tomcat7/tomcat-users.xml # add user with role manager-gui

Then I just went to http://localhost:8080/manager/ and uploaded the .war file. Et voilà.

I assume that you could also use glassfish

I've been thinking about making the switch from MySQL to PostgreSQL.

For database design/GUI work SQLyog has been pretty awesome over the years (auto-complete, drag & drop, tabbed editor windows, export query result to csv, etc.). Is there an equivalent for PostgreSQL?

When I hear web-based I think phpMyAdmin, which, while functional, pales in comparison to SQLyog (web app in question does look a lot more polished than phpMyAdmin)

These apps are nice, but the best PostgreSQL editor and GUI I've seen is EMS PostgreSQL Manager, http://www.sqlmanager.net/en/products/postgresql/manager. It even has a pretty nice query builder. Though I believe it's windows only, and also costs money. I use it every day and vastly prefer it over every other tool I've used.

Linux workstation here but run a couple of Windows VMs for browser testing and Windows only apps.

EMS looks pretty feature rich, thanks for the tip.

Quite a few:

http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...

http://www.postgresonline.com/journal/index.php?/archives/13...

Navicat is a good one if you don't mind paying $80-$250 for the personal version. http://www.navicat.com/

Very interested to see this, once the site comes back online. In spite of the previous efforts, I've always found the admin/query tools for Mysql and Postgresql as lacking compared to the ones from the commercial-only vendors. And I've never cared for the non-db-specific sql utilities. An easier tool to work with Postgres would certainly make my clients much happier.

Hey, random internet stranger here -- I like the idea -- maybe you could show some comparisons with the other products on the market (other postgres management programs). Also, I think you should put the screenshot of the dashboard on the front page, will make it instantly clearer what it does, and probably generate interest quicker?

I have to admit, the first thing I thought about when I saw it was "phpMyAdmin" and "security vulnerabilities". I hope I'm simply being uncharitable, but I'd have to have a lot of faith in the product and the underlying stack before I let a web-based GUI anywhere near a production database.

As the author of a comparable tool (TeamPostgreSQL), most of our users use an IP whitelist in their firewall to access the web client. Of course, you can also use the tool on your local network only as a substitute for a regular admin tool.

IP whitelist... secure? O'RLY?

This looks interesting (via the description and screenshot in the comments...the site is down as of this writing), but what does this provide that pgAdmin (http://www.pgadmin.org/) doesn't?

I certainly don't know what PostgreSQL Studio is like, but I think there's a lot of room for improvement in user-interface with pgAdmin. I tried using it but quickly gave up. It requires way too many mouse clicks to see the things I want to see.

(By comparison, I tend to enjoy using phpMyAdmin for MySQL.)

pgadmin is a postgres adoption prevention mechanism, hopefully this tool will be developer friendly, and stable.

I have to be the only person on the planet that actually likes pgadmin.

Nope.

The biggest problem with PGAdmin is it prioritizes the least used features, pushes them in your face, while burying the things you probably use most.

It's about ten clicks to make a simple query. It's nearly as many just to see what columns a table has.

The same thing in Seqeuel Pro? Two or three clicks. Max.

It literally just took me four clicks (one of them a double-click on the database server in the tree pane; so, yes, if you want to get pedantic, I pressed the trackpad five whole times) — inclusive of launching the app — to issue a query:

  1. Launch the app
  2. Double-click the db host to connect
  3. Dismiss the dialog box prompting me to install the server instrumentation tools
  4. Click the SQL magnifying glass icon in the toolbar.

That third click could be eliminated from ever being required again with the addition of checking the "don't show this dialog box again" box before dismissing the dialog.

So, best case, three clicks — four if you click on the "OK" button on the password dialog, instead of hitting enter. Yeah, PgAdmin isn't the prettiest tool on the planet, but demonstrably false hyperbole is unbecoming.

1. Launch the app. 2. Click the host. This apparently selects it. 3,4. Double click the host to force it to connect. 5. Click OK on the login screen even though it's set to "Store Password". 6. Click to select the proper database. 7. Click to magnifying glass. 8. Find the query window, which has a tendency to show up in really awkward places if you remove your second screen, click to drag it back somewhere useful, such as visible on your screen.

It feels like ten because of all the steps that are totally unnecessary. Sequel Pro, by contrast, doesn't have a separate query window, you never need to find it, and will connect automatically.

PgAdmin seems to ask an awful lot of stupid questions, and there's no way to turn them off. The "Don't show this dialog again" setting for each, as you mention, would help a lot in this department.

I used it for my book (Instant Postgres Starter) mainly because I wanted to show a cross-platform UI that would look consistent. Day to day I don't use it. Maybe if it was awesome I would but I don't feel hamstrung by the CLI tools--the opposite, really.

As per OpenSCG: http://www.openscg.com/2013/10/postgresql-studio-1-0-is-out/

The standard bearer for PostgreSQL GUIs is pgAdmin, but that requires people to make a database connection across the internet which they may be reluctant to do if they don’t have a VPN of some sort set up. The right thing is to have a web-based GUI allowing users to connect to their database through a browser. PhpPgAdmin is an option, but many of the new PostgreSQL users I’ve talked to like the newer look and feel of sites like Heroku and are turned off by the UI of phpPgAdmin.

Current version of pgAdmin adds support for connecting via ssh, though I haven't had cause to try using it yet.

It works. I've used pgAdmin's ssh support for years.

It can be used to connect to heroku instances also, which is useful.

So, instead of making a database connection through the internet, you tunnel it over http? What can you gain by doing that?

Looks like what I wanted is already here. I think I can now learn Postgres on a Chromebook which just has a web browser. (I had created a free database on heroku previously.)

https://teampostgresql.herokuapp.com

You don't need a VPN, pretty much any server running postgresql is also running sshd.

Clearly there is a pain point with PostgreSQL and GUI front-ends. Wish I could see the site.

Honestly I found the sooner one gives up and just uses the command line, the better.

Indeed. I really have no idea why so much energy is spent on GUI tools.

What do these things provide that one can't get from the command line, I wonder?

I guess being able to see a whole bunch of things at the same time is useful, plus never having learn much SQL, plus being edit things by clicking on them.

[I just realized that my comment may have appeared very harsh, specially from the authors' point of view. My apologies to them, I was _very happy_ to have a good GUI when I started working with databases]

@antihero : Seeing things does help, but in my experience, using scripts and the command line is faster for developpement, because a lot of code can be reused, which is not the case with clicks.

I was being too restrictive though, a lot can be done with a GUI, of course.

Currently the site is trying to request this URL http://localhost/wordpress/wp-content/themes/alexandria/skin...

(I have a local Django server running on 0.0.0.0:80 and the terminal keeps flashing when I browser the site and this image is obviously missing and gives a 404)

Server's down - anyone got a mirror, announcement or enough experience with the project to give a good rundown of what it is and what it does?

I was really excited when Induction (http://inductionapp.com/) was announced, but I don't think there's too much development for it. The interface is probably the best I've seen, but functionality is fairly sparse. pgAdmin3 has been my go-to app, but I'm excited try out PostgreSQL Studio!

I'm amazed no on mentioned SQLWorkbenchJ. For postgres on Mac, it is infinitely better than pgAdmin. There are a few usability issues (manually setting the JDBC driver, for one), but once you get the hang of it, it's quick and very fully featured. I use it over Navicat, and it's free.

Ugh, java... Really?

I really don't want to have to run a java server just to host this.

This looks amazing for people who want to install and maintain their database client as a webapp on their production database server.

You don't run it on a production server, you can configure it to point at any db server in your network. They use localhost as an example only.

http://www.postgresqlstudio.org/support/documentation/suppor...

They should have wrapped it with jetty-runner or something that makes it easy to run it without having to fire up Tomcat.

Surface comment of the day - What an excellent, clean website. From the color scheme to the font choices. Excellent.

Personally I started to use valentina studio, it has diagrams which I love. But mysql workbench is a lot better.

I'm seeing a blank site.

Screenshot? Demo?

Reply on news.ycombinator.com