Production psql Prompts

I deal with lots of production.  When you’re dealing with multiple production machines, it’s important to know many things:

  1. Who you are
  2. What you’re connected to
  3. Where you are
  4. Why you are connected
  5. When you are connected

Most prompts don’t give you any of this detail.  If you’re familiar with bash, you probably have heard of PS1, it’s an environment variable that lets you set what your prompt looks like.  psql has something very similar.  The question becomes, how do I get a useful prompt that is compact and doesn’t cause all of my commands to wrap off of the screen?

Let’s start with bash, most people are familiar with the default linux prompt (debian and redhat differ slightly in their defaults) of:

[root@ansible-test-1 ~]#

One day, I realized that wasn’t enough.  I had a lot of tabs open in my terminal emulator and I was ssh’d three ways from Sunday.  Many of those tabs were executing long running commands, and I couldn’t quickly look back to see how long it had been running for (unless I remembered to execute the `date` command prior to starting my job).  That’s when I came up with this prompt:


This tells me most of what I’d like to know.  I have:

[user@host : Full Path to current directory: Time the prompt was generated]
$ <your command gets entered here>


You can accomplish this with:

export PS1=[\u@\h : \[\e[4;33m\]\w\[\e[0m\] : \[\e[0;94m\]\t\[\e[0m\]]\n$

I did have one issue, in order to see the full path, I had to put the next command on the line below.  I’ve actually come to appreciate it quite a bit.  Essentially, I have a line that tells me: Who @ What: Where : When.  (I guess why I’m there is something I should probably already know : -)

Now, I decided to apply the same design to my psql prompt so that I knew what was going on.  psql prompt

You can accomplish this by putting the following in your .bashrc:

alias psql='psql -v PROMPT1=[`hostname -s`'\''@%m:%>]
%n@%/%=%# '\'''

It’s important to note, the line wrap, you have to actually enter that or this won’t work.  Essentially, I have to have bash set the ‘hostname’ in the prompt (which is why this isn’t in .psqlrc).

Why do I think this prompt is better than the default?

This prompt tells me:

  1.  scottsmac : What host psql is running from
  2. [local]         : What host psql is connected to
  3. 9520            : What port psql is connected to
  4. newline
  5. postgres      : What user psql is connected as
  6. @postgres  : What database psql is connected to

This, again gives me ‘who, what, where, why and when’ … okay, not the ‘why’ but that’s really up to you.





Extending Postgres by BigSQL

Recently we had a customer request to build a custom extension against Postgres by BigSQL distribution. Even though BigSQL ships with a large set of commonly used extensions and good collection of FDWs, these kind of user build requirements always crop up, based on how powerful the Postgres extension model is.

BigSQL makes it easy to build custom extensions for new requirements by including all of the required header files and libraries along with the Postgres by BigSQL installation. This is one of the key benefits of the open source BigSQL distribution as opposed to a proprietary open source-based approach.

One may think that, it will be a challenge because a BigSQL user has the freedom to relocate the the installation anywhere he/she wants or have a DevOps Sandbox. That means Postgres related files can exist anywhere the user wants. In this blog I’ll show you how easy it is. Here is an example of how to build extensions, where postgres binaries sits in my user home directory (I am using DevOps Sandbox on CentOS 6.7)

Building extension from Source

Extension build will use pg_config to understand the the location of the files. So only thing we have to do is to set the path to pg_config wherever it is:

export PATH=/home/vagrant/bigsql/pg95/bin:$PATH

After that, building any custom extension should be a breeze.
Get the required branch of extension:

git clone -b VERSION3_1_STABLE

Install any external library requirements

$sudo yum install openssl-devel.x86_64
$sudo yum install readline-devel

(of course, we need build tools like gcc and make)


The build should go through without any issues.

Getting Extension from PGXN (PostgreSQL Extension Network)

The Postgres community has many extensions available, and PGXN makes it easy to find and install open source extensions published by other community members. Postgres by BigSQL works perfectly with PGXN.

Just like above case, we need to add the path to pg_config before executing the PGXN

$ export PATH=/home/vagrant/bigsql1/pg95/bin:$PATH

Now we can install any extenion we need like:

$ pgxn install temporal_tables

Once installation complete, extension will be ready to use

postgres=# create extension temporal_tables;

Up Next

We just covered how easy it is to build and install PostgreSQL extensions on Linux. In my next blog post, I’ll show how to build and install extensions on Windows. Postgres by BigSQL uses Mingw (and gcc) to compile Postgres and related extensions on Windows which is great because it keeps the full tool chain open source (no proprietary compiler required) and enables extension authors to support Windows without making an MSVC specific build system or code and configuration changes.

Quick stats with pgBadger

A while back, I wrote about ‘Helpful Postgres logging and defaults‘.  The real question is, how helpful are these, really?  So, let’s take a quick look at how we can use the increased logging in order to do something useful.  If the previous post is TL;DR, here’s the quick list:

logging_collector = on 
log_filename = 'postgresql-%a.log' 
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_temp_files = 0
log_lock_waits = on

Essentially, these cover the basics when it comes to monitoring, without adding a whole lot of stuff to your logs.  These settings are default in the BigSQL sandboxes so that you’re running out of the gate.  If you’ve installed PostgreSQL another way, you can simply modify your postgresql.conf file to include these items.

Follow along

Quick Install

If you’d like to follow along, you can quickly setup a test pg96 environment by using the ‘standalone’ installer for postgres that BigSQL provides.  If you already have PostgreSQL, you can skip this section.

Grab BigSQL

scottm: ~ $ python -c "$(curl -fsSL"
Downloading BigSQL PGC 3.0.0 ...
Unpacking ...
Cleaning up
Setting REPO to
Updating Metadata
 BigSQL PGC installed. Try 'bigsql/pgc help' to get started.

Install PostgreSQL 9.6.current

scottm: ~ $ cd bigsql/
 scottm: bigsql $ ./pgc init pg96
 scottm: bigsql $ ./pgc install pg96
 Get:1 pg96-9.6.0-1b-osx64
 Unpacking pg96-9.6.0-1b-osx64.tar.bz2

Init pg96

scottm: bigsql $ ./pgc init pg96
## Initializing pg96 #######################
Superuser Password [password]:
 Giving current user permission to data dir
Initializing Postgres DB at:
 -D "/Users/scottm/bigsql/data/pg96"
Using PostgreSQL Port 5432
Password securely remembered in the file: /Users/scottm/.pgpass
to load this postgres into your environment, source the env file:

Start pg96

scottm: bigsql $ ./pgc start pg96
 pg96 starting on port 5432

Since the BigSQL package automatically sets the appropriate logging defaults, we’re set to go!

Modify Existing PG

  1. open up your postgresql.conf file
  2. place the above values at the end of the file
  3. save & close postgresql.conf
  4. restart postgres (if you already had ‘logging_collector=on’, you can just reload).


Now that you’re logging, it’s time to generate some workload.  My database isn’t actually serving clients at this point, so, I’m going to use the included pgbench tool to generate some data and traffic. (NB: If you run this step, you’ll use up about 2.5 GB of disk space).

pgbench -i -s 100 -U postgres

# This initializes a new pgbench database with a ‘scaling factor’ (i.e. sizing factor) of 100 bank branches (pgbench simulates debit/credit).  You’ll see a bunch of output fly-by as you initialize 10,000,000 rows (tuples) of data.  Don’t worry, this only translates to about 2.5 GB of space.


Great!  Now we’re logging at a verbosity that lets us see what’s going on in PostgreSQL, how do I take advantage?  Enter pgBadger; this tool is essentially a high-performance log parser for the postgresql information logs.  If you’re following along with my BigSQL install from above, you can grab pgBadger with:

scottm: bigsql $ ./pgc install pgbadger
Get:1 pgbadger-9.0
 Unpacking pgbadger-9.0.tar.bz2

If you’re not using the BigSQL package, you can:

git clone

Now, let’s run pgBadger against the logs.

If you’re following along:

scottm: bigsql $ perl pgbadger/pgbadger data/logs/pg96/postgresql-*.log
[========================>] Parsed 8993 bytes of 8993 (100.00%), queries: 0, events: 2
LOG: Ok, generating html report...
scottm: bigsql $

By default, pgBadger puts its output in a file called ‘out.html’ (see perl pgbadger/pgbadger –help for the flag to change this).

I’m going to open up the ‘out.html’ file in my web browser and view my report (I’ve attached the out.html report I generated for this blog so you can download it and play if you’d like).


pgBadger empty overview

Hmmm, looks like I have an empty report?  Strange?  Well, keep in mind, we’ve told postgres to log certain things:

  •  checkpoints
  • Vacuum
  • temp files
  • lock waits

We never told postgres to log queries (we’ll come back to this in a minute).  For now, let’s take a look at the ‘checkpoints’ and see what’s there.

Choose Checkpoints


Once we choose the ‘Checkpoints’ item from the top menu, we’ll see that our data-load created some activity for us (your data will most likely look somewhat different).

Screen Shot 2016-10-21 at 4.45.47 PM

Now I’m starting to see data about my checkpoint buffer utilization!  Cool!  I can actually see how memory is being used in the server.  This is coming out of the ‘log_checkpoints’ parameter that we set earlier.

To get richer data, we need to push more activity against our DB server.  With pgBench:

scottm: bigsql $ pg96/bin/pgbench -T 300 -c 10

This will run a 5 minute (300 second) test with 10 concurrent client sessions.

Once that’s complete, re-run pgBadger:

scottm: bigsql $ perl pgbadger/pgbadger data/logs/pg96/postgresql-*.log
[========================>] Parsed 38636 bytes of 38636 (100.00%), queries: 0, events: 2
[========================>] Parsed 3024 bytes of 3024 (100.00%), queries: 0, events: 2
[========================>] Parsed 24694 bytes of 24694 (100.00%), queries: 0, events: 2
LOG: Ok, generating html report...

(Your output may appear slightly different based on the number of log files that you have)

Now, your ‘out.html’ will have data based on your workload.

Badger with WorkloadWe also have some interesting data surrounding VACUUM behavior:Vacuum DataIt should be noted, these are just quick screen grabs, take a look at the attached ‘out.html’ for more.


The point is, using some wise logging defaults gets you some awesome pgBadger joy, without paying a TON in logging overhead.  Stay tuned for part 2 where I’ll crank up logging even more and show you how to get per-statement data.



Relocatable PG RPMs

Relocatable PostgreSQL RPMs – You asked and we delivered!

In our years of dealing with PostgreSQL users, one of the main gripes they have with PostgreSQL is not a missing feature or capability but rather it is the fact that the provided rpms are non relocatable rpms. The reasons for wanting relocatable rpms are many including the company they work for has specific standards or they are installing in a lab with other installations and they want to keep their install separate or they just simple want to place things in a more logical/accessible place.

Before now, the only ways available to users to do this were to use the graphical installer or build Postgres from source.  If you are setting up a Devops environment on Linux or the cloud, using a graphical installer doesn’t work well and, let’s be honest, who wants to be pestered  with advertising while doing an installation? Building Postgres from source is definitely another option but most DBAs don’t have the time to deal with working through that process and handling any errors that might occur.  They could hand off the task to their dev or sys admin team but then they will need to wait for that to happen and/or lose control of the way it is built. And besides, you want a consistent binary build on each server in your organization, not a compiled from source version that uses different versions of libraries or config settings on different servers over time.

So, in our Postgres by BigSQL distribution, we decided to make it easy for users to install Postgres wherever they want simply by using the 100% open source rpm that we build and make available to everyone. Our rpm does not modify system libraries and by default installs to a user friendly location:


But you have total control - you simply use the —prefix <path> option to tell the package manager to install the software where you want.  For example:
# rpm -ivh --prefix /home/postgres/PG96 postgresql-9.6.0-1-x64-bigsql.rpm
warning: postgresql-9.6.0-1-x64-bigsql.rpm: Header V3 DSA/SHA1 Signature, key ID 38379aec: NOKEY
Preparing...                   ################################# [100%]
Updating / installing...
   1:postgresql96-9.6.0-1      ################################# [100%]
PostgreSQL 9.6 is now installed in /home/postgres/PG96.

To initialize, run /etc/init.d/postgresql-96 start as root user.

And there you have it….PostgreSQL installed where you want it!

Now that you have PostgreSQL installed, be sure to check out how to take the next steps and initialize the PostgreSQL database and use it here:


Why Open Source over Open Source Based?

The Postgres landscape has become very competitive in recent years as the database project has matured and grown exponentially in popularity. Since more and more users are deploying Postgres to support a wide variety of applications, there is a need for experts to help make sure those deployments are successful. This has led to the birth of a number of Postgres focused companies as well as existing companies either expanding or creating a Postgres practice. All in all, this growth of expertise is a good thing for the Postgres community. More people using it makes it a better product and when more companies are using it and hiring Postgres talent, many give back to the community thus making the original Postgres project stronger and better each year. That is one of the main benefits of open source, i.e joined forces working together for the betterment of the project. The other benefit is, of course, that is it 100% free to use.

However, there is a often misunderstood or, more accurately, misrepresented side of this story. Over the years, many folks have taken the hard work of the community and turned it into something they call ‘open source based’ solutions. There is nothing actually wrong with anyone doing this and there certainly isn’t anything that prevents this in the Postgres world. The issue at hand is how these solutions are represented. Many of these companies incorrectly market themselves as open source companies in an effort to ride the open source wave and take advantage of the market’s momentum. These so called ‘open source based’ solutions, to be perfectly clear, are not open source. They are proprietary solutions which means that in order to use them, you must pay someone for the right to use it. The issue here is not that someone is trying to build a business on their own solutions but rather how they cause confusion in the market. These companies often walk a fine line when discussing or marketing their solutions by saying ‘open source’ but when the marketing layers are peeled back, you find it is a proprietary product with the same traps as traditional commercial database solutions.

The reason this distinction is so important is that more and more companies/people are looking to get away from proprietary databases today to avoid the pain of being locked into any one technology, paying too much for a commodity and having to deal with vendors that will historically raise prices as they see fit. Using a true open source database means you are not tied to any one vendor, ever. You can choose to pay for support or services at any time you like but you have the freedom to stop paying whenever you want and still continue to use the open source database. With open source based solutions, they make it seem like you are free to do the same but if you decide to stop paying for the right to use, then you must de-install or risk being out of compliance and liable. That means you are forced into a migration effort.  Who wants to be in that situation?

The solution is to skip the open source based solution altogether. Yes, the move to a pure open source database from a proprietary solution may take a little longer and thus cost a bit more but it is a one time cost and the end result: being free of technology lock-in and being free of vendor lock-in have substantial long time benefits thus you will see a return on the investment relatively quickly. Even those open source based solutions that promise you a easier migration and quicker return on investment fail to point out that the migration may still take quite some time and be difficult(so much so that it is nearly the same effort to rewrite things).

There are also a bunch of options available to you to still get support for your open source databases and you don’t risk losing the type of quality support you are accustomed to getting from a proprietary vendor.  That substantially reduces the perceived risk of using open source databases. Industry experts such as Gartner have acknowledged that open source databases have matured to the point that they are viable alternatives to commercial databases and that in the coming years, large percentages of applications will either be developed or migrated to open source databases, not open source based databases.

The choice is ultimately yours but do yourself the favor and do the appropriate due diligence and make sure all paths and associated risks are clearly understood.

Two great things that taste great together: PostGIS and PostgreSQL


In my past life as a geospatial DBA, I had to navigate users and managers who belonged to different database teams (PostgreSQL, MySQL, SQL Server, Oracle, etc). When I was lucky enough to work in a shop that supported open source solutions, I was often asked by members of team MySQL, Why should we use PostgreSQL as our geospatial database when MySQL has spatial data types?” 

The answer: PostGIS.

Sure, MySQL has the ability to run spatial analysis (with some prodding). But PostgreSQL + PostGIS wins in:

  • functionality
  • performance
  • adoption by 3rd party solutions (QGIS, ArcGIS Server, GeoServer…)

So if you like to do things the free and easy way, go with PostgreSQL and PostGIS.

Screen Shot 2016-10-14 at 10.35.27 AM

Holy Easy PostgreSQL deployment

Holy Easy PostgreSQL deployment!

In case you missed it, the BigSQL team released an awesome package manager for installing and configuring PostgreSQL and many related, useful components. The package manager can be found here:

Playfully named pgc, for ‘pretty good command line’, pgc is a utility similar to yum or apt-get that allows you to install, configure, update and manage Postgres related components including foreign data wrappers, stored procedure languages, connectors, devops tools, HA tools and monitoring tools. Common uses:

  • Provision Postgres (9.2 through 9.6, including multiple versions on same server)
  • Installing pgBouncer, Backrest, and other community projects
  • Scripting configurations in chef or other devops tools

PGC runs on Linux, Windows and OS X and supports the same exact cli so it is an ideal provisioning/management tool for multi OS environments.

PGC not only allows you to get and install these components but you can use pgc to update each component as new updates become available. Read more

Hello, Hola, Salut, Ciao, Hallo, Ola’

new_me Hey everyone!

My name’s Douglas Hunley, and I recently joined OpenSCG as a Sr Architect. I’ve been working with PostgreSQL since around version 7.3.4 (back in ’03) and I’m a certified PostgreSQL DBA (and trainer), an RHCSA, and recently a certified AWS Technical Professional.  Several lifetimes ago I was also a certified Oracle DBA on 8i (what can I say? I was young and stupid). I’m really excited to be joining OpenSCG in their technical services organization and I’m pretty stoked about our BigSQL offering too! I’ll be blogging here from time to time about various PostgreSQL and OpenSCG things and I’m also now managing the @openscg twitter account, so give me a shout there if you like!

And in case I don’t see you, good afternoon, good evening, and goodnight! :)

Why are you still paying for your GIS when you can get it for free?

If you haven’t switched to open source solutions (FREE!), then you have probably fallen for some common misconceptions/myths:


MYTH: Open source tools are buggy. 

Does software have bugs? Of course. It was made by humans! But open source has the benefit of a strong community that can crowdsource fixes because the code base is not proprietary (open source!) And, by the way, If you have been using the most popular proprietary GIS software (wink,wink) for more than 5 years, you know all about bugs.

MYTH: Free GIS software has a limited toolbox for analysis. 

Well this depends. Let’s refer to the 80/20 rule here. When applied to GIS, 80% of your analysis can be processed with 20% of the tools / algorithms available for a spatially enabled database. If you are a bada$$ spatial stats expert and love your proprietary tools, then by all means stick with that expensive stack (also take a look at PySAL). But if you are like most of us (roughly 80%), you can accomplish your analysis with the FREE stack.

MYTH: Open source tools are impossible to install and administer.

Granted, this has been true in the past. But the open source community has made great strides in creating tools that don’t require you to have an engineering degree to stand-up a fully functioning GIS stack. And, because the community is large (and committed), you can find a ton of tutorials and documentation on the web.

MYTH: PostGIS is fine for hobbyists, but it can’t support corporate needs.

Actually, more and more companies and government agencies are turning to PostgreSQL/PostGIS for their geospatial needs: Uber, FourSquare, NOAA, and Carto just to name a few.

In upcoming posts we will show you how to install new open source (FREE!) tools from BigSQL (powered by OpenSCG) to help you build your open source GIS stack.

Just think of all the cool stuff you can buy from the Public Lab with the money you will save…

Auditing database changes using pgaudit

It is a very common requirement in the database world to audit database activities such as Sarbanes-Oxley financial regulations or service level impacts. Reason for auditing, granularity of audit information and type of auditing may differ. The PostgreSQL Audit extension (pgaudit) for Postgres provides most of the necessary features for auditing. The BigSQL project ships the ready-to-use pgaudit extension along with Postgres binaries for different platforms.


Let us explore how easy it is to set it up along with Postgres 9.6

PostgreSQL should be loading the pgaudit extension libraries during start up. To achieve this, we just need to specify a parameter and restart the PostgreSQL instance.

 postgres=# alter system set shared_preload_libraries='pgaudit';
$ ./pgc restart pg96
pg96 stopping
pg96 starting on port 5432

Its good to verify this step once postgres instance comes up

postgres=# select name,setting,pending_restart from pg_settings where name='shared_preload_libraries';
name                      | setting | pending_restart
shared_preload_libraries  | pgaudit | f

Example Use Case

A typical case of auditing is the change control in a controlled production environment. Security policies may require every change to database structure (DDLs) be audited / auditable.

Enabling DDL auditing at Instance Level

 postgres=# alter system set pgaudit.log = 'ddl';

This doesn’t require a bounce.

**pgaudit allows us to have more granularity at the database level or even at the particular role level. Please see the official documentation for all options available and extensive features.

Testing the DDL Audit.

Lets try creating a new table

postgres=# create table t1 (id int,name varchar(30));

Postgres keeps all audit information in postgresql’s standard log file as specified by log_directory and log_filename parameters.

Now the following audit lines appear in the log file:

2016-09-15 08:05:42 EDT [4143]: [9-1] user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: SESSION,2,1,DDL,CREATE TABLE,,,"create table t1 (id int,name varchar(30));",<not logged>

Let’s try altering the table:

postgres=# alter table t1 alter  column name type varchar(50);

The following line appears in the log file:

2016-09-15 08:08:10 EDT [4143]: [18-1] user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: SESSION,3,1,DDL,ALTER TABLE,,,alter table t1 alter  column name type varchar(50);,<not logged>

Now let’s try creating a function, I am going to use Javascript (V8):

postgres=# CREATE FUNCTION plv8max (a integer, b integer)
postgres-# RETURNS integer AS $$
postgres$#   if (a > b)
postgres$#     return a;
postgres$#   else
postgres$#     return b;
postgres$# $$ LANGUAGE plv8;

This is resulting in following audit entry:

2016-09-15 08:10:15 EDT [4143]: [19-1] user=postgres,db=postgres,app=psql,client=[local] LOG:  AUDIT: SESSION,4,1,DDL,CREATE FUNCTION,,,"CREATE FUNCTION plv8max (a integer, b integer)
RETURNS integer AS $$
if (a > b)
return a;
return b;
$$ LANGUAGE plv8;",<not logged>


The PgAudit extension can audit not only DDLs, But SELECTs, INSERTS, UPDATES, etc. as well. We should also note that extensive auditing can cause excessive I/O in the system and hurt the overall system performance.