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
63
64
65
66
67
# 只列出含 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 -T | egrep -i -B100 'killed process'
# dmesg 的时间可能不对，可以结合 /var/log/messages 一起看，[dmesg 时间误差现象](https://www.cnblogs.com/edisonfish/p/17283958.html)
cat /var/log/messages | grep "Killed process"

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

# 查看硬盘序列号
sudo lshw -class disk | grep serial

正则表达式

常用正则：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

编译安装

参考自：【CentOS7】PostgreSQL-10.3的安装

  1. 安装编译工具：

    1
    yum install -y vim lrzsz tree wget gcc gcc-c++ readline-devel zlib-devel

  2. 进入/usr/local/目录下：cd /usr/local

  3. 下载 tar 包：curl -O https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz

  4. 解压：tar -xzvf postgresql-16.2.tar.gz

  5. 编译安装：

    1
    2
    3
    4
    5
    cd /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.」代表安装成功

  6. 配置系统环境变量：vi /etc/profile

    1
    2
    3
    4
    5
    6
    ...
    # /etc/profile 文件末尾添加
    export PGHOME=/usr/local/pgsql-16.2
    export PGDATA=$PGHOME/data
    export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
    export PATH=$PGHOME/bin:$PATH

  7. 使配置文件立即生效：source /etc/profile

  8. 创建数据库用户：useradd -m -d /home/postgres postgres

  9. 切换到数据库用户：su postgres

  10. 初始化数据库：pg_ctl init -D /home/postgres/db_data

  11. 启动数据库：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
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
#! /bin/sh

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems.  You should edit some of the variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
#   /etc/rc.d/rc0.d/K02postgresql
#   /etc/rc.d/rc1.d/K02postgresql
#   /etc/rc.d/rc2.d/K02postgresql
#   /etc/rc.d/rc3.d/S98postgresql
#   /etc/rc.d/rc4.d/S98postgresql
#   /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.

# Original author:  Ryan Kirkpatrick <pgsql@rkirkpat.net>

# contrib/start-scripts/linux

## EDIT FROM HERE

###### 上面不改 #####################
# Installation prefix
prefix=/usr/local/pgsql-16.2

# Data directory
PGDATA="/home/postgres/db_data"
###### 下面不改 #####################

# Who to run postgres as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

# It's often a good idea to protect the postmaster from being killed by the
# OOM killer (which will tend to preferentially kill the postmaster because
# of the way it accounts for shared memory).  To do that, uncomment these
# three lines:
#PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
#PG_MASTER_OOM_SCORE_ADJ=-1000
#PG_CHILD_OOM_SCORE_ADJ=0
# Older Linux kernels may not have /proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except for having a different
# range of scores.  For such a system, uncomment these three lines instead:
#PG_OOM_ADJUST_FILE=/proc/self/oom_adj
#PG_MASTER_OOM_SCORE_ADJ=-17
#PG_CHILD_OOM_SCORE_ADJ=0

## STOP EDITING HERE

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up postgres.  (If you want the script to wait
# until the server has started, you could use "pg_ctl start" here.)
DAEMON="$prefix/bin/postgres"

# What to use to shut down postgres
PGCTL="$prefix/bin/pg_ctl"

set -e

# Only start if we can find postgres.
test -x $DAEMON ||
{
	echo "$DAEMON not found"
	if [ "$1" = "stop" ]
	then exit 0
	else exit 5
	fi
}

# If we want to tell child processes to adjust their OOM scores, set up the
# necessary environment variables.  Can't just export them through the "su".
if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]
then
	DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"
fi


# Parse command line parameters.
case $1 in
  start)
	echo -n "Starting PostgreSQL: "
	test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
	su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"
	echo "ok"
	;;
  stop)
	echo -n "Stopping PostgreSQL: "
	su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"
	echo "ok"
	;;
  restart)
	echo -n "Restarting PostgreSQL: "
	su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s"
	test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
	su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &"
	echo "ok"
	;;
  reload)
	echo -n "Reload PostgreSQL: "
	su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
	echo "ok"
	;;
  status)
	su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
	;;
  *)
	# Print help
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

接下来有两种方式：

一种是直接执行：cd /etc/rc.d/init.d/ && chkconfig --add postgresql；

