At a company we have literally thousands of Pg servers. The layout is also kinda non-obvious. Each database is named the same, but contains different data. And in front of it all, we have pgbouncers.
After some talk, it was suggested that perhaps we could make psql prompt show which database it is connected to. And perhaps some more information, like backend pid. I thought it will be simple…
For starters – we have a function defined, named dba.smi which returns a bit of trivia about the server we run it on. It's based on text files that exist on the filesystem and contain information like:
- project name
- environment (production, beta, test)
- cluster (in our case cluster is simply a number (usually). Each cluster means at least 3 separate databases: primary, secondary, and report. For historical reasons source data is using old vocabulary: master, slave, and backup.
All of these can be fetched with calls to dba.smi(), and every user in system can call this function. Great. Trivial task. Wrote psqlrc like this:
SELECT dba.smi('aws_tag_project') AS project, dba.smi('aws_tag_environment') AS env, dba.smi('aws_tag_cluster') AS cluster, dba.smi('aws_tag_pgrole') AS pgrole \gset smi_ \SET PROMPT1 '%`date +%H:%M:%S` %:smi_project: %:smi_env:, cluster %:smi_cluster:, %:smi_pgrole: db: %n@%/, pid:%p\n%x%R%# '
After loading it, I got beautiful prompt:
15:39:31 magic production, cluster 1, backup db: depesz-rw@appdb, pid:22025 =>
Tested it on database server connected to app database, all works well. Success.
Well, no. when I connected to another database, like postgres, I got:
psql:/home/depesz/.psqlrc:5: ERROR: schema "dba" does NOT exist LINE 2: dba.smi('aws_tag_project') AS project, ^ 15:41:22 , cluster , db: depesz-rw@postgres, pid:23229 =>
Well, that ain't nice. I tried to hide it with adding \set QUIET in the beginning, and \unset QUIET at the end, but it didn't help.
Luckily, psql has conditionals. So I can first check if the function is there. While working on it I also discovered that smi returns old names of pgroles, so I have to map it to new ones. Trivial. New version:
\SET QUIET SELECT EXISTS( SELECT 1 FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE p.proname = 'smi' AND n.nspname = 'dba' ) \gset smi_ \IF :smi_exists SELECT dba.smi('aws_tag_project') AS project, dba.smi('aws_tag_environment') AS env, dba.smi('aws_tag_cluster') AS cluster, CASE dba.smi('aws_tag_pgrole') WHEN 'master' THEN 'primary' WHEN 'slave' THEN 'secondary' WHEN 'backup' THEN 'report' ELSE dba.smi('aws_tag_pgrole') END AS pgrole \gset smi_ \SET PROMPT1 '%`date +%H:%M:%S` %:smi_project: %:smi_env:, cluster %:smi_cluster:, %:smi_pgrole: db: %n@%/, pid:%p\n%x%R%# ' \ELSE \SET PROMPT1 '%`date +%H:%M:%S` db: %n@%/, pid:%p\n%x%R%# ' \endif \unset QUIET
Amazing. Now, when I try to connect to database that doesn't have dba.smi(), I just get plain prompt:
16:04:14 db: depesz-rw@postgres, pid:5144 =>
All is well, and am ready to push to production. Just sanity check, I'll connect, like any user would, through a pgbouncer, that's just formality. Right? Not quite:
16:05:40 magic production, cluster 1, report db: depesz-rw@appdb, pid:830285059 =>
Well, it looks OK, but the pid is definitely incorrect. Apparently whatever psql displays as pid (%p) is not really pid when connecting through pgBouncer.
Luckily, that is simple to work around. I will just get the pid using select query:
SELECT pg_backend_pid() \gset
and then, instead of %p, will use %:pg_backend_pid:.
Simple, and works.
But then it hit me: what will happen if I will connect to the special, magical, pgbouncer database? Users don't do it, but I might. In case you don't know – when you connect through pgbouncer, there is special “database" named pgbouncer where you can connect, and display some info from pgbouncer itself. Basically admin interface for pgbouncer.
Surely it will work, right? Well, no:
psql:/home/depesz/.psqlrc:3: ERROR: invalid command 'SELECT pg_backend_pid() ', USE SHOW HELP; psql:/home/depesz/.psqlrc:10: ERROR: invalid command 'select EXISTS( SELECT 1 from pg_proc p join pg_namespace n on p.pronamespace = n.oid where p.proname = 'smi' and n.nspname = 'dba' ) ', USE SHOW HELP; psql:/home/depesz/.psqlrc:12: error: unrecognized VALUE ":smi_exists" FOR "\if expression": BOOLEAN expected
Apparently this magical pgbouncer database doesn't handle selects. At all:
=# SELECT 1; ERROR: invalid command 'select 1;', USE SHOW HELP;
Well, OK. But that's simple. I will just check if database name is pgbouncer, and if yes – use simpler prompt. And when it's not – use normal code.
psql even helpfully has DBNAME variable, so it should be easy. Right?
=# \IF :DBNAME = pgbouncer unrecognized VALUE "pgbouncer = pgbouncer" FOR "\if expression": BOOLEAN expected
Apparently psql's \if expression can only parse single value, and it has to be one of: true, false, 1, 0, on, off, yes, no.
So, how to get one of these, let's say 1/0, if the dbname is pgbouncer? I can't use select. I can't use logic in \if…. cue despair.
Luckily, in docs I found this bit:
The expression argument of an \if or \elif command is subject to variable interpolation and backquote expansion
backquote. So I can call shell! Solution became obvious:
\IF `test :DBNAME = pgbouncer && echo 1 || echo 0`
this requires that we have test program installed, but it's by default everywhere, so I don't care. Much.
So, all things said, finally the psqlrc is:
-- Hide confirmation messages \SET QUIET -- Check if we're connecting to database pgbouncer. It is very limited, and can't run SELECT's \IF `test :DBNAME = pgbouncer && echo 1 || echo 0` -- This is pgbouncer. User plain prompt \SET PROMPT1 '%`date +%H:%M:%S` db: %n@%/\n%x%R%# ' \ELSE -- This isn't pgbouncer. So we should have SELECTs available -- If we're connecting through pgbouncer, then prompt's %p is not real, so get real PID SELECT pg_backend_pid() \gset -- Check if there is dba.smi() function SELECT EXISTS(SELECT 1 FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE p.proname = 'smi' AND n.nspname = 'dba') \gset smi_ \IF :smi_exists -- If dba.smi() exists, get data from it, and put it in prompt. SELECT dba.smi('aws_tag_project') AS project, dba.smi('aws_tag_environment') AS env, dba.smi('aws_tag_cluster') AS cluster, CASE dba.smi('aws_tag_pgrole') WHEN 'master' THEN 'primary' WHEN 'slave' THEN 'secondary' WHEN 'backup' THEN 'report' ELSE dba.smi('aws_tag_pgrole') END AS pgrole \gset smi_ \SET PROMPT1 '%`date +%H:%M:%S` %:smi_project: %:smi_env:, cluster %:smi_cluster:, %:smi_pgrole: db: %n@%/, pid:%:pg_backend_pid:\n%x%R%# ' \ELSE -- If dba.smi() is not available, pick simpler prompt \SET PROMPT1 '%`date +%H:%M:%S` db: %n@%/, pid:%:pg_backend_pid:\n%x%R%# ' \endif \endif -- Common settings, regardless of dba.smi() existence \SET PROMPT2 '%R%# ' \pset NULL '[null]' -- Use sensible pager \pset pager always \setenv PAGER 'less -iMFXSx4R' -- It's 21st century, let's use unicode for borders \pset linestyle unicode -- Stop hiding confirmation messages \unset QUIET -- vim: set ft=sql:
and it does what I wanted it to do. Installed it as /etc/postgresql-common/psqlrc everywhere, and it works.
When I showed it to colleague, he said: I had no idea psqlrc files could be so complex. And this kinda prompted me to write this blogpost. They (psqlrcs) can. They can do a lot of things, and if writing them doesn't seem simple – it's because we rarely do. But, I think, it's worth it.
Excellent article!
Looked really cool, but when I tried this I got:
psql:/etc/postgresql-common/psqlrc:2: error: invalid command \SET
psql:/etc/postgresql-common/psqlrc:5: error: invalid command \IF
psql:/etc/postgresql-common/psqlrc:8: error: invalid command \SET
psql:/etc/postgresql-common/psqlrc:10: error: invalid command \ELSE
psql:/etc/postgresql-common/psqlrc:20: error: invalid command \IF
psql:/etc/postgresql-common/psqlrc:31: ERROR: schema “dba” does not exist
LINE 2: dba.smi(‘aws_tag_project’) AS project,
^
psql:/etc/postgresql-common/psqlrc:32: error: invalid command \SET
psql:/etc/postgresql-common/psqlrc:33: error: invalid command \ELSE
psql:/etc/postgresql-common/psqlrc:35: error: invalid command \SET
psql:/etc/postgresql-common/psqlrc:36: error: \endif: no matching \if
psql:/etc/postgresql-common/psqlrc:38: error: \endif: no matching \if
psql:/etc/postgresql-common/psqlrc:41: error: invalid command \SET
psql:/etc/postgresql-common/psqlrc:42: error: \pset: unknown option: NULL
Pager is always used.
Line style is unicode.
psql (13.3 (Ubuntu 13.3-0ubuntu0.21.04.1), server 12.6 (Ubuntu 12.6-0ubuntu0.20.10.1))
OK figured it out, your code use \SET where it should say \set lowercase.
@Berend:
sorry, this is becaue of wp-geshi-syntax plugin for syntax highlighting. Looked for solution, and I think I found it. It should no longer be broken.
In case someone found this by googling – to disable uppercaseing of keywords in wp-geshi-syntax, you have to edit file wp-content/plugins/wp-geshi-highlight/geshi/geshi/sql.php (or whatever the language you want to disable it for), and change CASE_KEYWORDS to 0 or GESHI_CAPS_NO_CHANGE.