定制你自己的psql
定制你自己的psql
在一家公司，我们有成千上万的PostgreSQL服务器。这种布局也有点不明显。每个数据库的名字都一样，但包含不同的数据。而在这一切的前面，我们有pgbouncers。
经过讨论，有人建议，也许我们可以让psql提示，以显示它连接到哪个数据库。也许还有一些信息，比如后端pid。我认为这很简单...
作为开胃菜，我们定义了一个函数，名为dba.smi，它返回我们所运行的服务器的一些琐碎东西。它基于文件系统中的文本文件，其中包含的信息有：
•项目名称•环境（生产、测试、试验）•集群（在我们的例子中，集群通常只是一个数字。每个集群意味着至少有3个独立的数据库：主库、备库和报告(report)数据库。由于历史原因，源数据使用旧的词汇：主、从、和备份。
所有这些都可以通过调用dba.smi()来获取，而且系统中的每个用户都可以调用这个函数。很好，琐碎的任务。像这样写psqlrc。
selectdba.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%# '
加载后，我得到了友好的提示。
15:39:31 magic production, cluster 1, backup db: depesz-rw@appdb, pid:22025=>
在连接到应用程序数据库的数据库服务器上进行了测试，一切运行良好。成功。
好吧，不。当我连接到另一个数据库，如postgres，我得到了如下：
psql:/home/depesz/.psqlrc:5: ERROR: schema "dba" does not existLINE 2: dba.smi('aws_tag_project') as project,^15:41:22 , cluster , db: depesz-rw@postgres, pid:23229=>
嗯，这可不好。我试着在开始时加入 \set QUIET，在结束时加入 \unset QUIET 来隐藏它，但这并没有帮助。
幸运的是，psql有条件语句，所以我可以首先检查这个函数是否存在。在工作中我还发现，smi返回的是pgroles的旧名称，所以我必须把它映射到新的名称。小事一桩。新版本：
\set QUIETselect EXISTS(SELECT 1from pg_proc pjoin pg_namespace n on p.pronamespace = n.oidwhere p.proname = 'smi' and n.nspname = 'dba') \gset smi_\if :smi_existsselectdba.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
在连接到应用程序数据库的数据库服务器上进行了测试，一切运行良好。成功。
16:04:14 db: depesz-rw@postgres, pid:5144=>
一切都很好，我准备推送到生产中。只是理智的检查，我将像其他用户一样，通过pgbouncer连接，这只是形式上的。对吗？不完全是。
16:05:40 magic production, cluster 1, report db: depesz-rw@appdb, pid:830285059=>
嗯，看起来还行，但pid肯定不对。显然，当通过pgBouncer连接时，psql显示为pid（%p）的东西并不是真正的pid。
幸运的是，这很容易解决。可以使用如下查询获得pid。
SELECT pg_backend_pid() \gset然后，代替%p，使用%:pg_backend_pid:。很简单，也很有效。
但是我突然想到：如果我连接到特殊的、神奇的、pgbouncer数据库，会发生什么？用户不会这样做，但我可能会。如果你不知道，当你通过 pgbouncer 连接时，有一个名为 pgbouncer 的特殊 "数据库"，你可以在那里连接，并显示来自 pgbouncer 本身的一些信息。基本上是 pgbouncer 的管理界面。
当然会成功，对吗？嗯，不。
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 1from pg_proc pjoin pg_namespace n on p.pronamespace = n.oidwhere 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
显然，这个神奇的pgbouncer数据库并不处理查询。根本没有。
=# select 1;ERROR: invalid command 'select 1;', use SHOW HELP;
嗯，好吧。但这很简单。我只需检查数据库名称是否为 pgbouncer，如果是，则使用更简单的提示。如果不是，就用普通代码。
psql甚至还提供了DBNAME变量，所以应该很简单。对吗？
=# \if :DBNAME = pgbouncerunrecognized value "pgbouncer = pgbouncer" for "\if expression": Boolean expected
显然，psql的if表达式只能解析单个值，而且它必须是：true, false, 1, 0, on, off, yes, no。
那么，如果dbname是pgbouncer，如何获得其中的一个，比如1/0？我不能使用选择。我不能在if/.... cue绝望中使用逻辑。
幸运的是，在文档中我发现了这一点。
The expression argument of an \if or \elif command is subject to variable interpolation and backquote expansion
\if 或 \elif 命令的表达式参数会受到变量插值和反引号扩展的影响。因此我可以使用shell！解决办法很明了
\if `test :DBNAME = pgbouncer && echo 1 || echo 0`这需要我们安装测试程序，但它是默认的，所以我并不关心。
所以，所有事情都说了，最后的psqlrc是。
-- 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 PIDSELECT pg_backend_pid() \gset-- Check if there is dba.smi() functionselect 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.selectdba.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:
它做了我想做的事。把它安装成/etc/postgresql-common/psqlrc，到处都是，而且很有效。
当我把它展示给同事时，他说。我不知道psqlrc文件可以这么复杂。这就促使我写了这篇博文。它们（psqlrcs）可以。它们可以做很多事情，如果写它们似乎并不简单--那是因为我们很少做。但是，我认为，这是很值得的。
小结
psqlrc 文件用来定制 psql 客户端特性的配置文件，比如上面的样例，改吧改吧，并且支持shell，
1.%M指的是数据库服务器的主机名--如果连接是通过Unix域套接字，则为"[local]"。2.%>指的是监听端口3.%n指的是会话用户名4.%/是指当前的数据库5.%R指的是你是在单用户模式(^) 还是断开连接(!)，但通常是=。6.%#指的是你是超级用户（#）还是普通用户（>）。7.%x指的是交易状态--通常是空白的，除非是在一个事务块中(*)
还支持颜色
•32 for green•33 for yellow•34 for blue•35 for magenta•36 for cyan•37 for white
输出效果如下
[postgres@xiongcc ~]$ psql -U myuser -d postgrespsql (13.2)Type "help" for help.22:42:36 db: myuser@postgres, pid:2186 , is_slave:f=>\q[postgres@xiongcc ~]$ psqlpsql (13.2)Type "help" for help.22:43:23 db: postgres@postgres, pid:2193 , is_slave:f=#\q[postgres@xiongcc ~]$ psql -U myuser -d postgrespsql (13.2)Type "help" for help.22:43:25 db: myuser@postgres, pid:2197 , is_slave:f=>\q[postgres@xiongcc ~]$ psql -p 5433psql (13.2)Type "help" for help.22:43:37 db: postgres@postgres, pid:2203 , is_slave:t=#\q
会打印出：
1.数据库的角色，是主库还是备库2.哪个用户登录的3.登录的什么数据
比如这个psqlrc样例，还可以当定制化脚本使用：
[postgres@xiongcc ~]$ psqlCurrent Host Server Date Time : Tue Oct 26 22:49:03 CST 2021Administrative queries::settings -- Server Settings:conninfo -- Server connections:activity -- Server activity:locks -- Lock info:waits -- Waiting queires:dbsize -- Database Size:tablesize -- Tables Size:uselesscol -- Useless columns:uptime -- Server uptime:menu -- Help Menu\h -- Help with SQL commands\? -- Help with psql commandsDevelopment queries::sp -- Current Search Path:clear -- Clear screen:ll -- Listpsql (13.2)Type "help" for help.[local]:5432 postgres@postgres # :conninfo+----------+-------+| usename | count |+----------+-------+| NULL | 4 || postgres | 3 |+----------+-------+(2 rows)Time: 5.111 ms[local]:5432 postgres@postgres # :locks+-------------+--------------+--------------+---------------+-------------------+| blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement |+-------------+--------------+--------------+---------------+-------------------++-------------+--------------+--------------+---------------+-------------------+(0 rows)Time: 5.576 ms
你也可以指定null显示什么样
bands=# INSERT INTO members (name) VALUES ('');INSERT 0 1bands=# INSERT INTO members (name) VALUES (NULL);INSERT 0 1bands=# SELECT * FROM members;id | name----+---------------1 | David Pajo2 | Brian McMahan3 |4 |(4 rows)bands=# \pset null '(null)'Null display is "(null)".bands=# SELECT * FROM members;id | name----+---------------1 | David Pajo2 | Brian McMahan3 |4 | (null)(4 rows)
也可以设置ON_ERROR_ROLLBACK，让事务出错了继续运行（本质是savepoint实现的），而不是提示 ERROR: current transaction is aborted, commands ignored until end of transaction block
bands=# BEGIN;BEGINbands=# CREATE TABLE members (id SERIAL, name TEXT);CREATE TABLEbands=# INSERT INTO member (name) VALUES ('David Pajo');ERROR: relation "member" does not existLINE 1: INSERT INTO member (name) VALUES ('David Pajo');bands=# INSERT INTO members (name) VALUES ('David Pajo');INSERT 0 1bands=# INSERT INTO members (name) VALUES ('Brian McMahan');INSERT 0 1bands=# COMMIT;COMMIT
所以以上种种，你都可以添加到psqlrc里面，唯我所控。
原文译自：https://www.depesz.com/2021/06/23/a-tale-of-making-company-wide-standard-psqlrc/
参考
https://www.digitalocean.com/community/tutorials/how-to-customize-the-postgresql-prompt-with-psqlrc-on-ubuntu-14-04
https://opensourcedbms.com/dbms/psqlrc-psql-startup-file-for-postgres/
https://postgres.fun/20140221115413.html
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