一种是修改 /etc/rc.d/rc.local 文件：vi /etc/rc.d/rc.local，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/bin/bash
# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES
#
# It is highly advisable to create own systemd services or udev rules
# to run scripts during boot instead of using this file.
#
# In contrast to previous versions due to parallel execution during boot
# this script will NOT be run after all other services.
#
# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure
# that this script will be executed during boot.
 
exec 2> /tmp/rc.local.log      # send stderr from rc.local to a log file
exec 1>&2                      # send stdout to the same log file
echo "rc.local starting..."        # show start of execution
set -x

touch /var/lock/subsys/local

cd /etc/rc.d/init.d/
sudo sh postgresql start &      # 以root执行，不然可能会出现权限错误，&表示后台执行
 
# 脚本执行完后也给个日志
echo "rc.local completed"

添加可执行权限：chmod a+x /etc/rc.d/rc.local，最后查看一下 rc.local 服务是否启动：

1
2
3
4
5
6
7
8
systemctl status rc-local.serives
 
# 启动命令
systemctl enable rc-local.service
systemctl start rc-local.service
 
# 查看数据库服务
ps -ef | grep postgres

若要在容器中设置自启动，在没给容器提权的情况下，则需要第三种方式：将 /etc/rc.d/init.d/postgresql 放进 /root/.bashrc 中启动，vi /root/.bashrc，

1
2
3
4
5
...
# /root/.bashrc 文件末尾添加
if [ -f /etc/rc.d/init.d/postgresql ]; then
    sh /etc/rc.d/init.d/postgresql start > /tmp/postgresql.start.log 2>&1
fi

原理是：docker 容器在启动时，会自动执行 ~/.bashrc 文件，加载环境变量，当有其他命令在该文件时，也会一起执行。

当然，容器中自启动更普遍的方式应该是在镜像/容器中通过 CMD 或者 ENTRYPOINT 直接指定 shell 脚本启动执行。

配置文件设置

PG 电子书：PostgreSQL 14 Internals

配置参数解析文档：PostgresqlCO.NF: 人类的PostgreSQL配置

自动化参数调优：PGTune

PG13 一个推荐的配置解析（SSD，48 核，128GB 内存，机器资源独占，混布相当于降低内存和 cpu）

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
# 允许任何机器连接。默认只允许本地连接
listen_addresses = '*'

# 数据库连接端口。默认为5432
port = 5432

# 最大允许512个连接。默认为100
max_connections = 512

# 锁超时20s。默认为0，不超时
lock_timeout = 20000
# sql超时60s。默认为0，不超时
statement_timeout = 60000

# 数据库用于缓存数据的使用内存大小，一般设置为系统内存的 25%～30%，不宜过大，最多不超过40%。默认为128MB
shared_buffers = 64GB

# 查询优化器可用的内存大小，只是预估，不实际使用，值越大，越倾向于索引扫描，一般设置为系统内存的30%～50%，最大不超过90%。默认为4GB
effective_cache_size = 96GB

# 数据库维护性操作使用的内存（eg：vacuum, create index等），若需加快维护速度，可临时增大该参数 set maintenance_work_mem = 2GB;。默认为64MB
maintenance_work_mem = 64MB

# 尚未写入磁盘的WAL数据的共享内存量，增大该值有利于提高写入性能，不建议太大，最多不超过 128MB。默认与shared_buffers一致
wal_buffers = 16MB

# 查询优化器中统计信息的详细程度，越大越详细，查询优化器的决策越好，但会增加 ANALYZE 耗时。默认为100
default_statistics_target = 100

# 查询优化器获取一个随机页的cost（相比于一个顺序扫描页（seq_page_cost=1）的cost为1），该值相对seq_page_cost越小，越倾向于索引扫描，但不可低于 seq_page_cost。默认为4
# 默认值可以被想成把随机访问建模为比顺序访问慢 40 倍，而期望 90% 的随机读取会被内存缓存。
random_page_cost = 1.1

# 顺序扫描时并行 I/O 操作的最大数量。默认为1
effective_io_concurrency = 8

# 每个排序操作、哈希表等操作所能使用的内存大小，增大该值可以提高某些查询性能，但设置过高可能会导致内存耗尽。默认为4MB
work_mem = 4MB

