How I work with Postgres – psql, My PostgreSQL Admin | Hacker News Reader

How I work with Postgres – psql, My PostgreSQL Admin

craigkerstiens.com

149 pointscraigkerstiens56 comments

I do the same, but I have one thing I miss - an easy way to send the output out for graphs.

Tweaking and playing with gnuplot is a loss of time - if on a copy/paste excel and others can understand the data from the label and plot using reasonable defaults without many hints, certainly if columns are identified as datetime, labels etc. there could be a tool to use such hints and make a decent graph (to me, decent means giving a global understanding - sure you can tweak it to look good if you are preparing a report, but a lot of time is spent graphing thinks to figure things out and many graphs go to the trash in the process)

My dream is to do my select queries in psql and direct the output to that tool, never leaving psql - so it could be for example something that would be triggered on a new table creation matching a specific name like xx_, then it would simply require prefixing "select" by "create table xx_abc as ".

The best way I've found is to save the output to a CSV and pass it to other tools, but there are never quite user friendly and usually can't pick reasonable defaults.

There is an OSX psql frontend I tried after it was recommended here on HN (http://inductionapp.com/) but it was not that helpful in day to day operations.

Yet it seemed to be on the same problem - see this picture https://s3.amazonaws.com/induction/induction-visualize.png

Here is an example of feeding query output into gnuplot without leaving psql:

  # psql -U postgres
  psql (8.4.15)
  Type "help" for help.

  postgres=# \t
  Showing only tuples.
  postgres=# \a
  Output format is unaligned.
  postgres=# \f ' '
  Field separator is " ".
  postgres=# select * from example;
  1 1
  2 2
  3 3
  4 4
  postgres=# \o | /usr/bin/gnuplot
  postgres=# select 'set title "My Graph"; set terminal dumb 78 24; set key off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ; select * from example;
  postgres=# \o

                                    My Graph
  Time
      4 ++----------+----------+-----------+----------+-----------+---------**
        +           +          +           +          +            +     **** +
      |                                                           ****     |
  3.5 ++                                                      ****        ++
      |                                                   ****             |
      |                                               ****                 |
    3 ++                                           ***                    ++
      |                                        ****                        |
      |                                    ****                            |
  2.5 ++                               ****                               ++
      |                            ****                                    |
      |                        ****                                        |
    2 ++                    ***                                           ++
      |                 ****                                               |
      |             ****                                                   |
  1.5 ++        ****                                                      ++
      |     ****                                                           |
      + ****      +          +           +          +           +          +
    1 **----------+----------+-----------+----------+-----------+---------++
      1          1.5         2          2.5         3          3.5         4
                                     Servers

  postgres=#

This is incredible! I only wish it were a little easier to do on the fly.

Yeah, gnuplot is a very powerful tool.

I imagine you could put it all into a user-defined postgresql function, so all you have to say is:

   graph(select * from example);

You should be able to map it to a macro using \set.

    # \set foo '(select now())'
    # :foo;
    ?column?
    --------
    2013-02-14 04:18:23+01

    # select count(*) from :foo as foo;
    count
    -----

Afaik macros just expand inline, do you can embed stuff like \o.

Aye. This is getting over my head, so I inquired on the pgsql-general list (which is amazingly helpful).

Ian Barwick writes how to put all the prep stuff into a psql script, then all you have to do is define your query and invoke the script:

-- start quote --

What you could do is create a small psql script along these lines:

  barwick@localhost:~$ cat tmp/plot.psql
  \set QUIET yes
  \t\a\f ' '
  \unset QUIET
  \o | /usr/bin/gnuplot
  select 'set title "My Graph"; set terminal dumb 78 24; set key off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ;
  :plot_query;
  \set QUIET yes
  \t\a\f
  \unset QUIET
  \o

  barwick@localhost:~$ psql -U postgres testdb
  psql (9.2.3)
  Type "help" for help.

  testdb=#   \set plot_query 'SELECT * FROM plot'
  testdb=# \i tmp/plot.psql


                                    My Graph

      4 ++---------+-----------+----------+----------+-----------+---------**
      +          +           +          +          +           +     **** +
      |                                                          ****     |
  3.5 ++                                                     ****        ++
      |                                                  ****             |
      |                                              ****                 |
    3 ++                                         ****                    ++
      |                                      ****                         |
  2.5 ++                                *****                            ++
      |                             ****                                  |
      |                         ****                                      |
    2 ++                    ****                                         ++
      |                 ****                                              |
      |             ****                                                  |
  1.5 ++        ****                                                     ++
      |     ****                                                          |
      + ****     +           +          +          +           +          +
    1 **---------+-----------+----------+----------+-----------+---------++
      1         1.5          2         2.5         3          3.5         4
                                     Servers

  testdb=#

-- end quote --

And

Sergey Konoplev explains how to do it with a server-side function - you need gnuplot installed on the db server -

-- start quote --

  plpython/plperl/etc plus this way of calling

  select just_for_fun_graph('select ... from ...', 'My Graph', 78, 24, ...)

  will do the trick.

-- end quote --

You could configure the PAGER environment variable and "\pset pager always" so that all your query results are passed through an external script. The external script would have to decide whether to just spit it out on stdout to display as usual, or to send the query output to a graphing program. This is a pretty ugly solution but I'd be interested to know if anyone is doing anything like this.

I found that the best way to do this is to use the RPostgreSQL/R*SQL driver and plot the graph through R.

Heres a little more on it:

http://theexceptioncatcher.com/blog/2012/11/really-cool-thin...

And a question related to it: http://stackoverflow.com/questions/13295855/plotting-multipl...

We have graphs in the plan for CartoDB development, and it is built on postgresql, so hopefully making it easier for people to start getting graphs out of their databses.

But, but, but! If I use the CLI instead of a GUI, people might start asking me to put what I am doing into a file and save it in revision control. They might even ask me to make a function (procedure) out of some of it so it can be extended or reused. Then where would we be -- how can I claim that DBA-ing is magic? :-)

