postgresql-15.4 多节点主从(读写分离)

liuian 2025-08-31 04:04 26 浏览

1、下载软件

[root@TX-CN-PostgreSQL01-252 software]# wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz

2、初始化服务器环境

2.1 系统参数优化

systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
 
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

cat > /etc/sysctl.conf <<"EOF"
vm.swappiness=10
vm.zone_reclaim_mode=0
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
kernel.shmmax = 1288490188
kernel.shmall = 314572
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
EOF

sysctl -p

cat >> /etc/security/limits.conf <<"EOF" 
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000
EOF


echo "* - nproc unlimited" > /etc/security/limits.d/90-nproc.conf
echo "session required pam_limits.so" >> /etc/pam.d/login

# 关闭THP
# 这段配置的目的是在系统启动时禁用透明大页面（Transparent Huge Pages）。透明大页面是一种Linux内核的功能，用于改进内存管理性能。然而，在某些情况下（如某些数据库应用），透明大页面可能导致性能问题，因此禁用它们可能是有益的。
# root用户下
# 在vi /etc/rc.local最后添加如下代码
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

chmod +x /etc/rc.d/rc.local
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag

3、源码安装

3.1 安装依赖包

yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl readline readline-devel  python36 tcl openssl ncurses-devel openldap pam flex

3.2 创建用户

groupadd postgres
useradd postgres -g postgres
echo 'postgres#!188.' | passwd --stdin 'postgres'
chown postgres.postgres /home/postgres/.bash_profile

3.3 创建目录

-- 创建目录
 mkdir -p /usr/local/disk_vdb1/postgresql-15.4/{pgdata,archive,scripts,backup,pg15,soft,log}
 chown -R postgres:postgres /usr/local/disk_vdb1/postgresql-15.4
 chmod -R 775 /usr/local/disk_vdb1/postgresql-15.4

3.4 编译安装

-- 编译
 cp /root/software/postgresql-15.4.tar.gz /tmp/
 su - postgres
 cd /tmp/
 tar zxvf postgresql-15.4.tar.gz
 cd postgresql-15.4
 ./configure --prefix=/usr/local/disk_vdb1/postgresql-15.4/pg15
 make -j 16 && make install
 
# 编译完成，最后一行显示：All of PostgreSQL successfully made. Ready to install.
 
 如果希望编译所有能编译的东西，包括文档（HTML和手册页）以及附加模块（contrib），这样键入：
 make world -j 16 && make install-world

3.5 配置环境变量

-- 配置环境变量
cat >>  ~/.bash_profile <<"EOF"
export PGPORT=5432
export PGDATA=/usr/local/disk_vdb1/postgresql-15.4/pgdata
export PGHOME=/usr/local/disk_vdb1/postgresql-15.4/pg14
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF

source  ~/.bash_profile

3.6 初始化数据库

-- 初始化
su - postgres
/usr/local/disk_vdb1/postgresql-15.4/pg15/bin/initdb -D /usr/local/disk_vdb1/postgresql-15.4/pgdata -E UTF8 --locale=en_US.utf8 -U postgres

3.7 修改参数

-- 修改参数
cat > /usr/local/disk_vdb1/postgresql-15.4/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'          # what IP address(es) to listen on;
port = 5432                             # (change requires restart)
max_connections = 10000                 # (change requires restart)
# - Authentication -
password_encryption = md5       # md5 or scram-sha-256

# - Memory -
shared_buffers = 8GB                    # min 128kB  官方推荐内存设置带大小为系统内存的1/4
temp_buffers = 64MB                     # min 800kB
work_mem = 64MB                         # min 64kB
max_stack_depth = 4MB                   # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option


#------------------------------------------------------------------------------
## WRITE-AHEAD LOG
##------------------------------------------------------------------------------
wal_level = replica                     # minimal, replica, or logical
fsync = on                              # flush data to disk for crash safety
synchronous_commit = on         # synchronization level;
wal_log_hints = on                      # also do full page writes of non-critical updates
## - Checkpoints -
checkpoint_timeout = 10min              # range 30s-1d
max_wal_size = 1GB
min_wal_size = 80MB

# - Archiving -
archive_mode = on               # enables archiving; off, on, or always
# # (change requires restart)
archive_command = 'cp %p /usr/local/disk_vdb1/postgresql-15.4/archive/%f'  # command to use to archive a logfile segment


max_wal_senders = 10            # max number of walsender processes 设置了可以最多有几个流复制的链接
# (change requires restart)
wal_keep_size = 1024            # in megabytes; 0 disables
max_slot_wal_keep_size = 10     # in megabytes; -1 disables
wal_sender_timeout = 120s       # in milliseconds; 0 disables