# 是否为主共享内存区域请求巨型页，巨型页面的使用会导致更小的页面表以及花费在内存管理上的 CPU 时间更少，从而提高性能。默认为try
huge_pages = try

# 最大工作进程数，增加该值可以增加数据库并行处理能力，过大可能导致资源消耗过多，一般可以设置为CPU核数。默认为8
max_worker_processes = 16

# 并行查询的最大并行数。默认为2
max_parallel_workers_per_gather = 4

# 与 max_worker_processes 相同。默认为8
max_parallel_workers = 16

# 数据库维护性操作的最大并行数。默认为2
max_parallel_maintenance_workers = 4

# WAL级别，minimal<replica<logical，级别越高记录的WAL越详细，replica用于物理复制，logical用于逻辑复制。默认为replica
wal_level = replica

# 启用文件系统同步，确保即使系统发生崩溃或断电等异常情况，数据也不会丢失，在高写入负载下，会导致性能下降。默认为on
fsync = on

# 最小的 WAL 文件大小，WAL 文件用于确保数据的持久性和恢复能力。默认为80MB
min_wal_size = 128MB

# 最大的 WAL 文件大小，过小会导致频繁的 checkpoint，从而影响性能，过大则可能会占用过多存储空间，该参数仅为软限制，特殊情况会超出，该参数最好略大于 min_wal_size+wal_keep_size。默认为1GB
max_wal_size = 10GB

# 控制checkpoint（用来保证内存数据和磁盘数据一致性和完整性）分散写入，值越大，越分散，写入耗时越长，系统负载越小，一般设置为0.7～0.9，对于写入较大的数据库，该值越大越好。默认为0.5
checkpoint_completion_target = 0.9

### --- 主从同步相关参数 ---
## 主库设置
## 确保 wal_level 为 replica或logical
# 最大的从库连接数，需大于当前从库数。默认为10
max_wal_senders = 10

# WAL文件保留的最小磁盘空间，调大该参数可防止从库同步失败。默认为0，不保留
wal_keep_size = 8GB

# 主库等待从库接收WAL文件后响应的超时时间。默认为60s
wal_sender_timeout = 300s

# 最大复制槽数量，和 max_wal_senders 相同。默认为10
max_replication_slots = 10

## 从库设置
# 连接主库的信息
primary_conninfo = "host=master-db-host port=5432 user=replicator password=pwd"

# 指定主库的复制槽名称
primary_slot_name = 'xxx'

# 允许从库进行只读查询。默认为on
hot_standby = on

# 从库向主库发送状态信息的时间间隔（状态信息包括 WAL 接收器的状态、当前接收进度等数据，主数据库可以使用这些信息监控复制的健康状况和同步延迟）。默认为10s
wal_receiver_status_interval = 10s

# 允许从库向主库发送反馈信息，以减少查询延迟和 WAL 日志的删除（启用该配置需要确保有足够的磁盘空间，并定期监控主库的 WAL 文件状态，同时需要注意观察主从同步的延迟）。默认为off
hot_standby_feedback = on

# 从库等待 WAL 发送的超时时间。默认为60s
wal_receiver_timeout = 300s

### --- log 相关参数 ---
# 将日志输出到标准错误输出
log_destination = 'stderr'
# 启用日志收集器（按照 log_directory 和 log_filename 指定的路径保存）。默认为off
logging_collector = on
# 日志文件的存储目录
log_directory = 'log'
# 日志文件的命名格式
log_filename = 'pgsql-%Y%m%d_%H%M%S.log'
# 日志文件切分周期
log_rotation_age = 1d
# 不根据文件大小切分
log_rotation_size = 0
# 日志记录的最低级别
log_min_messages = warning
# 记录 SQL 语句的最低错误级别
log_min_error_statement = error
# 记录慢查询时间，单位毫秒，超过该值会记录到日志中。默认不记录
log_min_duration_statement = 5000
# 日志格式。默认只记录时间和进程id
log_line_prefix = '<%m [%p] %r %u@%d> '
# 记录等待锁时间超过deadlock_timeout的日志。默认为off，不记录
log_lock_waits = on

