PostgreSQL15安装和实现物理复制（主从配置）全程指南

1 概述

本文介绍如何在centos7或rocky9下安装postgresql15，并配置物理复制的全过程。postgresql安装采用shell脚本安装，一键执行，通俗易懂。

2 环境说明

3 部署步骤

3.1 主库安装postgresql

将附录5.1脚本拷贝执行：

sh install.sh
1

验证：

/usr/pgsql-15/bin/psql -h /opt/apps/postgresql/run -p 5432 -U postgresql -d postgres
1

3.2 从库安装postgresql

将附录5.1 脚本拷贝：并注释init()方法内的pg_init db_init

#初始化方法
function init(){
    os_init;
    pg_install;
    #pg_init;
    sh_init;
    #db_init;
}
init
1
2
3
4
5
6
7
8
9

3.3 使用pg_basebackup导出基准备份

在从库执行。

pg_basebackup -h 10.10.0.41 -U repuser -D /root/bak -p 5432 --wal-method=stream
1

3.4 将基准备份拷贝至从库数据目录中

从库执行。

rm -rf /opt/apps/postgresql/data
cp -r /root/bak /opt/apps/postgresql/data
chown -R postgresql:postgresql /opt/apps/postgresql/data
1
2
3

3.5 处理主从复制

从库执行。

3.5.1 postgresql.auto.conf增加primary_conninfo

/opt/apps/postgresql/data/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo='host=10.10.0.41 port=5432 user=repuser password=repuser123456'
recovery_target_timeline='latest'
1
2
3
4

3.5.2 创建standby.signal

su - postgresql -c "touch /opt/apps/postgresql/data/standby.signal"
1

3.5.3 启动从库

cd /opt/apps/postgresql && sh start.sh
1

3.5.4 查看从库日志

日志目录：/opt/apps/postgresql/data/pg_log/

4 验证

4.1 主库创建表，观察从库是否同步过去

略。

4.2 查询主从状态

4.2.1 主库查询

主库并不直接存储从库的复制状态。我们可以通过查看主库的 pg_stat_replication 视图，它会列出所有已连接并正在进行复制的从库信息

[root@localhost scripts]# /usr/pgsql-15/bin/psql -h /opt/apps/postgresql/run -p 5432 -U postgresql -d postgres
psql (15.5)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 13823
usesysid         | 16384
usename          | repuser
application_name | walreceiver
client_addr      | 10.10.0.42
client_hostname  | 
client_port      | 38120
backend_start    | 2024-01-25 13:00:52.184683+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/503EB18
write_lsn        | 0/503EB18
flush_lsn        | 0/503EB18
replay_lsn       | 0/503EB18
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2024-01-25 14:29:38.472977+08

postgres=# 
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

4.2.2 从库查询

[root@localhost scripts]# /usr/pgsql-15/bin/psql -h /opt/apps/postgresql/run -p 5432 -U postgresql -d postgres
psql (15.5)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
-[ RECORD 1 ]-----------+----------
pg_last_wal_receive_lsn | 0/503EB18
pg_last_wal_replay_lsn  | 0/503EB18

postgres=# 
1
2
3
4
5
6
7
8
9
10
11
12

5 附录

5.1 安装脚本

5.1.1 脚本示例

#!/bin/bash
set -x
#定义变量
base_dir="/opt/apps"
user_name="postgresql"
port="5432"
appuser_name="pguser"
appuser_password="pguser123456"
appdb_name="pgdb"
repuser_name="repuser"
repuser_password="repuser123456"
default_python_version=$(python --version 2>&1 | cut -d' ' -f2 | cut -d. -f1)
if [ "$default_python_version" -eq "2" ]; then
    appuser_password_encoded=$(python -c "import urllib; print(urllib.quote('$appuser_password'))")
elif [ "$default_python_version" -eq "3" ]; then
    appuser_password_encoded=$(python -c "import urllib.parse; print(urllib.parse.quote('$appuser_password'))")
else
    echo "无法识别的 Python 版本"
