PostgreSQL实用技巧：日志功能、实用命令、性能提升

日志功能
记录慢查询语句
开启慢查询记录功能，以及执行时间等于或大于设置值的语句log_min_duration_statement 将被记录：

ALTER database postgres SET log_min_duration_statement = '250 ms';
设置日志记录类型
使用 log_statement 选项设置日志记录语句类型。 'all';
有效值为all、ddl、none和mod。

记录锁定等待事件
记录数据库中发生锁定等待事件的时间：

ALTER DATABASE postgres SET log_lock_waits = 'on';
❙ ❙ ❙ 执行time❙ 语句
设置语句执行超时可以防止语句运行时间过长。可以针对每个数据库、每个用户或每个会话设置过期时间。建议设置全局超时，然后根据需要为特定用户或会话设置特殊超时。

ALTER DATABASE mydatabase SET statements_timeout = '60s';
查找消耗资源最多的请求和进程
pg_stat_statements 插件帮助我们发现消耗资源最多的请求和进程。 † ORDER BYmean_exec_time DESC
LIMIT 10 ;

跟踪数据库连接
以下查询可以根据状态返回数据库连接数：

SELECT count(*),
status
来自 pg_stat_activityY YY 状态；
如果空闲连接数超过20，建议使用PgBouncer等连接池。

查看表大小
使用以下语句查看表的文件大小：

SELECT pg_relation_size('table_name');

--更好的显示格式
SELECT pg_size_pretty( pg_relation_size(' table_name)'));
显示所有表的大小
以下查询返回数据库中所有表的大小，从最大到最小排序：

SELECT relname AS 关系,
pg_size_pretty (
pg_total_relation_size (C .oid)
) AS 总大小
FROM pg_class C
左连接 pg_namespace WERE = pg_namespace N Cn。 NOT IN (
'pg_catalog',
'information_scheme'
)
AND C .relkind 'i'
'i'
AND Brelationsname !~ '^pg_ORD_toast' Y C .oid) DESC

检查未使用的索引
使用以下查询返回未使用的索引，按索引大小降序排序。

选择方案名称|| '.' || relname AS 表,
indexrelname AS 索引,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS “索引大小”,
idx_scan 作为“索引检查”
pg_index i IS ui.indexrelid = i.indexrelid
其中唯一
且 idx_scan AND pg_relation_size(relid) > 5 * 8192
ORDER BY
pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULL 优先，in_relation pg_DESIZE; 评估表
中的数据大小根据内部统计返回表中的近似数据量，可用于替换大型表的 SELECT count(*) 查询。

SELECT reltuples::numeric as count
FROM pg_class
WHERE relname='table_name';

创建非阻塞索引
使用 ALL 表选项以避免在索引时锁定获取器，该选项不能在事务中使用。

CREATE INDEX COCURRENTLY foobar ON foo (bar);
PSQL 命令
自动记录请求所花费的时间
返回每个请求所花费的时间，包括网络传输时间。

\timin g
您可以使用 .psqlrc 文件应用默认配置。

自动格式化查询结果
提高查询结果的可读性：

\x auto
调用文本编辑器
变量$EDITOR用于设置默认文本编辑器，输入以下内容。命令可用于打开和编辑最近执行的查询语句。

\e
保存并关闭编辑器后，数据库将执行保存的查询语句。

定义空值的显示内容
将空值显示为其他字符，以便更容易区分空值和空字符串。

\pset null ⛔
保存查询历史记录
指定本地文件保存指定数据库查询历史记录：

\set HISTFILE ~/.psql_history-:DBNAME
用于显示 Statement 的内部命令
psql 命令行选项 -E (–echo-hidden) 可以显示内部命令生成的查询语句（例如 \dt mytable）：

psql -E
仅返回结果数据
psql 命令行上的 -qtA 选项表示以静默模式运行查询，以未对齐格式返回结果，并且仅返回数据。该选项与 -c 选项结合使用，可用于检索脚本文件中的查询结果。

psql -qtA
以 HTML 表的形式返回结果
psql 命令行上的 -qtH 选项表示以静默模式运行查询，以 HTML 表的形式返回结果，并且仅返回数据。

psql -qtH
清屏
以下命令可以清屏：

\!清除
连续运行查询语句
下一条命令可以每2 秒自动运行一次。运行最后一条查询语句并显示结果：

\watch
回滚到上一条语句
在交互模式下，设置以下命令，当执行错误时自动回滚到上一条语句：

\set ON_ERROR_ROLLBACK Interactive
直接导出为 CSV
连接数据库时，指定 --csv 选项和查询语句来运行查询，并以 CSV 格式输出查询结果。

psql --csv -c 'select from test *;'
运行脚本文件
以下命令可以在 psql 中运行脚本文件：

\i filename
指定清晰边框
以下命令可以设置 psql 查询结果的显示边框：

\pset border 2
设置线型为 unicode
当将线型设置为 unicode 时，结合以上设置。获得更清晰的显示格式：

\pset linestyle unicode
SQL函数
替换空值
合并函数可以将空值转换为指定字符串： id ,
coalesce(ip, 'No IP' ')
来自日志；
1
2
3
生成串行数据
功能可用于生成串行数据指定的时间间隔，包括数字和时间戳。

选择*来自
Gener_series(now() - '3months'::interval, now(), '1day');

截断日期
date_trunc函数可以将日期数据截断到指定的精度，例如月、周、天、小时、分钟。

SELECT date_trunc('day', now());
时间数据的加减法
时间戳数据可以直接对时间间隔进行加减：

SELECT now() - '1 Month' ::interval;
1
停止会话进程
pg_sleep 函数可以在指定时间内停止当前会话进程：

select pg_sleep(2.5);
命令
创建只读用户
系统角色 pg_read_all_data 可用于读取所有数据表：

GRANT pg_read_all_data TO username;
终止指定用户的后台进程 请求终止该用户的所有后台” test" 进程：

WITH pids AS (
SELECT pid
FROM pg_stat_activity
WHERE username='test'♻gter_SELECT'
)
FROM pids;

撤消 SQL指定用户
执行的语句 以下查询可以撤消用户“test”执行的所有 SQL 语句并解除事务绑定：

WITH pids AS (
SELECT pid
FROM pg_stat_activity
WHERE username='测试'
）
从pg_stat_activity中选择pg_cancel_backend（pid）
。