### --- autovacuum 相关参数（需根据表大小，表数据更新频率调整，系统资源） ---
# 启用自动清理，需同时开启track_counts。默认为on
autovacuum = on
# 执行自动清理的最大并发数。默认为3
autovacuum_max_workers = 3
# 每分钟启动一次自动清理进程。默认为1min
autovacuum_naptime = 1min
# 当表中的死行数超过该阈值时，触发 VACUUM 操作。默认为50
autovacuum_vacuum_threshold = 10000
# 在表中插入的行数超过此阈值时，触发 VACUUM 操作。默认为1000
autovacuum_vacuum_insert_threshold = 10000
# 当表中有足够的变化（如插入、更新、删除）且行数超过该阈值时，触发 ANALYZE 操作以更新统计信息。默认为50
autovacuum_analyze_threshold = 5000
# 当表中死行数达到表行数的5%时触发 VACUUM。默认为0.2
autovacuum_vacuum_scale_factor = 0.05
# 在表中插入的行数超过5%时，触发 VACUUM 操作。默认为0.2
autovacuum_vacuum_insert_scale_factor = 0.05
# 当数据变化超过表大小的 5% 时，触发 ANALYZE 操作，更新表的统计信息。默认为0.1
autovacuum_analyze_scale_factor = 0.05
# 每次vacuum操作执行一定量的 I/O 操作后休眠的时间（毫秒），目的是限制自动清理操作对磁盘 I/O 的影响，避免过多的 I/O 操作导致系统性能下降，可增加该值以减少对系统性能的影响。默认是2ms，需与autovacuum_vacuum_cost_limit配合使用
autovacuum_vacuum_cost_delay = 20ms
# 每次vacuum操作的最大 I/O 成本。默认是 -1（即使用 vacuum_cost_limit），可降低该值以减少对系统性能的影响
autovacuum_vacuum_cost_limit = 200


可单独针对表设置vacuum参数：
ALTER TABLE large_table
SET (
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.05
);

psql

