0


PostgreSQL实用技巧

日志功能

记录慢查询语句

开启慢查询记录功能,执行时间等于或者大于 log_min_duration_statement 设置值的语句会被记录:

ALTERdatabase postgres SET log_min_duration_statement ='250ms';

设置日志记录的语句类型

使用 log_statement 选项设置日志记录的语句类型:

ALTERDATABASE postgres SET log_statement ='all';

有效的取值包括 all、ddl、none 以及 mod。

记录锁等待事件

当数据库出现锁等待事件时记录日志:

ALTERDATABASE postgres SET log_lock_waits ='on';

性能技巧

设置语句的执行超时时间

设置语句的执行超时可以阻止超长运行的语句。超时时间可以基于数据库、用户或者会话进行设置。推荐设置一个全局的超时时间,然后根据需要为具体用户或者会话设置特殊的超时时间。

ALTERDATABASE mydatabase SET statement_timeout ='60s';

查找占用资源最多的查询和进程

pg_stat_statements 插件可以帮助我们发现消耗资源最多的查询和进程。

# create extension pg_stat_statements;SELECT
    total_exec_time,
    mean_exec_time as avg_ms,
    calls,
    query
FROM pg_stat_statements
ORDERBY mean_exec_time DESCLIMIT10;

监控数据库连接

以下查询可以按照状态返回数据库连接的数量:

SELECTcount(*),
       state
FROM pg_stat_activity
GROUPBY state;

如果空闲(idle)连接数量超过了 20,推荐使用连接池,例如 PgBouncer。

查看表的大小

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

SELECT pg_relation_size('table_name');-- 更好的显示格式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
LEFTJOIN pg_namespace N ON(N.oid = C .relnamespace)WHERE nspname NOTIN('pg_catalog','information_schema')AND C .relkind <>'i'AND nspname !~'^pg_toast'ORDERBY pg_total_relation_size (C .oid)DESC

检查未使用的索引

使用以下查询返回未被使用的索引,按照索引大小降序排列。

SELECT schemaname ||'.'|| relname AStable,
       indexrelname ASindex,
       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
WHERENOT indisunique
  AND idx_scan <50AND pg_relation_size(relid)>5*8192ORDERBY 
  pg_relation_size(i.indexrelid)/nullif(idx_scan,0)DESC NULLS FIRST,
  pg_relation_size(i.indexrelid)DESC;

评估表中的数据量

基于内部统计信息返回表中的近似数据量,可以用于替代大表的 SELECT count(*) 查询。

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

非阻塞式的索引创建

创建索引时使用 CONCURRENTLY 选项可以避免获取表上的锁,该选项不能在事务中使用。

CREATEINDEX CONCURRENTLY foobar ON foo (bar);

PSQL 命令

自动记录查询占用的时间

返回每次查询占用的时间,包括网络传输时间。

\timing

可以利用 .psqlrc 文件实现默认配置。

自动格式化查询结果

提高查询结果的可读性:

\x auto

调用文本编辑器

变量 $EDITOR 用于设置一个默认的文本编辑器,输入以下命令可以打开并编辑最后一次执行的查询语句。

\e

保存并关闭编辑器之后,数据库会执行保存的查询语句。

设置空值的显示内容

将空值 null 显示为其它字符,方便区分空值和空字符串。

\pset null ⛔

保存查询历史

为指定数据库设置一个保存查询历史的本地文件:

\set HISTFILE ~/.psql_history-:DBNAME

显示内部命令对应的查询语句

psql 命令行选项 -E(–echo-hidden)可以显示内部命令(例如 \dt mytable)生成的查询语句:

psql -E

只返回结果数据

psql 命令行的 -qtA 选项表示以静默模式运行查询,以非对齐的格式返回结果,并且只返回数据。该选项和 -c 选项结合使用,可以用于脚本文件获取查询结果。

psql -qtA

以 HTML 表格形式返回结果

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

psql -qtH

清理屏幕

以下命令可以实现清屏:

\! clear

连续运行一个查询语句

以下命令可以每间隔 2 秒自动执行一次最后的查询语句并显示结果:

\watch

回退到上一个语句

在交互模式下,设置以下命令可以在执行出错时自动回退到上一个语句执行之前:

\set ON_ERROR_ROLLBACK interactive

直接导出 CSV 格式

连接数据库时,指定 --csv 选项和查询语句可以运行查询并以 CSV 格式输出查询结果。

psql <connection-string>--csv -c 'select * from test;'

运行脚本文件

以下命令可以在 psql 中执行一个脚本文件:

\i filename

设置清晰的边界

以下命令可以在 psql 中为查询结果设置一个显示边界:

\pset border 2

将 linestyle 设置为 unicode

将 linestyle 设置为 unicode 时,结合上面的配置可以获得更清晰的显示格式:

\pset linestyle unicode

SQL 功能

替换空值

coalesce 函数可以将空值转换为指定的字符串:

SELECT id,coalesce(ip,'no IP')FROM logs;

生成序列数据

generate_series 函数可以生成一个指定间隔的数据序列,包括数字和时间戳。

SELECT*FROM
generate_series(now()-'3 month'::interval,now(),'1 day');

截断日期

date_trunc 函数可以将日期数据截断为指定的精度,例如月份、星期、日、小时、分钟。

SELECT date_trunc('day',now());

时间数据的加减运算

时间戳数据可以直接加减一个时间间隔:

SELECTnow()-'1 month'::interval;

暂停会话进程

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 usename='test')SELECT pg_terminate_backend(pid)FROM pids;

取消指定用户正在运行的 SQL 语句

以下查询可以取消用户“test”正在运行的所有 SQL 查询,回滚未提交的事务:

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

本文转载自: https://blog.csdn.net/horses/article/details/128259591
版权归原作者 不剪发的Tony老师 所有, 如有侵权,请联系我们删除。

“PostgreSQL实用技巧”的评论:

还没有评论