psql: Guardrails between PROD and TEST

This took way longer than I wanted

But I've got access to Prod and to Dev

I haven't gotten automated changes and change logging in yet

Doing things the old fashion way for the moment and running scripts by hand

So I want at least some guard rails and at very minum is prompt to the database that clearly indicates which database I'm on.

looks likes something redacted from a senate hearing

but prompts say "clone" or "produ" followed by IP address user#database

Clone database Production Database

put the following in ~./.psqlrc

select case when inet_server_addr() = 'X.X.X.X' then -- ' clone'
                 '%[%033[1;3;4;32m%]Clone %M %n@%/%R%#%x %[%033[0m%] '
            when inet_server_addr() = 'Y.Y.Y.Y' then -- 'prod'
                 '%[%033[1;3;4;31m%]Prod %M %n@%/%R%#%x %[%033[0m%] '
            Else --'unknown '
                 '%[%033[1;3;4;35m%]Unknown %M %n@%/%R%#%x %[%033[0m%] '
            END AS prompt \gset
;

\set PROMPT1 :prompt



-- %M refers to the database server’s hostname – is “[local]” if the connection is over a Unix domain socket
-- %> refers to the listening port
-- %n refers to the session username
-- %/ refers the current database
-- %R refers to whether you’re in single-line mode (^) or disconnected (!) but is normally =
-- %# refers to whether you’re a superuser (#) or a regular user (>)
-- %x refers to the transaction status – usually blank unless in a transaction block (*)

-- 31 for red
-- 32 for green
-- 33 for yellow
-- 34 for blue
-- 35 for magenta
-- 36 for cyan
-- 37 for white


-- % infront of [ gets the [ out of the prompt
-- [1;  is bold
-- [2;  faded?
-- [3;  italic
-- [4;  underline
-- [5;  blinking
-- [1;3;4;  bold underline italic  

UPDATE:

I'm now using tunneling from my laptop to prod which mess up my prompts. Here is a revised example:

\set QUIET on
\o /dev/null


-- \set CLONE1 'xx.xx.xx.xx'
-- select case when inet_server_addr() = ':CLONE1' then -- 'kyle clone'

-- %n refers to the session username
-- %/ refers the current database
-- %R refers to whether you’re in single-line mode (^) or disconnected (!) but is normally =
-- %# refers to whether you’re a superuser (#) or a regular user (>)
-- %x refers to the transaction status – usually blank unless in a transaction block (*)
-- %>: Port
-- %M is “The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket”.

-- 31 for red
-- 32 for green
-- 33 for yellow
-- 34 for blue
-- 35 for magenta
-- 36 for cyan
-- 37 for white

\set my_host :HOST
\set my_port :PORT
select
   case when :'my_host' = 'localhost' AND inet_server_addr() is NULL  then
      case when :'my_port' = '5432'  then -- 'local host + 5432 -> should be tunning to prod'
                 '%[%033[1;3;4;31m%] Prod localhsot:%>  %n@%/%R%#%x %[%033[0m%] '
      END
   Else
      case when inet_server_addr() = 'xx.xx.xx.xx' then -- 'prod'
                 '%[%033[1;3;4;31m%] Produ %M %n@%/%R%#%x %[%033[0m%] '
            Else --'unknown '
                 '%[%033[1;3;4;35m%] %M:%>  %n@%/%R%#%x %[%033[0m%] '
       END
    END AS prompt \gset
;
-- \set PROMPT1 :prompt
\set PROMPT1 '%`date  +%D-%H:%M:%S`' :prompt


\pset pager off
\setenv LESS -FX
select now() ;
set lock_timeout to '200ms';
set statement_timeout to 0;
\o
\set QUIET off
-- select now() ;
show lock_timeout ;
show statement_timeout ;
\timing

reference

https://www.digitalocean.com/community/tutorials/how-to-customize-the-postgresql-prompt-with-psqlrc-on-ubuntu-14-04

https://www.depesz.com/2013/02/03/waiting-for-9-3-create-a-psql-command-gset-to-store-query-results-into-psql-variables/