2014-09-24

I Shall Return... to Postgres Open

Last week, I had the opportunity to attend my first Postgres conference, that being +Postgres Open 2014.  Two days packed with a ridiculous amount of useful information and fantastic Postgres community interaction.  And now that the conference & sponsor t-shirts are washed, many new social media connections have been made, and the backlog of work email has been addressed —

Many Thanks

First off, the program committee deserves a veritable heap of congratulations and gratitude piled upon them from the attendees.  From the quality of the speakers to the well planned (and orchestrated) schedule, and even to the menu — everything was top notch. And therefore, I am calling you out by name, +Kris Pennella, +Gabrielle R, +Cindy Wise, +Stephen Frost, +Jonathan Katz, & +Selena Deckelmann.  Thanks for your time and effort!

The Talks

As with any (great) conference, the difficult part is the inability to attend multiple presentations at the same time, and thus being forced to choose.  The schedule this year offered no shortage of these situations.  But when your choices include community figures of the likes of +Bruce Momjian presenting on "Explaining the Postgres Query Optimizer" and +Simon Riggs with "How VACUUM works, and what to do when it doesn't", you simply can't go wrong.  Nonetheless, I wish I hadn't had to miss out on the talks given by +denish patel , +Álvaro Hernández Tortosa, +Gurjeet Singh, and  +Vibhor Kumar, to name but a few.

Fortunately, the talks were recorded and will be uploaded to the Postgres Open YouTube channel in about a month or so.  In the meantime, the presenters' slides are available on the Postgres Open wiki.


Not Just for DBAs

I particularly want to call +Dimitri Fontaine's talk, "PostgreSQL for developers" to the attention of developer crowd and as being an excellent example of why these conferences are not just for DBAs.  As a consultant, on virtually every job I see examples of poorly written SQL as being the root cause of a poorly performing database (even after proper tuning of config parameters).  And therefore, Dimitri's admonition that you must treat SQL just as you would any other language in which you write code rings particularly true with me.  I tweeted it once, and I'll say it again — this talk could lead to peace and love between developers and DBAs.  (Not to mention that the techniques he teaches in the examples walked through will blow your mind.)

Milwaukee's Best

On a related note, I'd like to point out that +pgMKE, Milwaukee's Postgres User Group, was well represented in Chicago.  +Jeff Amiel's talk on "Row-Estimation Revelation and the Monolithic Query" was described as being worth the cost of attending the conference.  And with myself and +Phil Vacca in attendance, we had a sizable portion of our merry band of less than 10 members present.  Not bad for a user group that's less than a few months old!

Postgres People

As I said at the outset, this was my first Postgres conference.  And I have to add that the people I met were the highlight of the whole affair.  It was a great group, many of whom I'd either never met, or only knew online.  It was readily apparent that the accelerating success of Postgres on many fronts is largely due to the efforts and energy of this community.  It was great to experience it first hand and in action.  It may be stating the obvious, but I'm already looking forward to next year.

2014-02-07

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
<ESC>[{attr1};...;{attrn}m

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.

Enjoy!

2013-08-21

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:
    edb_logging.conf
    edb_tuning.conf
    edb_vacuuming.conf


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!

2013-05-29

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!).

2013-05-05

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.