
How I work with Postgres – psql, My PostgreSQL Admin - craigkerstiens
http://craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/
======
guylhem
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>

~~~
atsaloli
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=#

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

~~~
atsaloli
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);

~~~
lobster_johnson
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.

~~~
atsaloli
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 --

------
Roboprog
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? :-)

~~~
papsosouid
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."

~~~
Roboprog
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.

------
drewda
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...](http://www.navicat.com/en/products/navicat_essentials/essentials_overview.html)

~~~
SkyMarshal
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.

------
olefoo
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.

------
ScotterC
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/>

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

------
kochb
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.

~~~
staz
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?)

------
thisisblurry
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.

------
akurilin
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?

~~~
atsaloli
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>

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

~~~
atsaloli
You are most welcome!

------
Tashtego
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.

~~~
stevvooe
'subl -w' is the correct incantation.

~~~
xentronium
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...](http://stackoverflow.com/questions/14598261/making-sublime-
text-2-command-on-linux-behave-as-it-does-on-macos-x)

------
djthorpe
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>

------
sehrope
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/>

------
jaytaylor
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.

------
pyxy
`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.

~~~
pyxy
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

------
msluyter
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.

~~~
gburt
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.

------
dinkumthinkum
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.

~~~
Roboprog
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>

~~~
Roboprog
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.

~~~
sehrope
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.

~~~
Roboprog
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.

~~~
sehrope
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.

------
alternize
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.

------
EEGuy
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.

(C) Globally change | to \t, but don't bother to save the file. Just select
all and copy to clipboard. A few 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.

------
papsosouid
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?

~~~
defrost
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 )

