客户端

Postgresql的客户端工具有很多种，DataGrid，pgAdmin,Navicate等等。比起更人性化，返回结果更容易操作，编写sql有高亮有自动提示的这些工具，作为DBA的我，更推荐原生的psql。

psql - 最好的客户端工具

psql采用的是双命令行工具设计（Dual-mode Command-line Interface）。

• 连接数据库：psql -h localhost -p 5432 database_name

• 获得psql的帮助：\?

• 获得语法的帮助：\h STATEMENT

• 在shell中执行命令：psql -c "STATEMENT"

• 通过psql执行sql文件：psql < f.sql

psql用法_转储

psql的轻便和易用往往超乎你的想象，比如你需要将某个节点上的表同步到其他节点上，如果用其他工具，你需要打开同步界面-选择节点信息-然后再在可视化页面里面寻找表格，然后再一直下一步直到同步完成。而上述这些操作，在psql这里只需要一行命令

pg_dump -h SRC -p 5432 -U USR -d SRCDB -t sch.tbl --no-owner --no-privileges \
| psql -h DST -p 5432 -U USR -d DSTDB

psql用法_自定义psqlrc文件

除此之外，你可以创建.psqlrc文件来定义命令行环境和快捷命令。

vim ~.psqlrc
\set activeprocess 'select pid,usename,query from pg_stat_activity where state = ''active'';'

然后你就可以在命令行中调用它了

postgres=# :activeprocess
   pid   | usename  |                                 query                                  
---------+----------+------------------------------------------------------------------------
 2942409 | postgres | select pid,usename,query from pg_stat_activity where state = 'active';

类似的，你可以在.psqlrc中定义输出配置，常用的元信息查询等等。

psql用法_批量执行查询结果

作为DBA，有些操作需要拼接元信息形成新的操作命令，然后再进行操作。通常我们只是复制这些结果，再进行粘贴执行一次操作。在psql中，被\gexec命令简化。把查询结果当成 SQL 执行，做批量 DDL 神器。

-- 批量重建 bloated 索引（只是示例）
SELECT format('REINDEX INDEX  %I.%I;', schemaname, indexrelname) FROM pg_stat_all_indexes WHERE idx_scan=0
\gexec
​

psql用法_监控

我们直到linux中有watch命令进行定义监控，一样的，pg也有这样的watch命令。

SELECT now(), state, wait_event, query FROM pg_stat_activity \gx
\watch 1
# 每秒一次监控进程

psql用法_透视结果

和excel表一样，你可以透视

SELECT usename,client_addr,count(1) as cnt FROM pg_stat_activity group by usename,client_addr;
  usename   | client_addr | cnt 
------------+-------------+-----
            |             |   4
 postgres   |             |   2
 halo_admin | ::1         |   1
(3 rows)
​
postgres=# \crosstabview usename client_addr cnt  
  usename   |   | ::1 
------------+---+-----
            | 4 |    
 postgres   | 2 |    
 halo_admin |   |   1

psql用法_错误放大镜

SELECT 1/0;
ERROR:  division by zero
postgres=# \errverbose
ERROR:  22012: division by zero
LOCATION:  int4div, int.c:704
postgres=# 

显示上一次错误的 DETAIL/HINT/CONTEXT/位置栈。

psql用法_分页返回

\set FETCH_COUNT 10000   -- 每批 1w 行
SELECT * FROM huge_table;

psql用法_快速重定向

\o audit.log
\qecho ==== 开始巡检：:HOST :DBNAME ====
SELECT * FROM pg_settings WHERE name like 'shared_%';
\o

这么多的骚操作，是一般客户端所不具备的，作为DBA，首选当然是这个原生自带的客户端工具啦。

最后附送一份.psqlrc配置

-- ========= 基础体验 =========
\timing on
\x auto
\pset linestyle unicode
\pset border 2
\pset null '[NULL]'
\set VERBOSITY verbose
\set SHOW_CONTEXT always
​
-- 出错即停 & 事务回滚策略（交互会话更安全）
\set ON_ERROR_STOP on
\set ON_ERROR_ROLLBACK interactive
​
-- 提示符：user@host:db [事务状态] 以及多行提示
\set PROMPT1 '%n@%m:%/%x%R '
\set PROMPT2 '… %n@%m:%/%x%R '
​
-- 大结果分批抓取（脚本友好）
\set FETCH_COUNT 10000
​
-- 常用开关（按需临时启用）
-- \pset pager off           -- 关闭分页
-- \a \t                     -- 非对齐 + 仅值
-- \pset format csv          -- 输出为 CSV
-- \o /tmp/out.txt           -- 输出重定向到文件；\o 取消
​
-- ========= 快捷查询（输入 :名称 直接执行）=========
\set who 'SELECT pid,usename,datname,state,wait_event_type||''.''||wait_event AS wait, now()-query_start AS runtime, left(query,120) AS sql FROM pg_stat_activity ORDER BY runtime DESC;'
​
\set locks 'SELECT pg_blocking_pids(pid) AS blockers, pid, usename, relation::regclass AS rel, locktype, mode, granted FROM pg_locks WHERE NOT granted OR pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_locks) ORDER BY granted, pid;'
​
\set tx 'SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database WHERE datname=current_database();'
​
\set size 'SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total, pg_size_pretty(pg_relation_size(relid)) AS main FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;'
​
\set idx 'SELECT relname AS idx, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_all_indexes WHERE schemaname NOT IN (''pg_catalog'',''information_schema'') ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;'
​
\set lag 'SELECT application_name, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS byte_lag, now()-pg_last_xact_replay_timestamp() AS time_lag FROM pg_stat_replication;'
​
\set conf 'SELECT name, setting, unit, source, boot_val, reset_val FROM pg_settings ORDER BY name;'
​
\set tbl 'SELECT n.nspname AS schema, c.relname AS table, pg_size_pretty(pg_total_relation_size(c.oid)) AS total, c.reltuples::bigint AS est_rows FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE c.relkind=''r'' ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 50;'
​
-- ========= 版本感知（示例）=========
\if :SERVER_VERSION_NUM >= 150000
  \set wait 'SELECT pid,wait_event_type,wait_event,query FROM pg_stat_activity WHERE wait_event IS NOT NULL ORDER BY query_start;'
\endif
​
-- ========= 批量 DDL 模板（用法示例）=========
-- SELECT format('REINDEX INDEX CONCURRENTLY %I.%I;', schemaname, indexrelname)
-- FROM pg_stat_all_indexes WHERE idx_scan=0;
-- \gexec
​
-- ========= 交叉表（先跑查询，随后）=========
-- \crosstabview 行列名 值列
​
-- ========= 审计与日志（按需开启）=========
-- \o /tmp/psql_audit.log
-- \qecho ==== 巡检开始 :HOST :DBNAME ====
-- \o
​