Colorizing The psql Prompt — A Guide

The shell can be a bland, boring thing to stare at day after day.

So we shell dwellers spend an inordinate amount of time customizing our text based world. And there is no shortage of examples of impressively pimped out shell prompts that abound on the Internet. Just take a stroll through this wiki on color bash prompts at Arch Linux, for example. Inspiring, isn't it! (If to you it's not, you might as well abandon reading this article now.)

Quite some time ago, I came across +Craig Kerstiens' outstanding intro (which I highly recommend) to customizing your psql prompt, Getting more out of psql (The PostgreSQL CLI). I promptly (no pun intended) applied pretty much all of his customizations to my .psqlrc file, but never quite understood the arcane language of symbols that are responsible for the colorized prompt. So I set about to deconstruct it that I might build my own. The following is a summary of the process used to arrive at the example shown on the left.

So, let's begin with the final product and break it down from there. (My format here is essentially an adaptation of a connection URI in libpq.)

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

Yep.  Complete gibberish. Let's make things a bit clearer:

\set PROMPT1 '\n%[%033[1;31;40m%]%[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

What we've done here is to obscure what are referred to as 'terminal control characters', leaving only a combination of
  • actual characters that will be printed at the prompt ( ➤, psql://, @, \n , etc.)
  • psql specific commands and variables (\set PROMPT1)
  • psql substitution strings (%n, %#, etc.)

Taking it down just one more notch:

\set PROMPT1 '\n%[%033[1;31;40m%]%[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

And you'll find that we are left with sets of matching %[ ... ]% sequences. As described in the docs, these allow the use of terminal control characters that can, among other things, colorize our prompt. And that's what we're after. So let's turn this on it's head and look at what's within those brackets.

Inverting our highlighting somewhat:

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

So what do we have here? These, my friends, are terminal control codes. And they are the secret sauce we're looking for.

Isolated like this, it's easy to quickly pick out a pattern. Everything begins with %033[. (This is one of the things that was killing me initially. I kept trying to pick out a closing ]. Well, there isn't one!) And everything (except that K, which I will address last) ends with m.  Lets decipher that…

There's no end to the references you can find for terminal control codes if you Google for them. Here's a handy one that I find clearly laid out and understandable:

Scrolling to the bottom, we have the display codes.  I've pasted them here for convenience:

Set Attribute Mode

Display Attribute

0 Reset ALL
1 Bright
2 Dim
4 Underscore
5 Blink
7 Reverse
8 Hidden
Foreground Colors

30 Black
31 Red
32 Green
33 Yellow
34 Blue
35 Magenta
36 Cyan
37 White
Background Colors

40 Black
41 Red
42 Green
43 Yellow
44 Blue
45 Magenta
46 Cyan
47 White

And based on this format, now we can see that %033 is an escape sequence equivalent to <ESC> and m is, well... m!

So the way this works is that when we set a display attribute, foreground color, and background color; they will remain in effect until we change them or reset everything. Stepping through my example prompt (and using a very light gray background in lieu of white for readbility's sake), we have —

 1;31;40 > bold red foreground on black background 
   32    > green foreground 
   36    > cyan foreground 
   34    > blue foreground 
   36    > cyan foreground 
   33    > yellow foreground 
 K       > ...erase to the end of the current line      
 0       > reset all attributes 
 1;33    > bold yellow foreground 
 0       > reset all attributes 

Or, more specifically...

 ➤ psql://database_user@hostname:port/database         


Okay, almost there... But you might be scratching your head at this point. These colors look not even remotely similar to those in my screenshot posted above.

Well, that's where the terminal emulator you're using and the color scheme (theme) you've chosen come in. Your theme will determine what colors the emulator you're using will substitute for the standard black, blue, red, green, and so on.

In my case, I am using iTerm2 on OS X with the 'arthur' color scheme.  You might prefer something such as Solarized or Tango.  But that's is where the fun and creativity of customizing something to your personal taste comes in.  And I hope you now have enough of an understanding of how colors are applied to the psql prompt to go forth and roll your own.



Taming Your postgresql.conf Changes With Includes

A few weeks ago, my comrade +Douglas Hunley and I were working on a small project together for a customer that involved a fair number of changes to parameters in the Postgres database configuration file, postgresql.conf.  And being the ridiculously anal admin type that I am when it comes to the organization of and commentary throughout my config files for any service, I was beginning to have fits with the havoc this was wreaking upon the relative beauty that is postgresql.conf.  (I blame my years in design engineering working with engineering change requests for this particular trait.)

postgresql.conf is a masterpiece of a configuration file, being ridiculously well documented throughout with a plethora of commentary to boot and parameters grouped by category and functionality, rather than just a straight alphabetical listing.  The numerous edits being made, plus my penchant for thorough commentary on each change was breaking up the flow of the file.  The result was not making for easy reading.  And the more I tried to address that issue, the less the changes that were being made stood out.

And then I recalled those wonderful includes in the Apache config files I used to know and love, wondering if there was any chance Postgres might have a similar capability.  Praise be to the Postgres Docs, it does.  Just throw them at the end of the file and they'll override any previous settings!

Okay, so why do this?  Consider the elegant simplicity of organization includes provide...

Easily Set Standard Configs For Related Parameters

Say, for example, your organization has a standard logging config you want running on every server.  You might consider having a standard postgresql.conf file with these parameters set.  But what if there are physical differences between the servers that affect other parameters, such as work_mem, effective_shared_cache, etc.?  Or your WAL settings differ?  Or autovacuum?  You can easily see where this is going.

Organization That Self Documents

Whether you work with many databases or just one, you're eventually going to return to one after enough time has passed for you to have forgotten everything you (or someone else, for that matter) you'd set and/or why it was set that way.  Let's say your organization comes up with a standard naming convention for these files.  As I work for +EnterpriseDB (EDB), I might use this to name my files:

and so on.  Now, when you look at the directory listing it becomes readily apparent fairly quickly where to look for any custom settings I or my colleagues may have made, doesn't it.

More Thoroughly Documented Changes

Being a huge advocate of not only clear and thorough documentation within configuration files, but also maintaining a record within of previous settings, dates of and reasons for changes and so on; I find this method allows much clearer and more readable information.  Some night consider this overkill.  But if I'm tasked with troubleshooting why sorts & merges on disk have recently dramatically increased, and I take a quick stroll through a file that might be named edb_memory, finding an entry akin to the following:

# change date:      2013-08-01
# previous value:   20MB
# new value:        5MB
# change by:        jgraber@edb
# reason:           let's see what happens!

    work_mem = 5MB

I'm going to be torn between buying this jgraber guy a beer for great documentation of changes, and smashing the bottle over his head for monkeying with this for no apparent reason.  But at least I've potentially saved a tremendous amount of time and frustration wondering what happened.

(Yes, you could do this in postgresql.conf, no question.  But imagine what that already heavily commented file is going to become over time as these changes are made.)

Okay then, go include some stuff... and things!


Book Review — Instant PostgreSQL Starter

Following up on the positive experience I had recently with another of Packt Publishing's "Instant" titles for Postgres, I picked up a copy of Instant PostgreSQL Starter and dove right in.  (And I must say I'm enjoying these short format books.)

The first major section of the book seems targeted at someone with absolutely no experience, or perhaps minimal experience, with databases whatsoever.  Yet I still found it useful, as I'm coming to Postgres from an Oracle direction.  Simply having to walk through the installation process, perform the basic table creation, inserts, updates, queries, etc. that you will be walked through provided an easy way of becoming more familiar with the nuances of the database and its GUI admin tool, pgAdmin3.

As a brief aside — I think the author's choice of going down a fairly platform agnostic path by utilizing +EnterpriseDB's installer and interacting with the database through pgAdmin3, rather than psql in a shell, was a good one.  It provides a one size fits all approach for anyone to get up and running without having to delve into the nuances of shells on different systems, various installation methods, and so on.

What I think was the most valuable portion of the book for me was "Top 9 features you need to know about", which gives an overview of such topics as hashing passwords for storage, XML in Postgres, and full-text search, to name a few.  For someone coming to Postgres from another database, it's learning about these kinds of features that truly help you get up to speed a bit faster.  And I appreciate being able to get a brief overview of these topics in order to simply know about them and how they work, without becoming an expert in any (just yet!).


Book Review — Instant PostgreSQL Backup and Restore How-to

I recently was fortunate to receive an ePub copy of “Instant PostgreSQL Backup and Restore How-to” from Packt Publishing for review. The topic obviously high on the list of skills any DBA must master. And given my still relatively recent foray into the realm of Postgres from Oracle, quickly getting up to speed on this is certainly a top priority for me.

From the outset, the book does not disappoint. Like any of you, I'm sure, I have a ridiculous number of tech books in my library. This, however, is my first publication from Packt. And I must say, I definitely enjoyed the short, directly to the point format. I'll likely be taking a look at other Packt titles if for no other reason than that.

Author Shaun Thomas had my immediate gratitude and admiration for providing an extremely quick, concise method for creating a reasonably large database to work through the methods presented. Again, as a relative newcomer to Postgres, with no Postgres instances in use at my organization, I found this invaluable.

And without any delay, you will roll right into the basics of backup of your databases. Full, partial, data only, schema info, specific tables... it's all there within the first few pages. In only a few minutes after beginning the book, you have enough information to have a backup of your database underway. Well done.

At the time of this writing, I have not yet made it to the following chapters on more advanced topics. But there is no question I'm looking forward to them given my experience so far.

(And I would add that backup and recovery of Postgres seems immensely easier that what I've experienced with Oracle. The command line tools are outstanding, from what I've seen so far. And as usual, they a much more "Unix-like", for lack of a better term, than what Oracle provides. That is a huge plus, in my opinion.)

I'd highly recommend this book. Its concise coverage of the backup and recovery of Postgres will have you up and running in no time. And who has time to waste when it comes to developing proficiency in anything data related these days.


Big Cats & The Elephant

In my experience, it has been an exceedingly rare thing to be able to get a database up and running quickly and easily for the sole purpose of having one to play with.  Oracle is a beast to install, for example, and SQL Server loves to run you through endless wizards.  MongoDB was the notable exception to this, and this was therefore of the reasons that I first jumped on board so eagerly to give it a test drive.

But even the simplicity of downloading the MongoDB binaries for OS X and starting it up with the right flags on the command line falls short of the mind-numbingly incredible ease with which you can get PostgreSQL up and running on a Mac with Postgres.app!

What were are talking about here is

  • download
  • unzip
  • move to Applications
  • run it
No joke.  You now have PostgreSQL running on the default port of 5432.

The documentation will open by default.  I suggest adding the path to the Postgres binaries to your own PATH, and this information is provided in brief doc that will open.  This is not absolutely necessary, however, as one can start the psql shell (analogous to SQL*Plus) from the menu that will appear if you click on the small elephant that will appear in your menu bar.

While I plan to go through the steps to get Postgres started on CentOS Linux at a later date for more complex, multiserver configurations; I can't overstate how highly I recommend this for its elegant simplicity.  Many, MANY thanks to Mattt Thompson and Heroku Postgres for making this available to the masses!