Linux服务器运维文档
Wiki

前言

　　记录一下服务器问题排查常用的一些命令。

常用篇

Linux

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
# 只列出含 XXX 的文件
ll | grep "XXX"

# 按列显示文件名
ls -1
ls -l | grep ^[^d] | awk '{print $9}'

# 返回进入当前目录之前的目录
cd -

# 在文件中查找带 XXX 的行，并输出到 /tmp/99
fgrep "XXX" a.txt > /tmp/99

# 在当前文件夹中查找带 XXX 的行，并输出到 /tmp/99
fgrep "XXX" -r ./* > /tmp/99

# 显示前5行
head -n 5 a.txt

# 显示倒数第5行
tail -n 5 a.txt

# 显示第5行至末尾
tail -n +5 a.txt

#  提取第二行 [linux系统中sed命令输出指定的行](https://www.cnblogs.com/superbaby11/p/16556602.html)
sed -n '2p' a.txt

# 以;分隔每一行，并提取第一列和第三列
awk -F ';' '{print $1,$3}' a.txt

# 以:分隔每一行，并提取第一列和第三列
awk -F '[:]' '{print $1,$3}' a.txt

# 查看 8080 端口占用
lsof -i:8080
netstat -tnlp | grep :8080

# 查看系统运行状态
top

# 查看一定时间内进程cpu占用情况
pidstat

# 查看运行进程
ps -ef

# 查看postgres数据库连接状态，并按cpu使用率排序
ps -aux | grep postgres | sort -nrk 3,3

# 查看磁盘占用大小
du -sh *

# 查看磁盘剩余空间
df -h

# 查看程序被 killed 的原因
dmesg | egrep -i -B100 'killed process'

# 查看 url 请求时间
curl -o /dev/null -s -w %{time_namelookup}:%{time_connect}:%{time_starttransfer}:%{time_total} [url]

正则表达式

常用正则：i Hate Regex

1
2
3
4
5
6
7
8
9
10
11
// 匹配 hello 之前的字符
(.+(?=hello))

// 匹配其他数字和英文字母但不匹配结尾的 2
([a-zA-Z_0-9]+[^2])

// 提取包含test以及time后的数字
test[a-zA-Z0-9\-\_\=\|\ ]*time=([\d+])

// 提取中括号里的内容
[\[](.*?)[\]]

工具

  • crontab：设置定时任务工具；
  • Socat：网络工具（透明代理，端口转发，文件传输等），新版瑞士军刀：socat

服务器之间文件传输

参考资料：Linux下的SCP指令详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 本地主机传输文件到远程主机
scp [本地文件路径] [用户名]@[远程主机IP地址]:[目标路径]
# eg:
scp file.txt user@example.com:/home/user/

# 远程主机传输文件到本地主机
scp [用户名]@[远程主机IP地址]:[远程文件路径] [本地目标路径]
# eg:
scp user@example.com:/home/user/file.txt /path/to/local/

# 传输本地主机整个目录到远程主机
scp -r [本地目录路径] [用户名]@[远程主机IP地址]:[目标路径]
# eg:
scp -r directory/ user@example.com:/home/user/

# 若远程主机的SSH服务器端口不是默认的22端口，则需要指定端口号
scp -P [端口号] [本地文件路径] [用户名]@[远程主机IP地址]:[目标路径]

PostgreSQL

配置文件设置

1
2
3
4
5
6
7
8
// 设置空闲长事务超时时间
idle_in_transaction_session_timeout
// 如果一个会话等待某个类型的锁的时间超过deadlock_timeout的值，该参数决定是否在数据库日志中记录这个信息。
log_lock_waits
deadlock_timeout

// 事务执行超时时间
statement_timeout

psql

1
2
3
4
5
6
7
8
9
10
nohup psql postgresql://user:password@host:port/dbname -f update.sql > update.sql 2>&1 &  # 刷库命令，update.sql 文件以 begin; 开始，commit; 结束
\q # 退出数据库
\c exampledb # 切换数据库
\l+ # 查看全部数据库
\du+ # 查看全部用户
\d+ # 查看全部表
\dt+ [table_name] # 查看表大小
\dn+ # 查看全部schema
\dp [table_name] # 查看表的权限详情
\x # 竖式显示记录

sql

查看锁等待状态

pg中关于AccessShareLock和ExclusiveLock的问题：

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 1. 先用一个函数来将锁转换为数字，
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 按从大到小排序输出数据库每个索引大小
select indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) as size from pg_stat_user_indexes where schemaname='public' order by pg_relation_size('public'||'.'||indexrelname) desc;

-- [PostgreSQL中查询 每个表的总大小、索引大小和数据大小，并按总大小降序排序](https://blog.csdn.net/sunny_day_day/article/details/131455635)
SELECT
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_indexes_size(c.oid)) AS data_size,
    nspname AS schema_name,
    relname AS table_name
FROM
    pg_class c
LEFT JOIN
    pg_namespace n ON n.oid = c.relnamespace
WHERE
    relkind = 'r'
    AND nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema'
ORDER BY
    pg_total_relation_size(c.oid) DESC;

常用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
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 查找超过1小时的长事务
select count(*) from pg_stat_activity where state <> 'idle' and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval '3600 sec'::interval;

-- 查看处于等待锁状态
select * from pg_locks where not granted;
-- 查看等待锁的关系（表，索引，序列等）
select * from pg_class where oid=[上面查出来的relation];
-- 查看等待锁的数据库
select * from pg_database where oid=[上面查出来的database];
-- 锁表状态
select oid from pg_class where relname='可能锁表了的表';
-- 查询出结果则被锁
select pid from pg_locks where relation='上面查出的oid'; 

-- 关闭事务并回滚
select pg_cancel_backend(pid);
-- 若无法关闭，则强制杀死进程连接
select pg_terminate_backend(pid);
  
-- 查看连接信息，重点关注state处于idle in transaction
select * from pg_stat_activity;

-- 替换数据库名称
update pg_database set datname = 'destniationDb' where datname = 'sourceDb';
-- 清除数据库所有连接
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='test_db' AND pid<>pg_backend_pid();
-- 复制数据库，需断开sourceDb的全部连接
CREATE DATABASE destniationDb TEMPLATE sourceDb OWNER test_user; 

-- 清空表并重置自增序列
truncate table table1,table2 RESTART IDENTITY;

-- 导出数据库中数据，HEADER 可不带
\COPY (select * from table1) TO '/tmp/sql_output.csv' WITH CSV HEADER;

-- 输出删除全部表的sql
\COPY (SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' from pg_tables WHERE schemaname = 'public') TO '/tmp/sql_output.sql';

-- 添加部分索引（满足条件才建立索引）, where 和 select 语句的一致
create index [XXX] where [XXX]

-- 查看当前连接事务执行超时时间
show statement_timeout;
-- 设置数据库事务执行超时时间为 60 秒
AlTER DATABASE mydatabse SET statement_timeout='60s';
-- 设置用户事务执行超时时间为 5 分钟
ALTER ROLE guest SET statement_timeout='5min';

权限配置，PostgreSQL权限管理详解：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建只读组
create role readonly_group;
-- 创建只读用户继承只读组
create user reader with password 'reader' in role readonly_group;
-- 删除用户
drop user reader;
-- 将只读组权限赋给只读用户
grant readonly_group to reader;

-- 读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_group;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly_group;
-- 写权限
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_group;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO write_group;

主从&备份

参考资料：postgresql流式复制(Streaming Replication)、【PostgreSQL】PostgreSQL复制的监控、【PostgreSQL】导出数据库表(或序列)的结构和数据、pg_ctl、pg_basebackup

1
2
3
4
5
6
7
8
-- 创建流复制备份用户（主从）
create user replicator replication login encrypted password 'replicator'

-- 在主库创建一个物理复制槽（PG9.4引入，一个从库一个复制槽）
select pg_create_physical_replication_slot('phy_repl_slot_1');

-- 查看复制槽状态
select * from pg_replication_slots;

相关命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 冷备数据库(结合刷库命令可恢复数据库)，加 -s 参数只导出数据库表结构
nohup pg_dump postgresql://user:password@host:port/dbname -f db_dump.20240107.sql > dump.log 2>&1 &

# 新建一个数据库
pg_ctl init -D /home/postgres/db_data_dir

# 修改配置后重新加载配置
pg_ctl reload -D /home/postgres/db_data_dir
# 或者重启数据库
pg_ctl restart -D /home/postgres/db_data_dir

# 设置数据库默认连接密码
export PGPASSWORD=test_pswd

# 完整复制数据库
nohup pg_basebackup -h localhost -p port -U replicator -D /home/postgres/db_data1_dir -v -P -R -Xs > ./backup.log 2>&1 &

# 从库提升为主库
pg_ctl promote -D /home/postgres/db_data_dir

设置：postgresql.conf

1
2
3
4
5
6
7
8
wal_level = hot_standby
# 主备机不同步时，re_wind恢复结点
wal_log_hints = on
# 设置最大流复制数（从库数）
max_wal_senders = 3
wal_keep_segments = 64
# 支持从库读，以及从库再拉从库
hot_standby = on

设置：pg_hba.conf

1
2
3
4
5
6
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    replication     all             0.0.0.0/0             md5

设置 recovery.conf（自 Postgresql 12 起，recovery.conf 并入 postgresql.conf）：

1
2
3
standby_mode          = 'on'
primary_conninfo      = 'host=db_addr port=db_port user=replicator password=<password>'
primary_slot_name     = 'phy_repl_slot_1'

常见问题：

  1. 当自增主键报 duplicate key value violates unique constraint 主键冲突时，一般是因为存在手动分配 id 的数据（复制表或着手动插入分配了 id），自增主键 seqence TABLE_COLUMN_seq 没有更新，新插入一个值自增 id 和数据库已插入的分配 id 冲突，此时需要执行 SELECT setval('TABLE_COLUMN_seq', (SELECT max(COLUMN) FROM "TABLE")) 更新自增主键;

  2. 分析 sql 性能时，可在 sql 语句前增加 EXPLAIN 关键字，查看执行计划，EXPLAIN 一般不会实际执行 sql，但 sql 中带有子语句时，子语句可能会执行，所以为保险起见，最好是在事务中使用 EXPLAIN；eg:

    1
    2
    3
    begin;
    EXPLAIN select * from table1 where id=1;
    rollback;

    若要分析实际执行时间，可以使用 EXPLAIN ANALYZE，该选项会实际执行 SQL，也可以组合参数一起分析执行命令 EXPLAIN (ANALYZE, BUFFERS) select * from table1 where id=1;。

  3. 如果业务数据无法直接使用批量写入数据库，就最好在一个事务中写入（当然也得看数据量），在同一个事务中写入，不仅能利用事务本身的 ACID 特性，而且比单独分次执行 sql 效率更高；

  4. PG 数据库中，如果要使用 order 排序查询时，一般带主键的复合索引比单个字段索引更有效，以为 PG 数据在数据更新后，一般会乱序存储，导致单字段索引在查询时需要访问的页面会更多；

  5. PG 刚创建/删除索引后，不一定会及时生效，需要数据库运行一段时间后才会开始生效，如需要立即生效，可执行 ANALYZE VERBOSE table_name;命令。

后记

　　后面持续更新。。。

本文标题： Linux服务器运维文档

文章作者： Shaun

发布时间： 2021年02月09日 - 20时06分

最后更新： 2024年03月03日 - 13时18分

原始链接： http://cniter.github.io/posts/b59e3e7b.html

许可协议： "署名-非商业性使用-相同方式共享 4.0 国际" 转载请保留原文链接及作者。