Funny: this got upvoted, then immediately downvoted.

Come on, tell the truth: how many of you have had The Great and Powerful Oz for a DBA? (there's a wonderful BOFH episode about the BOFH vs a DBA - read when in a foul mood some time for a chuckle)

I find your characterization very puzzling. It is the DBAs I've worked with who want to use revision control, who want to use correct database schemas, who want data integrity, who want to use stored procedures. They are the ones fighting against developers who never bothered to learn how to use a relational database and push the mysql/php style of "just make your app the database and the database is just a file full of inconsistent data, don't do joins, don't use view, don't use stored procedures or functions, don't use triggers, etc."

Either you have been lucky, or my coworkers and I have been unlucky. YMMV.

Fortunately, most all the DBAs I've worked with do in fact value data integrity, but, don't seem to care about other development practices like automated DB version migrations or reducing repetition.

Navicat is a decent way to deal with both Postgres and MySQL (among many other database engines) with the same interface.

All the different ways Navicat packages its products are unnecessarily confusing. Premium Essentials ($20) is almost certainly what you want: http://www.navicat.com/en/products/navicat_essentials/essent...

Just downloaded their trial edition a few days ago for an upcoming project but haven't started playing with it yet. Glad to hear good things about it.

I'm partial to SQL-mode in emacs which allows you to start psql in a comint style buffer, send queries from an edit buffer and capture them to output buffers; it used to be a pain to set up but these days it mostly just works.

And by accident or design, there is almost no interference between the psql commands and the editor.

Can't wait till Sequel Pro supports Postgres. Was really excited to see them publicly start down the path of implementing it last September. http://stuconnolly.com/blog/sequel-pro-postgresql-support/

I agree. I honestly think this is the biggest thing slowing adoption of psql. It is the one thing I miss from mysql.

Just found TeamPostgreSQL (http://www.teampostgresql.com/) earlier today, by far the best I've ever worked with.

Not that the other tools have set a very high bar. pgAdmin crashes constantly, and you can't sort columns by clicking on them. Navicat doesn't run properly on Linux, you have to mess around with wine. phpPgAdmin has a single DB host is hard coded into a config file and just feels antiquated.

One major drawback of TeamPostgreSQL so far: it doesn't support SSL connections.

It doesn't support SSL connexion to the database or it doesn't support HTTPS ? (And does it work if you put it behind a proxy?)

Matt Thompson (https://github.com/mattt), also from Heroku created Induction (http://inductionapp.com/) to handle this sort of database administration around a year ago, but it seems like he's become pretty busy with other projects at the moment. I hope that he's able to pick up progress on it again as it really had a lot of promise behind it.

Are there any good guides out there for starting out Postgres 9.x+ administration, including proper safe deployment on a VPS, maintenance and all sorts of good practices as far as tools are concerned?

I'd recommend "The Accidental DBA" (or, Administering PostgreSQL When It's Not Your Job). You can find it here: http://www.pgexperts.com/presentations.html

The Accidental DBA sounds like a really well fitting title for my request. Thanks! :)

You are most welcome!

PostgreSQL 9.0 High Performance, Gregory Smith

Postgresql 9 Administration Cookbook, Simon Riggs, Hannu Krosing

Thank you!

Great article but I can't get his advice for using Sublime Text as the editor to work. \e will open a new file, but saving/quitting doesn't ever run the query.

'subl -w' is the correct incantation.

Last time I checked, it didn't work in linux iff there were other sublime windows open.

See http://stackoverflow.com/questions/14598261/making-sublime-t...

Thanks- that works!

I'm trying to build a mac GUI server app at the moment for postgresql for a standalone server, like Postgres.app. I should be finished in a couple of months or so and then I will write a GUI client app for Mac and iOS. If you're interested, you can download the source here: https://github.com/djthorpe/postgresql-kit

We created JackDB[1], a database development client that works in the browser, to solve exactly this problem.

We're making a lot of progress with it, including a lower price offering and launching a free tier soon. Check it out, or shoot me an email if you're interested.

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

I've frequently given the same advice to my co-workers. The GUI's for Postgres are all lackluster, and learning to use psql with \d seems like the best way to go. It takes a little time to get used to, but once acclamated it blends into the background and works great.

`win psql` in Acme window is my choice: you get per-session history for free, easy copy-paste (with a real 3-button mouse of course) and even multiline SQL queries are very comfortable.

forgot to mention some useful psql options for comfortable work within Acme: `win psql -xn -vPROMPT2=`

setting PROMPT2 to null string gifts you with ability to easily copy-paste SQL expressions

`-n` disables readline lib because we don't need it in Acme

I've been using Squirrel and it seems ok; fairly decent most of the time, actually. (Anyone else have any thoughts on Squirrel?) I've never used psql though. I'll look into it.

Squirrel, like all Java software I have ever used, has this bad habit of getting in to an inconsistent state where it needs to be restarted to use.

That's cute but, let's be honest, viewing query results for anything but trivial schemas or amounts of data with psql is somewhat close to impractical.

Dump the result set as a table into a file and refresh "the file" in a browser?

Having 2 windows open is about the same as having 2 panels in one of the DB "Monties". http://catb.org/jargon/html/M/monty.html

That said, the sql*pro CLI that Oracle provides for their DB SUCKS (!!!) rotten eggs, and gives people the impression that a CLI is useless for database work.

Yes the CLI for sql*plus does suck. When I am using it (pretty rare these days) I use rlwrap to make it suck less. Command history is pretty convenient too.

Here's my ~/bin/sqlplus shell script pointing to the Oracle instant client install:

    #!/bin/sh

    OIC_DIR=$HOME/opt/oracle-instant-client
    export LD_LIBRARY_PATH="${OIC_DIR}:$LD_LIBRARY_PATH"
    export TNS_ADMIN="${OIC_DIR}"
    rlwrap "${OIC_DIR}/sqlplus" "$@"

Bonus: You can use a tnsnames.ora file with the instant client if you put it in the instant client directory and export the environment variable in the script above.

That's right, it's been a while. "pro" was the C preprocessor, "plus" was the gawdhawful CLI.

Thanks for the tip on rlwrap, in case I have to use that thing again. I'm assuming it provides GNU readline support on top of the base tool.

Even if you're not an Emacs guy you may want to consider using it with the sqlplus.el module. It adds a nice table formatter and other features to make SQL*Plus more palatable.

Yes, rlwrap provides GNU readline atop just about anything. When I write my REPL tools (test utilities, etc) I usually wrap them for local use in rlwrap as 99% of the time it's what you want.

While you are being honest, can you also be specific? I have large, fully normalized schemas which I have only ever viewed via psql. I run queries returning lots of data all the time. I have no idea what part of this is supposed to be impractical.

On the contrary, it can be very efficient when you become familiar with psql. psql + tmux (or your favorite terminal multiplexer) means you can have as many interactive sessions open as you like, and you don't need to leave your keyboard.

That's why you aggregate, section, order, limit and use all the commands to reduce what you are looking for to something that fits on the screen. You can't "view" a billion rows straight in any meaningful way at once anyway, no matter what your client. Most of the time you're interested in some summary statistic. Graphics are often summary statistics as well.

I've found it to be quite good, you can express powerful stuff with the keyboard directly, once you know what you're doing. A GUI is always less versatile in forming joins and search criteria. Once you start having a dropdown query builder, what's the point?

I do use a separate editor often, nano or sublime, depending if I'm working on the remote or local end. Both have syntax highlight, and then it's the natural path to scripts and version control from that.

i'm pretty happy with ems' sql manager - while not free, it has everything i ever needed from a postgres gui. working with psql to query large data sets seems a bit tedious to me - i prefer a multiwindow gui application with a decent tabular view that i can browse through. i heavily use psql for maintenance stuff tho.

For ad-hoc, read-only query building on a Windows platform, I get a lot of mileage out of this quaint, non-CLI "toolchain":

(1) pgAdminIII's query tool (not the tedious query builder, yes the bare SQL ("pencil" button) query tool, a plain text editor and SQL runtime

(2) An editor able to both allow open files to be changed externally, and notify me that that's happened (no file close/reopen; can leave one file open for many round trips here). Also, have it make whitespace characters visible so one can see TAB characters. EditPlus does these job for me.

(3) A spreadsheet program open to a blank, unnamed, unsaved sheet. You guessed it, I'm talking Excel.

Here's the workflow:

(A) Run the next try of the query-build-in-progress in the query tool, sending its output to the file "simultaneously open" on EditPlus. Include headers, and use a column separator that's unlikely to occur in data, e.g. the pipe symbol, '|'. Almost all keystrokes.

(B) Switch to EditPlus. It then politely notices the file's been changed, assent to its doing a file reload (no close/re-open in the UI, but of course that's what it does). Two keystrokes.