fi
# 获取OS版本信息
os_version=$(cat /etc/redhat-release | grep -oP '\d+' | head -1)
#获取Ip
host_ip=$(python -c "import socket;print([(s.connect(('8.8.8.8', 53)), s.getsockname()[0], s.close()) for s in [socket.socket(socket.AF_INET, socket.SOCK_DGRAM)]][0][1])")
#操作系统初始化
function os_init(){
# 获取 firewalld 的详细状态信息
systemctl stop firewalld
systemctl disable firewalld
# 获取当前 SELinux 状态
selinux_status=$(getenforce)
# 判断 SELinux 状态并执行相应操作
if [ "$selinux_status" == "Disabled" ]; then
    echo "当前 SELinux 状态为 disabled"
elif [ "$selinux_status" == "Enforcing" ]; then
    setenforce 0
    sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
    echo "SELinux 已禁用并配置为 disabled"
elif [ "$selinux_status" == "Permissive" ]; then
    sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
    echo "SELinux 已配置为 disabled"
else
    echo "未知的 SELinux 状态: $selinux_status"
fi

# 检查 limits.conf 文件是否包含所需配置
if grep -q "^\*\s*soft\s*nofile\s*65536" /etc/security/limits.conf && \
   grep -q "^\*\s*hard\s*nofile\s*65536" /etc/security/limits.conf && \
   grep -q "^\*\s*soft\s*nproc\s*65536" /etc/security/limits.conf && \
   grep -q "^\*\s*hard\s*nproc\s*65536" /etc/security/limits.conf; then
    echo "文件描述符限制已优化"
else
    # 执行配置命令
    echo "* soft nofile 65536" >> /etc/security/limits.conf
    echo "* hard nofile 65536" >> /etc/security/limits.conf
    echo "* soft nproc 65536" >> /etc/security/limits.conf
    echo "* hard nproc 65536" >> /etc/security/limits.conf
    echo "文件描述符限制已优化"
fi

# 检查 systemd 配置文件是否包含所需配置
if grep -q "^\s*DefaultLimitNOFILE=1000000" /etc/systemd/system.conf && \
   grep -q "^\s*DefaultLimitNPROC=65535" /etc/systemd/system.conf; then
    echo "Systemd 文件描述符和进程数限制已优化"
else
    # 执行配置命令
    cat >> /etc/systemd/system.conf << EOF
DefaultLimitNOFILE=1000000
DefaultLimitNPROC=65535
EOF
    systemctl daemon-reexec
    echo "Systemd 文件描述符和进程数限制已优化"
fi
#创建用户
if id "$user_name" &>/dev/null; then
    echo "User $user_name already exists."
else
    # 如果用户不存在，则创建用户
    useradd "$user_name"
    echo "User $user_name created successfully."
fi
#创建pg目录
mkdir -p $base_dir/$user_name/data
mkdir -p $base_dir/$user_name/run
mkdir -p $base_dir/$user_name/archive
mkdir -p $base_dir/$user_name/backup
mkdir -p $base_dir/$user_name/scripts
#更改目录权限
chown -R $user_name:$user_name $base_dir/$user_name
}
function pg_install(){
# 判断操作系统版本号，安装postgresql15-server
if [ "$os_version" == "7" ]; then
    cat /etc/redhat-release
    #配置epel源
    curl -o /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
    #增加postgresql源
    cat > /etc/yum.repos.d/postgresql.repo << EOF
[postgresql]
name=postgresql
baseurl=https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.9-x86_64/
enabled=1
gpgcheck=0
EOF
    #安装依赖包
    rpm -ivh https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.5-1.el7.x86_64.rpm
    #安装postgresql15-server
    yum install postgresql15-server postgresql15-contrib -y
elif [ "$os_version" == "9" ]; then
    cat /etc/redhat-release
    #增加postgresql源
    cat > /etc/yum.repos.d/postgresql.repo << EOF
[postgresql]
name=http
baseurl=https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-9.3-x86_64/
enabled=1
gpgcheck=0
EOF
    #安装postgresql15-server
    yum install postgresql15-server postgresql15-contrib -y
else
    echo "This script is intended to run on CentOS 7."
fi
}
function pg_init(){
#initdb
su - $user_name -c "/usr/pgsql-15/bin/initdb --username=$user_name --encoding=UTF8 --lc-collate=C --lc-ctype=en_US.utf8  --data-checksums -D $base_dir/$user_name/data"
#配置pg_hba.conf文件
cat >> $base_dir/$user_name/data/pg_hba.conf << EOF
host    replication     $repuser_name   0.0.0.0/0               md5
host    all             all             0.0.0.0/0               md5
EOF
#配置postgresql.conf
cat > $base_dir/$user_name/data/postgresql.conf << EOF
# basic
listen_addresses='0.0.0.0'
port=$port                                                  # 端口
unix_socket_directories='$base_dir/$user_name/run'          # socket目录
max_connections = 9999                                      # 最大连接数
superuser_reserved_connections = 10                         # 给超级用户预留的连接数
shared_buffers = 1GB                                        # 共享内存，一般设置为内存的1/4
effective_cache_size = 2GB                                  # 查询优化器估计的可用于缓存数据文件系统的总内存量,一般设置为内存的1/2
max_worker_processes = 48                                   # 最大工作线程，和cpu核数一致
max_parallel_workers_per_gather = 4                         # 单个查询在执行过程中可以使用的最大并行工作进程数，CPU核心数的1/12
max_parallel_workers = 24                                   # 整个数据库实例允许的最大并行进程数，CPU核心数的1/2到2/3之间
max_parallel_maintenance_workers = 6                        # 维护操作期间允许的最大并行进程数，CPU核心数的1/8
work_mem = 16MB                                             # 设置在写入临时磁盘文件之前查询操作(例如排序或哈希表)可使用的最大内存容量
maintenance_work_mem = 256MB                                # 在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的 最大的内存量
timezone = 'Asia/Shanghai'                                  # 系统时区
hot_standby = on                                            # 打开热备
# optimizer                                                 
default_statistics_target = 500                             # 默认100，ANALYZE在pg_statistic中存储的信息量，增大该值，会增加ANALYZE的时间，但会让解释计划更精准
# wal                                                       
max_wal_size = 1GB                                          # 建议与shared_buffers保持一致
min_wal_size = 80MB                                         # 建议max_wal_size/12.5
wal_log_hints = on                                          # 控制WAL日志记录的方式，建议打开
wal_level = replica                                         # wal日志写入级别，要使用流复制，必须使用replica或更高级别
wal_sender_timeout = 60s                                    # 设置WAL发送者在发送WAL数据时等待主服务器响应的超时时间
# archive                                                   
archive_mode = on                                           # 开启归档日志
archive_command = 'gzip < %p > $base_dir/$user_name/archive/%f.gz'
# log 近7天轮询
log_destination = 'stderr'                                  # 日志格式
logging_collector = on                                      # 日志收集器
log_directory = 'pg_log'                                    # 日志目录 $PGDATA/pg_log
log_filename = 'postgresql-%Y-%m-%d.log'                    # 日志名称格式
log_rotation_age = 43200                                    # 日志保留时间单位是分钟  
log_file_mode = 0600                                        # 日志文件的权限
log_rotation_size = 0                                       # 日志的最大尺寸，设置为零时将禁用基于大小创建新的日志文件
log_truncate_on_rotation = on                               # 这个参数将导致PostgreSQL截断（覆盖而不是追加）任何已有的同名日志文件
log_min_duration_statement = 0                              # 如果语句运行至少指定的时间量，将导致记录每一个这种完成的语句的持续时间
log_duration = on                                           # 每一个完成的语句的持续时间被记录
log_lock_waits = on                                         # 控制当一个会话为获得一个锁等到超过deadlock_timeout时，是否要产生一个日志消息
log_statement = 'mod'                                       # 控制哪些 SQL 语句被记录。有效值是 none (off)、ddl、mod和 all（所有语句）。ddl记录所有数据定义语句，例如CREATE、ALTER和 DROP语句。mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM
log_timezone = 'Asia/Shanghai'                              # 设置在服务器日志中写入的时间戳的时区
# sql                                                       
statement_timeout = 300000                                  # 语句执行超时时间 5分钟
idle_in_transaction_session_timeout = 300000                # 事务空闲超时时间 5分钟
idle_session_timeout = 1800000                              # 会话空闲超时时间 30分钟
lock_timeout = 60000                                        # 等锁超时时间 1分钟
EOF
}
#启动和开机自启动
function sh_init(){
#启动服务
su - $user_name -c "/usr/pgsql-15/bin/pg_ctl -D $base_dir/$user_name/data -l $base_dir/$user_name/pg.log start"
#配置开机自启动
chmod +x /etc/rc.d/rc.local
cat >> /etc/rc.d/rc.local << EOF
su - $user_name -c "/usr/pgsql-15/bin/pg_ctl -D $base_dir/$user_name/data -l $base_dir/$user_name/pg.log start"
EOF
#创建启动、停止、重启脚本
cat > $base_dir/$user_name/start.sh << EOF
#!/bin/bash
su - $user_name -c "/usr/pgsql-15/bin/pg_ctl -D $base_dir/$user_name/data -l $base_dir/$user_name/pg.log start"
EOF
cat > $base_dir/$user_name/stop.sh << EOF
#!/bin/bash
su - $user_name -c "/usr/pgsql-15/bin/pg_ctl -D $base_dir/$user_name/data -l $base_dir/$user_name/pg.log stop"
EOF
cat > $base_dir/$user_name/restart.sh << EOF
#!/bin/bash
su - $user_name -c "/usr/pgsql-15/bin/pg_ctl -D $base_dir/$user_name/data -l $base_dir/$user_name/pg.log restart"
EOF
#创建备份脚本
cat > $base_dir/$user_name/scripts/backup.sh << EOF
#!/bin/bash
set -ex
cmd="/usr/bin/pg_dump -Fc -v --dbname=postgresql://$appuser_name:$appuser_password_encoded@$host_ip:$port/$appdb_name -f $base_dir/$user_name/backup/$appdb_name-\$(date +%Y-%m-%d).dmp"
\$cmd
find $backup_dir -name "*.dmp" -mtime +30 -exec rm -f {} \;
EOF
#添加定时任务脚本
cat > $base_dir/$user_name/scripts/crontab.sh << EOF
#!/bin/bash
# 检查是否存在 crontab，如果不存在，创建一个空的 crontab
if [ -z "$(crontab -l)" ]; then
    echo "" | crontab -
fi
(crontab -l ; echo "0 23 * * * sh $base_dir/$user_name/scripts/backup.sh > $base_dir/$user_name/backup/backup.log 2>&1")|crontab -
EOF
chmod +x $base_dir/$user_name/scripts/crontab.sh
/bin/sh $base_dir/$user_name/scripts/crontab.sh
#创建恢复脚本
cat > $base_dir/$user_name/scripts/pg_restore.sh << EOF
#!/bin/bash
pg_restore -v --dbname=postgresql://$appuser_name:$appuser_password_encoded@$host_ip:$port/$appdb_name $base_dir/$user_name/backup/$appdb_name.dmp
EOF
#创建psql命令示例
cat > $base_dir/$user_name/scripts/psql.txt << EOF
/usr/pgsql-15/bin/psql -h $base_dir/$user_name/run -p $port -U $user_name -d postgres
EOF
}
function db_init(){
#创建init.sql文件
cat > $base_dir/$user_name/scripts/init.sql << EOF
create USER $repuser_name with login replication encrypted password '$repuser_password';
-- 创建数据库
CREATE DATABASE $appdb_name;
-- 连接到新创建的数据库
\c $appdb_name;
-- 创建用户
CREATE USER $appuser_name WITH PASSWORD '$appuser_password';
-- 赋予用户所有权限
GRANT ALL PRIVILEGES ON DATABASE $appdb_name TO $appuser_name;
-- 将 appdb 的所有权（OWNER）设置为 $appuser_name
ALTER DATABASE $appdb_name OWNER TO $appuser_name;
EOF
#创建drop.sql
cat > $base_dir/$user_name/scripts/drop.sql << EOF
drop database $appdb_name;
drop user $appuser_name;
EOF
#执行sql
/usr/pgsql-15/bin/psql -h $base_dir/$user_name/run -p $port -U $user_name -d postgres -f $base_dir/$user_name/scripts/init.sql
}
#初始化方法
function init(){
    os_init;
    pg_install;
    pg_init;
    sh_init;
    db_init;
}
init
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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264

