如虎添翼：PostgreSQL DBA的顶级脚本技巧

青年数据库学习互助会 2024-11-28

123

点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

正文开始

PostgreSQL，作为一款功能强大且高度可靠的开源关系型数据库管理系统，受到了越来越多开发者的青睐。然而，如何充分利用PostgreSQL的高级功能，使其在高负载和复杂环境中依然表现优异，是许多数据库管理员和开发人员面临的挑战。本文将揭秘一系列PostgreSQL常用脚本，帮助你轻松应对各种数据库管理难题，提升工作效率，确保系统稳定运行。

处理锁定问题

锁定是保证数据一致性的重要机制，但不当的锁定策略可能导致性能瓶颈。以下脚本可以帮助识别和解决锁定问题：

查找可能被锁阻塞的进程：

SELECT pid, datname, usename, application_name, client_addr, client_port,
   to_char(now(), 'YYYY-MM-DD HH24:MI:SS') AS now,
   to_char(now() - xact_start, 'DD HH24:MI:SS MS') AS xact_time,
   to_char(now() - query_start, 'DD HH24:MI:SS MS') AS query_time,
   state,
   to_char(now() - state_change, 'DD HH24:MI:SS MS') AS state_time,
   wait_event, wait_event_type, left(query, 40)
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid()
ORDER BY query_time DESC;

复制

找到持有初始锁的PID，并显示等待事件：

WITH sos AS (
 SELECT array_cat(array_agg(pid),
         array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
 FROM pg_locks
 WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
   a.wait_event_type || ': ' || a.wait_event AS wait_event,
   current_timestamp - a.state_change AS time_in_state,
   current_timestamp - a.xact_start AS time_in_xact,
   l.relation::regclass AS relname,
   l.locktype, l.mode, l.page, l.tuple,
   pg_blocking_pids(l.pid) AS blocking_pids,
   (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] AS last_session,
   coalesce((pg_blocking_pids(l.pid))[1] || '.' || coalesce(CASE WHEN locktype = 'transactionid' THEN 1 ELSE array_length(pg_blocking_pids(l.pid),1) + 1 END, 0), a.pid || '.0') AS lock_depth,
   a.query
FROM pg_stat_activity a
JOIN sos s ON (a.pid = ANY(s.pids))
LEFT OUTER JOIN pg_locks l ON (a.pid = l.pid AND NOT l.granted)
ORDER BY lock_depth;

复制

设置锁超时：

ALTER SYSTEM SET lock_timeout = '10s';

复制

日志记录

良好的日志记录习惯对于性能调优和故障排除至关重要。以下脚本可以帮助配置日志记录：

记录慢查询：

ALTER DATABASE postgres SET log_min_duration_statement = '250ms';

复制

控制哪些语句类型被记录：

ALTER DATABASE postgres SET log_statement = 'all';

复制

当数据库等待锁时记录：

ALTER DATABASE postgres SET log_lock_waits = 'on';

复制

性能优化

通过合理的设置和分析，可以显著提高数据库的性能。以下脚本有助于性能优化：

使用语句超时来控制失控查询：

ALTER DATABASE mydatabase SET statement_timeout = '60s';

复制

使用pg_stat_statements
找出消耗资源最多的查询：

SELECT total_exec_time, mean_exec_time AS avg_ms, calls, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

复制

监视连接数：

SELECT count(*), state
FROM pg_stat_activity
GROUP BY state;

复制

索引管理

合理的索引设计可以加速查询速度，但也需要定期检查和维护。以下脚本用于索引管理：

创建非阻塞索引：

CREATE INDEX CONCURRENTLY idxwuyang ON wuyang (id);

复制

检查未使用的索引：

SELECT schemaname || '.' || relname AS table,
   indexrelname AS index,
   pg_size_pretty(pg_relation_size(i.indexrelid)) AS "index size",
   idx_scan as "index scans"
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
 AND idx_scan < 50
 AND pg_relation_size(relid) > 5 * 8192
ORDER BY 
 pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
 pg_relation_size(i.indexrelid) DESC;

复制

表大小查询

了解表的大小有助于进行存储管理和性能优化。以下脚本用于查询表的大小：

查询特定表的大小：

SELECT pg_size_pretty(pg_relation_size('table_name'));

复制

查询所有表的大小：

SELECT relname AS relation,
   pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 AND C.relkind <> 'i'
 AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC;

复制

psql命令行工具

psql
是PostgreSQL提供的强大命令行工具，支持多种便捷操作。以下是一些实用的psql
命令：

自动记录查询时间：
```
\timing

复制
```
自动格式化查询结果：
```
\x auto

复制
```
在编辑器中编辑查询：
```
\e

复制
```
设置空值的显示字符：
```
\pset null 

复制
```

保存每个数据库的历史记录：

\set HISTFILE ~/.psql_history- :DBNAME

复制

显示内部命令生成的查询：
```
psql -E

复制
```
仅获取数据：
```
psql -qtA

复制
```
以HTML表格形式获取结果：
```
psql -qtH

复制
```
搜索以前的查询：使用Ctrl + R
开始搜索历史记录中的查询。
清除psql屏幕：
```
\! clear

复制
```
持续运行查询：
```
\watch

复制
```
显示非默认配置：
```
\dconfig

复制
```

出错时回滚到上一条语句：

\set ON_ERROR_ROLLBACK interactive

复制

从psql直接导出CSV：

psql  --csv -c 'SELECT * FROM test;'

复制

从文件运行查询：
```
\i filename

复制
```
提供干净的边框：
```
\pset border 2

复制
```

保存常用查询：将常用的查询保存在.psqlrc
中，以便快速访问。

\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > interval ''5 minutes'' ORDER by 2 DESC;'
\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''table hit rate'' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'
\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as "index scans" FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
\set table_sizes 'SELECT relname AS relation, pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (''pg_catalog'', ''information_schema'') AND C.relkind <> ''i'' AND nspname !~ ''^pg_toast'' ORDER BY pg_total_relation_size(C.oid) DESC;'

复制