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
reference