5.1.2 脚本解析

上述脚本包含了在Linux环境中安装和配置PostgreSQL 15数据库的详细步骤，同时涵盖了数据库初始化、用户与权限设置、日志管理、备份恢复策略以及操作系统层面的优化等内容。以下是详细的解析：

1. 环境变量定义：
   • 定义了如base_dir（基础目录）、user_name（PostgreSQL运行用户）、port（数据库服务端口）、appuser_name及appuser_password（应用连接数据库的用户名和密码）、appdb_name（应用数据库名）等变量。
   • 使用Python确定默认版本，并编码应用程序用户的密码以确保安全地存储和使用。
2. 操作系统初始化：
   • 关闭并禁用firewalld防火墙服务。
   • 检查并根据需要调整SELinux状态，使其变为disabled。
   • 设置文件描述符和进程数限制，以便PostgreSQL可以打开足够多的文件句柄和并发处理更多连接。
   • 创建名为postgresql的系统用户及其相关的目录结构，并赋予相应的权限。
3. PostgreSQL安装：
   • 根据不同的Red Hat/CentOS版本配置Yum仓库源。
   • 安装依赖包，例如libzstd库。
   • 安装PostgreSQL 15服务器和contrib扩展包。
4. PostgreSQL初始化：
   • 初始化数据库集群，创建数据目录，使用initdb命令指定用户名、字符集、校验和、时区和其他参数。
   • 配置pg_hba.conf文件以控制不同用户的认证方式，包括从任何IP地址通过md5认证进行复制和一般连接。
   • 编辑postgresql.conf以设置监听地址、端口、socket路径、最大连接数、内存相关参数（如shared_buffers、effective_cache_size、work_mem等），以及WAL相关参数（如wal_level、max_wal_size、min_wal_size等）。
   • 设置日志记录相关的参数，包括日志级别、时间戳时区、语句记录类型（如仅记录DDL和修改数据的SQL语句）以及日志文件轮转策略。
