前言
记录一下服务器问题排查常用的一些命令。
常用篇
Linux
1 | 只列出含 XXX 的文件 |
正则表达式
常用正则：i Hate Regex
1 | // 匹配 hello 之前的字符 |
工具
- crontab：设置定时任务工具；
- Socat：网络工具（透明代理，端口转发，文件传输等），新版瑞士军刀：socat
服务器之间文件传输
参考资料：Linux下的SCP指令详解
1 | 本地主机传输文件到远程主机 |
PostgreSQL
编译安装
参考自：【CentOS7】PostgreSQL-10.3的安装
安装编译工具：
1
yum install -y vim lrzsz tree wget gcc gcc-c++ readline-devel zlib-devel
进入/usr/local/目录下：
cd /usr/local下载 tar 包：
curl -O https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz解压：
tar -xzvf postgresql-16.2.tar.gz编译安装：
1
2
3
4
5cd /usr/local/postgresql-16.2
./configure --prefix=/usr/local/pgsql-16.2 # /usr/local/pgsql-16.2 为安装目录
make && make install
Two thousand years later，出现「PostgreSQL installation complete.」代表安装成功配置系统环境变量：
vi /etc/profile1
2
3
4...
# /etc/profile 文件末尾添加
export PGHOME=/usr/local/pgsql-16.2
export PATH=$PGHOME/bin:$PATH使配置文件立即生效：
source /etc/profile创建数据库用户：
useradd -m -d /home/postgres postgres切换到数据库用户：
su postgres初始化数据库：
pg_ctl init -D /home/postgres/db_data启动数据库：
pg_ctl start -D /home/postgres/db_data
自启动设置
复制 PostgreSQL 自启动文件：cp /usr/local/postgresql-16.2/contrib/start-scripts/linux /etc/init.d/postgresql
修改自启动文件：vi /etc/init.d/postgresql，
1 |
|
接下来有两种方式：
一种是直接执行：cd /etc/rc.d/init.d/ && chkconfig --add postgresql；
一种是修改 /etc/rc.d/rc.local 文件：vi /etc/rc.d/rc.local，
1 |
|
添加可执行权限：chmod a+x /etc/rc.d/rc.local，最后查看一下 rc.local 服务是否启动：
1 | systemctl status rc-local.serives |
若要在容器中设置自启动，在没给容器提权的情况下，则需要第三种方式：将 /etc/rc.d/init.d/postgresql 放进 /root/.bashrc 中启动，vi /root/.bashrc，
1 | ... |
原理是：docker 容器在启动时，会自动执行 ~/.bashrc 文件，加载环境变量，当有其他命令在该文件时，也会一起执行。
当然，容器中自启动更普遍的方式应该是在镜像/容器中通过 CMD 或者 ENTRYPOINT 直接指定 shell 脚本启动执行。
配置文件设置
1 | // 设置空闲长事务超时时间 |
psql
1 | nohup psql postgresql://user:password@host:port/dbname -f update.sql > update.sql 2>&1 & # 刷库命令，update.sql 文件以 begin; 开始，commit; 结束 |
sql
查看锁等待状态
pg中关于AccessShareLock和ExclusiveLock的问题：
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
create function f_lock_level(i_mode text) returns int as
$$
declare
begin
case i_mode
when 'INVALID' then return 0;
when 'AccessShareLock' then return 1;
when 'RowShareLock' then return 2;
when 'RowExclusiveLock' then return 3;
when 'ShareUpdateExclusiveLock' then return 4;
when 'ShareLock' then return 5;
when 'ShareRowExclusiveLock' then return 6;
when 'ExclusiveLock' then return 7;
when 'AccessExclusiveLock' then return 8;
else return 0;
end case;
end;
$$
language plpgsql strict;
-- 2. 修改查询语句，按锁级别排序：
with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.transactionid is not distinct from w.transactionid and
r.pid <> w.pid
order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;现在可以排在前面的就是锁级别高的等待，优先干掉这个。
-[ RECORD 1 ]-+----------------------------------------------------------
locktype | relation -- 冲突类型
r_mode | ShareUpdateExclusiveLock -- 持锁模式
r_user | postgres -- 持锁用户
r_db | postgres -- 持锁数据库
relation | tbl -- 持锁对象
r_pid | 25656 -- 持锁进程
r_xact_start | 2015-05-10 14:11:16.08318+08 -- 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08 -- 持锁SQL开始时间
r_locktime | 00:01:49.460779 -- 持锁时长
r_query | vacuum freeze tbl; -- 持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode | AccessExclusiveLock -- 等待锁模式
w_pid | 26731 -- 等待锁进程
w_xact_start | 2015-05-10 14:11:17.987362+08 -- 等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08 -- 等待锁SQL开始时间
w_locktime | 00:01:47.556597 -- 等待锁时长
w_query | truncate tbl; -- 等待锁SQL
-[ RECORD 2 ]-+----------------------------------------------------------
locktype | relation
r_mode | ShareUpdateExclusiveLock
r_user | postgres
r_db | postgres
relation | tbl
r_pid | 25656
r_xact_start | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime | 00:01:49.460779
r_query | vacuum freeze tbl;
w_mode | RowExclusiveLock
w_pid | 25582
w_xact_start | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime | 00:01:42.698959
w_query | insert into tbl(crt_time) select now() from generate_series(1,1000); -- 这个SQL其实等待的是truncate tbl的锁;
......
统计数据库表以及索引存储空间
1 | -- 按从大到小排序输出数据库每个索引大小 |
常用sql语句
1 | -- 查找超过1小时的长事务 |
子查询优化
PG 的子查询实际有两种，分为子连接（Sublink）和子查询（SubQuery），按子句的位置不同，出现在 from 关键字后的是子查询，出现在 where/on 等约束条件中或投影中的子句是子连接。
子查询：select a.* from table_a a, （select a_id from table_b where id=1) b where b.a_id = a.id;
子连接：select * from table_a where id in（select a_id from table_b where id=1);
在简单的子连接查询下，PG 数据库查询优化器一般会将其转化为内连接的方式：select a.* from table_a a, table_b b where a.id=b.a_id and b.id=1;，正常索引没问题情况下这两种方式都能得一样的结果，最终执行的都是索引内连接结果。但在某些情况下，PG 查询优化器在子连接的 SQL 下，子连接的查询会走索引，而主查询会顺序扫描（Seq Scan），原因是当 table_a 的数据量很大时，索引值又有很多重复的，同时查询优化器也不知道子连接返回的具体数据，这时查询优化器可能会认为顺序扫描更快，从而不走索引，导致耗时增加，所以为减少查询优化器的不确定性，最好是直接使用内连接的方式代替 in 语句。 当然，对于特别复杂的查询业务，还是开启事务，分多次查询，在代码层做一些业务逻辑处理更合适，别让数据库把事情全做了，这也能减轻数据库的压力。 PG 查询计划执行路径可以看看： PostgreSQL 查询语句优化，postgresql通过索引优化查询速度操作
tricks
- 由于 pg 无法强制使用索引，所以只能通过一些其他方法来引导查询优化器使用索引，比如调整查询条件；
- 获取分组中最大值对应的一行数据；
权限配置，PostgreSQL权限管理详解：
1 | -- 创建只读组 |
主从&备份
参考资料：postgresql流式复制(Streaming Replication)、【PostgreSQL】PostgreSQL复制的监控、【PostgreSQL】导出数据库表(或序列)的结构和数据、pg_ctl、pg_basebackup
1 | -- 创建流复制备份用户（主从） |
相关命令：
1 | # 冷备数据库(结合刷库命令可恢复数据库)，加 -s 参数只导出数据库表结构 |
设置主库：postgresql.conf
1 | wal_level = hot_standby |
设置主库：pg_hba.conf
1 | # Allow replication connections from localhost, by a user with the |
设置从库 recovery.conf（自 Postgresql 12 起，recovery.conf 并入 postgresql.conf）：
1 | standby_mode = 'on' |
并发 dump&restore 数据库
导出数据库全部表结构
1
pg_dump -d postgresql://user:pswd@host:port/db_name --schema-only -f db_name_schema.sql
导出外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35psql -d postgresql://owner_user:pswd@host:port/db_name -t -A -F"," -c "
SELECT DISTINCT
'ALTER TABLE ' || quote_ident(nsp.nspname) || '.' || quote_ident(cls.relname) ||
' ADD CONSTRAINT ' || quote_ident(con.conname) ||
' FOREIGN KEY (' || array_to_string(ARRAY(
SELECT quote_ident(att.attname)
FROM pg_attribute att
WHERE att.attnum = ANY(con.conkey)
AND att.attrelid = cls.oid), ', ') ||
') REFERENCES ' || quote_ident(f_nsp.nspname) || '.' || quote_ident(f_cls.relname) ||
' (' || array_to_string(ARRAY(
SELECT quote_ident(att.attname)
FROM pg_attribute att
WHERE att.attnum = ANY(con.confkey)
AND att.attrelid = f_cls.oid), ', ') ||
') ON DELETE ' || CASE con.confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END ||
' ON UPDATE ' || CASE con.confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END || ';'
FROM pg_constraint con
JOIN pg_class cls ON con.conrelid = cls.oid
JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
JOIN pg_class f_cls ON con.confrelid = f_cls.oid
JOIN pg_namespace f_nsp ON f_cls.relnamespace = f_nsp.oid
WHERE con.contype = 'f';" > db_name_fkeys.sql导出数据库全局用户/权限
1
pg_dumpall -d postgresql://superuser:pswd@host:port --globals-only -f db_name_user.sql
4个并行任务导出全部数据
1
pg_dump -d postgresql://user:pswd@host:port/db_name --data-only -F d -j 4 -f ./db_name_data_dir
新建数据库实例
1
pg_ctl init -D ~/new_db_data
导入数据库全局用户/权限
1
psql -U superuser -p port -f db_name_user.sql
新建数据库
1
create database new_db_name owner owner_user
导入数据库全部表结构
1
psql -U superuser -p port -f db_name_schema.sql
移除新库外键约束
1
2
3
4
5
6
7
8
9
10
11
12psql -d postgresql://owner_user:pswd@host:port/db_name <<EOF
DO \$\$
DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT conname, conrelid::regclass
FROM pg_constraint
WHERE contype = 'f') LOOP
EXECUTE 'ALTER TABLE ' || r.conrelid || ' DROP CONSTRAINT ' || r.conname;
END LOOP;
END \$\$;
EOF4个并行任务导入数据
1
pg_restore -d postgresql://owner_user:pswd@host:port/db_name -j 4 ./db_name_data_dir
恢复新库外键约束
1
psql -d postgresql://owner_user:pswd@host:port/db_name -f db_name_fkeys.sql
MVCC/数据碎片/索引膨胀/FREEZE
参考自：PostgreSQL | 空间又告警了，先从整理索引碎片开始，正确的评估postgres index膨胀，PostgreSQL VACUUM 之深入浅出 (一)，深入理解 PostgreSQL 中的 MVCC（多版本并发控制）机制，硬核-深度剖析PostgreSQL数据库“冻结炸弹”原理机制
简单总结一下：
- mvcc 主要通过锁或乐观并发控制机制来解决冲突，通过事务号实现多版本及查询可见性（当前事务只能看到当前事务启动前已提交的数据，即只可能大事务号看到小事务号的数据），当事务号达到设定值时，事务号会发生回卷，此时需要以单用户模式执行 vacuum freeze 操作，将所有事务号置为2，代表冻结事务，对所有事务可见，当然可通过设置参数实现自动 freeze，减少人工介入维护时间；
- 由于 postgres 的 mvcc 机制，更新和删除以及新增的回滚都会造成数据碎片，虽然有 vacuum，但索引的膨胀不可避免，所以当发现索引碎片率超过 30% 时，需要进行重建索引 REINDEX，但常规的 REINDEX 会锁表，在 pg12 之后才有 REINDEX CONCURRENTLY，可在线重建，不会锁表，重建完之后需要执行 ANALYZE 更新一下统计信息使索引立即生效。
空间清理
由于标准的 vacuum 无法释放空间归还给操作系统，只是在数据库内部清理/释放/使用（所以 vacuum 只对于未造成空间膨胀的数据库有效，而且当存在大量更新/删除操作时，vacuum 也不一定能及时控制数据库大小，导致数据库空间一步步变大）。而 VACUUM FULL 或者 CLUSTER 在清理磁盘时会进行锁表（SELECT、INSERT、UPDATE 和 DELETE 等操作都无法正常进行，基本可认为是需要停机维护），对于已经占用大量存储空间的数据库，可以使用 pg_repack 进行在线清理/释放表空间，相比 CLUSTER 或 VACUUM FULL，pg_repack 无需获取排它锁，更轻量。
对于既成事实占用存储空间超大的数据库，缩减空间一个可能的方案是先 dump 数据，同时开始记录原数据库增量的 dml sql（log_statement=mod），新建一个数据库，用 dump sql 文件写入，记录 dump 最新的节点（时间或者啥 id，再将原数据库节点之外的数据迁移到新数据库中（用之前记录的增量 dml sql，需过滤回滚的事务），再用新数据库替换原数据库，如此达到释放空间的目的（该方案同样适用于数据库版本升级）。
常见问题：
当自增主键报
duplicate key value violates unique constraint主键冲突时，一般是因为存在手动分配 id 的数据（复制表或着手动插入分配了 id），自增主键 seqence TABLE_COLUMN_seq 没有更新，新插入一个值自增 id 和数据库已插入的分配 id 冲突，此时需要执行SELECT setval('TABLE_COLUMN_seq', (SELECT max(COLUMN) FROM "TABLE"))更新自增主键;分析 sql 性能时，可在 sql 语句前增加
EXPLAIN关键字，查看执行计划，EXPLAIN 一般不会实际执行 sql，但 sql 中带有子语句时，子语句可能会执行，所以为保险起见，最好是在事务中使用 EXPLAIN；eg:1
2
3begin;
EXPLAIN select * from table1 where id=1;
rollback;若要分析实际执行时间，可以使用 EXPLAIN ANALYZE，该选项会实际执行 SQL，也可以组合参数一起分析执行命令
explain (analyze,verbose,costs,buffers,timing) select * from table1 where id=1;。如果业务数据无法直接使用批量写入数据库，就最好在一个事务中写入（当然也得看数据量），在同一个事务中写入，不仅能利用事务本身的 ACID 特性，而且比单独分次执行 sql 效率更高；
PG 数据库中，如果要使用 order 排序查询时，一般带主键的复合索引比单个字段索引更有效，因为 PG 数据在数据更新后，一般会乱序存储，导致单字段索引在查询时需要访问的页面会更多；
PG 刚创建/删除索引后，不一定会及时生效，需要数据库运行一段时间后才会开始生效，如需要立即生效，可执行
ANALYZE VERBOSE table_name;命令，离线或者低负载的时候可以执行VACUUM VERBOSE ANALYZE table_name，清理表的同时更新统计信息，得到更好的 SQL 执行计划。
后记
后面持续更新。。。