1
2
3
4
5
6
7
8
9
10
11
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] # 查看表大小
\di+ [index_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';
子查询优化

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 无法强制使用索引，所以只能通过一些其他方法来引导查询优化器使用索引，比如调整查询条件；
  • 获取分组中最大值对应的一行数据；
  • 临时禁用索引 table_col1_idx：update pg_index set indisvalid = false where indexrelid = 'table_col1_idx'::regclass;
  • 查看哪些索引长期未使用，查询 pg_stat_user_indexes 表；

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 创建只读组
create role readonly_group;
-- 设置只读模式
ALTER ROLE readonly_group SET default_transaction_read_only TO 'on';
-- 创建只读用户继承只读组
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
9
10
wal_level = hot_standby 
# PG12 之后，wal_level = replica

# 主备机不同步时，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' # PG12之后，删除该配置项
primary_conninfo      = 'host=db_addr port=db_port user=replicator password=<password>'
primary_slot_name     = 'phy_repl_slot_1'
区分主库从库

主要方式：从库的根目录下存在 recovery.conf 文件（PG12 之后无该文件，而是存在一个 0KB 的 standby.signal 文件）。

SELECT * FROM pg_stat_replication; 如果有结果（显示所有连接到该节点的从库），则表示当前节点为主库。

主库一般配置参数：

  • PG12 之后，wal_level = replica或logical；
  • max_wal_senders 一般设置较大，允许多个从库；
  • hot_standby，主库一般为 off；

从库一般配置参数：

  • hot_standby，从库为 on；
  • primary_conninfo，有连接到主库的相关配置信息；

并发 dump&restore 数据库

  1. 导出数据库全部表结构

    1
    pg_dump -d postgresql://user:pswd@host:port/db_name --schema-only -f db_name_schema.sql

  2. 导出外键约束

    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
    psql -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

  3. 导出数据库全局用户/权限

    1
    pg_dumpall -d postgresql://superuser:pswd@host:port --globals-only -f db_name_user.sql

  4. 4个并行任务导出全部数据

    1
    pg_dump -d postgresql://user:pswd@host:port/db_name --data-only -F d -j 4 -f ./db_name_data_dir

  5. 新建数据库实例

    1
    pg_ctl init -D ~/new_db_data

  6. 导入数据库全局用户/权限

    1
    psql -U superuser -p port -f db_name_user.sql

  7. 新建数据库

    1
    create database new_db_name owner owner_user

  8. 导入数据库全部表结构

    1
    psql -U superuser -p port -f db_name_schema.sql

  9. 移除新库外键约束

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    psql -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 \$\$;
    EOF

  10. 4个并行任务导入数据

    1
    pg_restore -d postgresql://owner_user:pswd@host:port/db_name -j 4 ./db_name_data_dir

  11. 恢复新库外键约束

    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，但仍然存在部分数据碎片无法再被重复利用（连续空间释放中间一部分，再重新分配后，可能导致少许剩余空间太小无法再利用，实时清理或合并这些小空间的代价又太大），且索引的膨胀不可避免（当数据被删除标记为死元组时，被删除数据的索引仍然存在，而 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 无需获取排它锁，更轻量。

　　针对 vacuum 不及时导致一直新申请磁盘空间膨胀的问题，PG 支持设置 autovacuum，根据系统资源调整相关参数后，可以使用 pg_stat_user_tables 视图监控表的膨胀情况，关注 n_dead_tup（死元组数量）和 last_autovacuum（上次vacuum时间）：SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;，以及使用 pg_stat_activity 视图检查 vacuum 进程的执行情况和影响：SELECT datname, pid, usename, query_start, state, query FROM pg_stat_activity WHERE query LIKE '%vacuum%';。

　　对于既成事实占用存储空间超大的数据库，缩减空间一个可能的方案是先 dump 数据，同时开始记录原数据库增量的 dml sql（log_statement=mod），新建一个数据库，用 dump sql 文件写入，记录 dump 最新的节点（时间或者啥 id，再将原数据库节点之外的数据迁移到新数据库中（用之前记录的增量 dml 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
#!/bin/bash

export PGPASSWORD=pwd

function create_idx() {
    sql_cmd=$1
    pid=$!
    echo "$pid [`date '+%Y-%m-%d_%H:%M:%S'`] start: $sql_cmd"
    psql -p 5432 -d db_name -c "$sql_cmd"
    echo "$pid [`date '+%Y-%m-%d_%H:%M:%S'`] end: $sql_cmd"
}

table1_idx_arr=(
    "create index concurrently table1_column1_idx on table1 (column1);"
    "create index concurrently table1_column2_idx on table1 (column2);"
)
{
for table1_idx in "${table1_idx_arr[@]}"; do
    create_idx "$table1_idx"
done
} > ./logs/table1_idx.log 2>&1 &
echo "$! creating table1_idx..."
pids+=($!)

table2_idx_arr=(
    "create index concurrently table2_column1_idx on table2 (column1);"
    "create index concurrently table2_column2_idx on table2 (column2);"
)
{
for table2_idx in "${table2_idx_arr[@]}"; do
    create_idx "$table2_idx"
done
} > ./logs/table2_idx.log 2>&1 &
echo "$! creating table2_idx..."
pids+=($!)

for pid in "${pids[@]}";do
    wait $pid
    ret=$?
    if [ $ret != 0 ];then
        echo "[`date '+%Y-%m-%d %H:%M:%S'`] ERROR [$0:$LINENO], pids[${pids[*]}], pid[$pid] exit with ret[$ret]"
    else
        echo "[`date '+%Y-%m-%d %H:%M:%S'`] INFO [$0:$LINENO], pids[${pids[*]}], pid[$pid] exit with ret[$ret]"
    fi
done

如果要控制并行数，可以增加等待机制，每次调用 create_idx "$table1_idx" 前，先调用 wait_create_idx。

1
2
3
4
5
6
7
8
9
10
proc_name="create index"
function wait_create_idx() {
    while true; do
        proc_cnt=`ps aux | grep "$proc_name" | wc -l`
        if [ $proc_cnt -le 10 ]; then # 10个并发进程
            break
        fi
        sleep 60 # 休眠60s
    done
}

常见问题：

  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,verbose,costs,buffers,timing) select * from table1 where id=1;。

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

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

  5. PG 刚创建/删除索引后，不一定会及时生效，需要数据库运行一段时间后才会开始生效，如需要立即生效，可执行 ANALYZE VERBOSE table_name;命令，离线或者低负载的时候可以执行 VACUUM VERBOSE ANALYZE table_name，清理表的同时更新统计信息，得到更好的 SQL 执行计划。

后记

　　后面持续更新。。。

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

文章作者： Shaun

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

最后更新： 2025年09月28日 - 09时45分

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

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