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

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+])

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

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地址]:[目标路径]

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































prefix=/usr/local/pgsql-16.2


PGDATA="/home/postgres/db_data"



PGUSER=postgres


PGLOG="$PGDATA/serverlog"


















PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin



DAEMON="$prefix/bin/postgres"


PGCTL="$prefix/bin/pg_ctl"

set -e


test -x $DAEMON ||
{
	echo "$DAEMON not found"
	if [ "$1" = "stop" ]
	then exit 0
	else exit 5
	fi
}



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



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'"
	;;
  *)
	
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

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










 
exec 2> /tmp/rc.local.log      
exec 1>&2                      
echo "rc.local starting..."        
set -x

touch /var/lock/subsys/local

cd /etc/rc.d/init.d/
sudo sh postgresql start &      
 

echo "rc.local completed"

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

1
2
3
4
5

...

if [ -f /etc/rc.d/init.d/postgresql ]; then
    sh /etc/rc.d/init.d/postgresql start > /tmp/postgresql.start.log 2>&1
fi

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
);

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 # 竖式显示记录

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;


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;

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

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);
  

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();

CREATE DATABASE destniationDb TEMPLATE sourceDb OWNER test_user; 


truncate table table1,table2 RESTART IDENTITY;


\COPY (select * from table1) TO '/tmp/sql_output.csv' WITH CSV HEADER;


\COPY (SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' from pg_tables WHERE schemaname = 'public') TO '/tmp/sql_output.sql';


create index [XXX] where [XXX]


show statement_timeout;

AlTER DATABASE mydatabse SET statement_timeout='60s';

ALTER ROLE guest SET statement_timeout='5min';

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;

1
2
3
4
5
6
7
8

create user replicator replication login encrypted password 'replicator'


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

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

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

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

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'

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

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 
            break
        fi
        sleep 60 
    done
}