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 ;
\timingreference