环境准备
主机地址 | 操作系统 | 数据库版本 | 数据库角色 |
10.124.50.10 | Ubuntu 20.04.3 LTS | Postgresql-13.12 | master |
10.124.50.11 | Ubuntu 20.04.3 LTS | Postgresql-13.12 | replica |
依赖安装
sudo sed -i 's/archive.ubuntu.com/mirrors.aliyun.com/g' /etc/apt/sources.list
sudo apt-get update
sudo apt-get install -y gcc make libreadline-dev zlib1g-dev
编译安装postgres
sudo tar -zxvf postgresql-13.12.tar.gz
sudo cd /data/postgresql-13.12/
sudo ./configure --prefix=/data/pgsql-13.12
sudo make -j `nproc` && make install
sudo make world && make install -world
sudo mkdir -p /data/pgsql-13.12/{logs,data}
sudo adduser postgres sudo
sudo chown -R postgres:postgres /data/pgsql-13.12
数据库配置
配置环境变量
su - postgres
cat >> .profile << EOF
##postgres
PGPORT=5432
PGDATA=/data/pgsql-13.12/data
export PGPORT PGDATA
export LANG=zh_CN.utf8
export PGHOME=/data/pgsql-13.12
LD_LIBRARY_PATH=\$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:\$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=\$PGHOME/bin:\$PATH
export MANPATH=\$PGHOME/share/man:\$MANPATH
export PGUSER=postgres
export PGHOST=127.0.0.1
export PGDATABASE=postgres
PGLOG="\$PGHOME/logs"
EOF
配置主库
数据初始化
sudo /data/pgsql-13.12/bin/initdb --encoding=utf8 -D /data/pgsql-13.12/data
修改客户端认证配置/data/pgsql-13.12/data/pg_hba.conf
host replication replica 10.124.50.0/24 md5
host all all 10.124.50.0/24 trust
修改主配置/data/pgsql-13.12/data/postgresql.conf
logging_collector = on# Enable capturing of stderr and csvlog # 这个参数为on时,pg数据库就开始记录日志了,但是默认为off
log_directory = 'pg_log'# directory where log files are written, # 该参数是配置日志的目录,可以是绝对目录,也可以是相对目录
log_filename = 'postgresql-%u.log'# log file name pattern, # 该参数是配置log的名字,%u表示星期几(取值1-7)
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 20GB
log_min_messages = warning
log_min_duration_statement = 3000 # -1 is disabled, 0 logs all statements # 这个参数非常重要,是排查慢查询的好工具,-1是关闭记录>这类日志
log_line_prefix = '%m %p %u %d %r %e'# special values: # 日志输出格式(%m,%p实际意义配置文件中有解释),可根据自己需要设置
listen_addresses = '*'
wal_level = replica
max_wal_senders= 4 #可以设置最多几个流复制链接,差不多有几个从,就设置多少
wal_keep_size = 1024
max_connections = 2048 #从库的 max_connections要大于主库
archive_mode = on #允许归档
archive_command = 'cp %p /data/pgsql-13.12/data/archive/%f' #根据实际情况设置
wal_sender_timeout = 120s
hot_standby = on
hot_standby_feedback = on
checkpoint_timeout = 30min
max_wal_size = 64GB
min_wal_size = 4GB
wal_compression = on #开启wal日志压缩,会有额外的cpu开销
设置systemd托管服务
cat EOF > /etc/systemd/system/pgsql.service < EOF
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/data/pgsql-13.12/data/
OOMScoreAdjust=-1000
ExecStart=/data/pgsql-13.12/bin/pg_ctl start -D \${PGDATA} -s -o "-p \${PGPORT}" -w -t 300
ExecStop=/data/pgsql-13.12/bin/pg_ctl stop -D \${PGDATA} -s -m fast
ExecReload=/data/pgsql-13.12/bin/pg_ctl reload -D \${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl start pgsql.service
systemctl enable pgsql.service
配置主从同步
创建主从账号
su - postgres
psql
postgres=# CREATE ROLE replica login replication encrypted password 'replica';
postgres=# \q;
从库操作
pg_basebackup -U replica -h 10.124.50.10 -p 5432 -F p -X s -v -P -R -D /data/pgsql-13.12/data/
主库查看主从状态
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
各节点查看数据同步情况
pg_controldata