环境描述:
操作系统: CentOS Linux release 7.6.1810 (Core)
postgresql版本 postgres (PostgreSQL) 12.4
host1 192.168.112.200
host2 192.168.112.201
首先翻看 postgresql(12.4) 官方文档 (Part VI.Reference => PostgreSQL Client Applications =>psql) 的 Prompting 部分描述
也可以参考 postgresql(12.4) 的源码 src/bin/psql/prompt.c 文件中 get_prompt 的注释部分
Prompting
The prompts psql issues can be customized to your preference. The three variables PROMPT1,PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new command. Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:
%M
The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location.
%m
The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket.
%>
The port number at which the database server is listening.
%n
The database session user name. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)
%/
The name of the current database.
%~
Like %/, but the output is ~ (tilde) if the database is your default database.
%#
If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)
%p
The process ID of the backend currently connected to.
%R
In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished * ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string,or ( if there is an unmatched left parenthesis. In prompt 3 %R doesn't produce anything.
%x
Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).
%l
The line number inside the current statement, starting from 1.%digits The character with the indicated octal code is substituted.
%:name:
The value of the psql variable name. See the section Variables for details.
%`command`
The output of command, similar to ordinary “back-tick” substitution.
%[ ... %]
Prompts can contain terminal control characters which, for example, change the color,background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur
within the prompt. For example:
testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, colorcapable terminals.
To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
首先PROMPT1\PROMPT2\PROMPT3 的区别:
Prompt 1 is the normal prompt that is issued when psql requests a new command
Prompt 2 is issued when more input is expected during command entry
Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.
PROMPT 的默认值:
The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
我们通过实验进行一下测试.
Prompt 1:
[postgres@pg1 ~]$ psql -h 192.168.112.201
psql (12.4)
Type "help" for help.
postgres=# \set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x ' <= 配置前
192.168.112.201:5432 postgres@postgres=# <= 配置后
Prompt 2:
配置前:
postgres=# select *
postgres-# from
postgres-# test
postgres-# ;
配置后:
postgres=# \set PROMPT2 '%M %n@%/%R %# '
postgres=# select *
192.168.112.201 postgres@postgres- # from
192.168.112.201 postgres@postgres- # test
192.168.112.201 postgres@postgres- # ;
Prompt 3:
配置前:
postgres=# copy test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1
>> 2
>> 3
>> \.
COPY 3
配置后:
postgres=# \set PROMPT3 '%M %n@%/%R %# '
postgres=# copy test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
192.168.112.200 postgres@postgres # 1
192.168.112.200 postgres@postgres # 2
192.168.112.200 postgres@postgres # 3
192.168.112.200 postgres@postgres # \.
COPY 3
带日期等个性化配置:
[postgres@pg1 ~]$ psql -h 192.168.112.201
psql (12.4)
Type "help" for help.
postgres=# \set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x '
21:13:55 (postgres@192.168.112.201:5432)postgres=#
配置固化到配置文件:
1.在/home/postgres 下创建 .psqlrc 文件.
\set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x '
\set PROMPT2 '%M %n@%/%R %# '
\set PROMPT3 '%M %n@%/%R %# '
2.根据postgresql源码文件 src/bin/psql/psqlrc.sample 文件内容
--
-- system-wide psql configuration file
--
-- This file is read before the .psqlrc file in the user's home directory.
--
-- Copy this to your installation's sysconf directory and rename it psqlrc.
-- The sysconf directory can be identified via "pg_config --sysconfdir".
[postgres@pg1 ~]$ pg_config --sysconfdir
/pgsoft/etc/postgresql
可见 /pgsoft/etc/postgresql 目录下的 psqlrc 文件是优先于 .psqlrc 进行读取的.
