top of page
  • Writer's picturekyle Hailey

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  


22 views0 comments
bottom of page