# - Standby Servers -
hot_standby = on                        # "off" disallows queries during recovery

#------------------------------------------------------------------------------
## REPORTING AND LOGGING
##------------------------------------------------------------------------------
#
## - Where to Log -
#
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on                  # Enable capturing of stderr and csvlog
log_directory = '/usr/local/disk_vdb1/postgresql-15.4/log'                      # directory where log files are written,
## can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_truncate_on_rotation = on           # If on, an existing log file with the
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 50MB                        # Automatic rotation of logfiles will
log_min_duration_statement = 2000 # 配置数据库慢查询时间，如果超过配置的时间，就会将查询的SQL语句记录到日志

# - What to Log -
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] '            # special values:
log_timezone = 'Asia/Shanghai'
#
datestyle = 'iso, mdy'
# #intervalstyle = 'postgres'
timezone = 'Asia/Shanghai'

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'                     # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
#
# # default configuration for text search
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = ''   # citus (change requires restart)
unix_socket_directories = '/usr/local/disk_vdb1/postgresql-15.4/pgdata'
EOF


cat   > /usr/local/disk_vdb1/postgresql-15.4/pgdata/pg_hba.conf << EOF
# TYPE  DATABASE    USER    ADDRESS       METHOD
host      all       all    127.0.0.1/32     md5
host      all       all    0.0.0.0/0        md5
local     all                 all          trust
host  replication  replica 0.0.0.0/0        md5
EOF

3.8 启动数据库

-- 启动
 su - postgres
 pg_ctl start
 pg_ctl status
 pg_ctl stop
 pg_ctl stop -m fast

3.9 配置系统服务

-- 配置系统服务
cat > /etc/systemd/system/PG15.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/usr/local/disk_vdb1/postgresql-15.4/pgdata
OOMScoreAdjust=-1000
ExecStart=/usr/local/disk_vdb1/postgresql-15.4/pg15/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/usr/local/disk_vdb1/postgresql-15.4/pg15/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/disk_vdb1/postgresql-15.4/pg15/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
EOF


systemctl daemon-reload
systemctl enable PG15
systemctl start PG15
systemctl status PG15

4、创建用户

系统用户 postgres 密码 postgres#!188.
数据库账号密码: Ydfoie@@df1213%
超管账号 admin  密码: Ydfoie#df1213%
slave账号  账号 slave 密码 qwe!@#ZXC
应用账号 账号 dbuser   密码 Xinhulian111aaa@dbuser

su - postgres
psql
# 超管用户密码
ALTER USER postgres WITH PASSWORD 'Ydfoie@@df1213%';
# 创建同步账号
create role replica login replication encrypted password 'qwe!@#ZXC';

# 创建超管账号admin并授予superuser角色，并允许通过客户端登录
CREATE ROLE admin superuser PASSWORD 'Ydfoie#df1213%' login;

# 创建应用账号dbuser并授权相应数据库、登录、创建数据库权限
CREATE USER dbuser WITH PASSWORD 'Xinhulian111aaa@dbuser';
GRANT ALL PRIVILEGES ON DATABASE db_name TO dbuser;
ALTER USER dbuser WITH LOGIN;
ALTER USER dbuser CREATEDB;

5、启动从库

5.1 从库部署

按照上面主库对从库进行初始化但是不用创建用户和启动

5.2 在从库上同步主库信息

# 先删除从库初始化后的数据
rm -rf /usr/local/disk_vdb1/postgresql-15.4/pgdata/*
# 进行同步
pg_basebackup -D /usr/local/disk_vdb1/postgresql-15.4/pgdata -h 10.10.64.23 -p 5432 -U replica -F p   -X s -v -P -R
# 启动从库
systemctl enable PG15
systemctl start PG15

5.3 验证主从同步

# 从库上验证
[root@TX-CN-PostgreSQL01-252 ~]# ps -ef | grep walreceiver| egrep -v grep
postgres 23043 23037  0 11:54 ?        00:00:00 postgres: walreceiver streaming 0/8000060 #出现此进程表示正常
# 主库上验证 
[root@TX-CN-PostgreSQL01-12 pg15]# ps -ef | grep walsender| egrep -v grep #当有多个从库时会出现多条记录
postgres 25450 13456  0 11:54 ?        00:00:00 postgres: walsender replica 10.10.64.26(43666) streaming 0/A000060
postgres 25882 13456  0 11:55 ?        00:00:00 postgres: walsender replica 10.10.64.32(41616) streaming 0/A000060

pgsql if