(D) Switch to spreadsheet, paste. Two keystrokes. Here is where using the TAB character as a column separator works its good magic; all the headings and all the query result drop into properly aligned cells in the spreadsheet.

Analyze the results in the spreadsheet, maybe highlight some color on problematic rows, columns or cells. Go back to the SQL editor and re-run the whole chain.

No file naming (except once on startup), no import wizard (ugh!), no open/close, no CSV misinterpretation. All stock software.

Various ways of icing this particular workflow-cake:

* After pasting into the sheet, highlight the columns that Excel typed as numeric when you know they're character values that happen to be all digits. Use 'Format Cells' to change the 'Number' (data type!) from 'General' to 'Text'. Nothing will appear to change, but here's the magic: Highlight all the cells and delete their content (delete key). Now paste from clipboard again. This type the character data hasn't lost its leading zeroes, and it's properly left-justified.

* Start with a SELECT ... then once the result is in Excel, delete the colums it turns out you don't want... then hightlight those headings, copy to clipboard, paste to editor, reformat as comma-separated, copy and paste that in place of the in the original select.

* Use underscores in column names instead of camel-case, e.g. row_id instead of rowId (camel-case doesn't work in PG unless column identifiers are quoted, not worth the pain). Once in Excel, highlight all the column names, repalce underscore with blank, set the Format to Wrap. Now the column names form a distinctive taller-than-the-data-rows, easy-to-read.

I've come to think of (and use) the clipboard as a manual, stepwise imitation of the character stream native to shell scripting of *nix CLI tools. Not a true 'toolchain' in that one has to manually pump everything through the clipboard, but but still effective and quick enough for fast, "filenameless" turnaround on query development, and very little mousework.

Most of all, it gives me the very significant power of a spreadsheet for query error analysis.

Every time this topic comes up, people discuss tools they use which all seem to be nothing more than a graphical version of psql. As in, they let you see a list of tables, then see the columns of that table, run queries, etc. But none of that is actually making anything better or easier, its just a different client with the same features.

Does anyone know of a tool that has a good visual DB design interface? That is what I am missing (not just when I work with postgresql, but period). I want to be able to easily and visually see the relationships between tables, not just the tables themselves, or one table with a list of its relationships. Something like this: http://ondras.zarovi.cz/sql/demo/ but not web based and actually supporting all of postgresql?

As much as it pains me to say this, that's one of the things that MS Access did fairly well. It might actually be possible to do that through ODBC with it actually.

We've used MySQL Workbench to visually design our Postgres schema.

We then export it into MySQL database, generate a migration based on that, and then apply the migration to a Postgres database. Before we used migrations, we instead had a shell script with a series of sed commands to map the MySQL SQL output to SQL that will work in Postgres.

So, there are options, but I'd very much like a visual DB designer for Postgres as well.

Not sure about other db's, but there are several pages on the PG wiki about gui tools:

http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

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

There's a bit of a killer tool that's relatively well known in geospatial data circles (and has been kicking about for a few decades) called FME from Safe Software [1] that I've never seen referenced in the non spatial database circles.

It's got some of the best table visualisation and table/tool/translation/QC intergration visual design tools I've seen.

It's essentially built with the goal of starting with multiple table sources in various ASCII / <some>SQLDB format and displaying all tables, building filter pipes to merge and translate data on the fly and produce single or multiple coherent databases and table sets (or even more ascii tables) as output.

It has it's quirks but it's a solid bit of kit ( I used it some years back to read in and unify data from several million leases (geospatial boundaries and related metadata) from multiple sources (Australian, Canadian, South African, etc. land departments) - from whoa to go was about four days, once running it chewed through the data on par with normal copy speeds (eg: it imported cleaned & filtered data in a time ballpark to just copying the data from A->B).

I'm not affiliated, but on the basis of that job, yeah, I'll spruik it.

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

[2] http://www.safe.com/fme/fme-technology/fme-desktop/overview/

( See desktop demo video from overview section )

Reply on news.ycombinator.com