5. 数据库和用户管理：
   • db_init函数中，创建了一个SQL脚本用于初始化数据库和用户账号，包括创建一个具有特定权限的应用程序用户和数据库，并为复制设置一个专用用户。
   • 提供了创建和删除数据库及用户的SQL脚本示例。
6. 服务启动与自启动配置：
   • 创建启动、停止、重启PostgreSQL服务的bash脚本，方便操作。
   • 配置系统自启动脚本，保证系统重启后自动启动数据库服务。
7. 备份与恢复：
   • 创建一个定期执行的备份脚本，使用pg_dump工具对数据库进行完整备份，并按日期命名备份文件，同时清理超过30天的旧备份。
   • 创建一个用于恢复数据库的pg_restore脚本。
8. 定时任务：
   • 添加到crontab的定时任务，每天晚上11点执行备份脚本，将备份输出重定向到日志文件。

5.3 启停脚本

• 启动：/opt/apps/postgresql/start.sh
• 停止：/opt/apps/postgresql/stop.sh
• 重启：/opt/apps/postgresql/restart.sh

5.4 编写python3脚本向库中插入数据

建表语句

CREATE TABLE "t1" (
	"c1" INTEGER NULL DEFAULT NULL
);
1
2
3

安装psycopg包

若没有pip3命令请执行：yum install python3-pip -y
pip3 install psycopg
1
2

脚本示例：向主库中插入10000条数据

#!/usr/bin/python3
import psycopg
host = '10.10.0.41'
port = '5432'
database = 'pgdb'
user = 'pguser'
password = 'pguser123456'
# 创建连接和游标
conn = psycopg.connect(host=host, port=port, dbname=database, user=user, password=password)
cursor = conn.cursor()
# 假设有一批数据要插入，这里以列表形式表示
data_to_insert = [(i,) for i in range(1, 100001)]
# 批量插入数据的 SQL 语句模板
insert_query = """
    INSERT INTO t1 (c1) VALUES (%s);
"""
# 执行批量插入操作
cursor.executemany(insert_query, data_to_insert)
# 提交事务
conn.commit()
# 关闭游标和连接
cursor.close()
conn.close()
print("数据已成功批量插入到 t1 表中！")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

码字不易，喜欢本文请点赞。