postgresql-15.4 多节点主从(读写分离)
1、下载软件
[root@TX-CN-PostgreSQL01-252 software]# wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz2、初始化服务器环境
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/defrag3、源码安装
3.1 安装依赖包
yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl readline readline-devel python36 tcl openssl ncurses-devel openldap pam flex3.2 创建用户
groupadd postgres
useradd postgres -g postgres
echo 'postgres#!188.' | passwd --stdin 'postgres'
chown postgres.postgres /home/postgres/.bash_profile3.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.43.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_profile3.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 postgres3.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
EOF3.8 启动数据库
-- 启动
su - postgres
pg_ctl start
pg_ctl status
pg_ctl stop
pg_ctl stop -m fast3.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 PG154、创建用户
系统用户 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 PG155.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