环境描述:
操作系统: 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) 的定义部分
也可以参考 postgresql(12.4) 的源码 src/bin/psql/help.c 文件中 usage 的函数部分
或者执行 psql --help
[postgres@pg1 ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
sql命令与元命令的区别在官方文档 (Part VI.Reference => PostgreSQL Client Applications =>psql) 中的 Entering SQL Commands 与 Meta-Commands 进行描述
1.\x 设置查询结果输出
设置前:
postgres=# select * from test;
id
-----
123
(1 row)
设置后:
postgres=# \x
Expanded display is on.
postgres=# select * from test;
-[ RECORD 1 ]
id | 123
2.\timing 显示执行时间
设置前:
postgres=# select * from test;
id
-----
123
(1 row)
设置后:
postgres=# \timing on
Timing is on.
postgres=# select * from test;
id
-----
123
(1 row)
Time: 0.221 ms
3.\watch反复执行当前SQL
postgres=# select * from test;
id
-----
123
(1 row)
postgres=# \watch
Thu 21 Jan 2021 05:01:44 AM EST (every 2s)
id
-----
123
(1 row)
Thu 21 Jan 2021 05:01:46 AM EST (every 2s)
id
-----
123
(1 row)
4.-E 获取元命令对应的SQL语句
使用前:
[postgres@pg1 ~]$ psql
psql (12.4)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
public | test | table | SYSTEM
(1 row)
使用后:
[postgres@pg1 ~]$ psql -E
psql (12.4)
Type "help" for help.
postgres=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
public | test | table | SYSTEM
(1 row)
5. -c psql执行SQL脚本
效果:
[postgres@pg1 ~]$ psql -c "select * from test"
id
-----
123
(1 row)
6.-A 设置非对其模式输出
设置前:
[postgres@pg1 ~]$ psql -c "select * from test"
id
-----
123
(1 row)
设置后:
[postgres@pg1 ~]$ psql -A -c "select * from test"
id
123
(1 row)
7.-t 只显示记录数据
设置前:
[postgres@pg1 ~]$ psql -c "select id from test";
id
-----
123
(1 row)
设置后:
[postgres@pg1 ~]$ psql -t -c "select id from test";
123
备注: psql -tA -c "select id from test"; -tA的写法对后期编写shell脚本进行维护帮助极大,以及对监控工具(比如zabbix,请关注后期更新)极其友好.
8. \set 元命令传递变量
postgres=# \set v_id 123
postgres=# select * from test where id = :v_id;
id
-----
123
(1 row)
使用psql 定制日常维护脚本
编辑 .psqlrc 文件或者 pg_config --sysconfdir 目录下的 psqlrc 文件 (postgresql源码文件 src/bin/psql/psqlrc.sample 文件内容)
\set active_session 'select pid,usename,datname,query,client_addr from pg_stat_activity where pid <> pg_backend_pid() and state = \'active\' order by query';
\set wait_event 'select pid,usename,datname,query_start,client_addr,wait_event_type,wait_event from pg_stat_activity where pid <> pg_backend_pid() and wait_event is not null order by wait_event_type';
\set connections 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc';
1.查询活动会话
postgres=# :active_session
pid | usename | datname | query | client_addr
-----+---------+---------+-------+-------------
(0 rows)
2.查询等待事件
postgres=# :wait_event
pid | usename | datname | query_start | client_addr | wait_event_type | wait_event
------+---------+---------+-------------+-------------+-----------------+---------------------
9727 | SYSTEM | | | | Activity | LogicalLauncherMain
9724 | | | | | Activity | AutoVacuumMain
9722 | | | | | Activity | BgWriterHibernate
9721 | | | | | Activity | CheckpointerMain
9723 | | | | | Activity | WalWriterMain
(5 rows)
3.查看数据库连接数
postgres=# :connections
datname | usename | client_addr | count
---------+---------+-------------+-------
| SYSTEM | | 1
| | | 4
(2 rows)
