复制方式
物理流复制
- 流复制只能对PG实例级进行复制
- 物理复制的核心原理是主库将预写日志WAL日志流发送给备库，备库接收到WAL日志流后进行重做
- 流复制能对DDL操作进行复制
- 流复制主库可读写，但从库只允许查询操作不允许写入
- 流复制要求PG大版本必须一致
逻辑复制
- 可基于表级别复制，是一种粒度可细的复制
- 逻辑主备角色分为发布者(Publication)和订阅者(Subscription)
- 逻辑主库和逻辑备库为不同的PostgreSQL实例，可以在同一主机上，也可以在不同主机上。
流复制环境搭建
主备规划
磁盘规划，及创建用户组
# 创建postgres用户
useradd -rmU postgres
# 修改postgres用户密码
passwd postgres
# 以明文方式修改postgres用户的密码
echo "postgres"|passwd postgres --stdin
# 进入postgres用户目录
su - postgres
创建数据库目录
su - root
mkdir -p /pgdata/pg14
mkdir -p /pgdata/pgwal/archive_wals
mkdir -p /pg/{pghome,patroni}
chown -R postgres:postgres /{pg,pgdata}
# chown -R postgres:postgres /pgdata/pgwal
chmod -R 700 /{pg,pgdata}
配置环境变量
su - postgres
vi .bashrc
export PGHOME=/pg/pghome
export PGDATA=/pgdata/pg14
export PGPORT=5432
export PGDATABASE=postgres
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
source .bashrc
二进制安装
官网介绍的redhat系统yum源安装方式：
https://www.postgresql.org/download/linux/redhat/
# 检查yum源是否有PG
yum list | grep postgres
# 使用yum安装系统自带的PG
yum install postgresql-server.x86_64
# 官网介绍的指定PG版本的安装，需要能连接网络或先下载相关rpm包
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y --downloadonly --downloaddir=/root/software postgresql14-server
yum reinstall -y --downloadonly --downloaddir=/root/software postgresql14-server
yum install -y postgresql14-server
源码安装
# yum 依赖包
yum install -y openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel python-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel gettext gettext-devel bison flex uuid-devel gcc gcc-c++
# 解压源码包
tar zxvf postgresql-14.3.tar.gz
# 进入解压的源码包
cd postgresql-14.3
# 预安装
# ./configure --prefix=/pg/pghome --with-pgport=5432
./configure --prefix=/pg/pghome --with-perl --with-python --with-openssl --with-pam -without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=16 --enable-dtrace -enable-debug
# 编译
gmake world
# 安装包括插件
gmake install-world
# 下面可以省略
# 编译安装PG自带的插件
cd contrib
make && make install
主库初始化
su - postgres
initdb -D /pgdata/pg14 -E UTF8 --locale=zh_CN.UTF-8
主备流复制搭建
主库参数配置
vi $PGDATA/postgresql.conf
listen_addresses = '0.0.0.0'
port = '5432'
max_connections = '1024'
wal_buffers=32MB
work_mem=4MB
superuser_reserved_connections = 10
max_locks_per_transaction = '64'
max_prepared_transactions = '0'
max_replication_slots = '10'
max_wal_senders = '50'
max_worker_processes = '8'
track_commit_timestamp = 'off'
logging_collector = on
log_destination=csvlog
log_filename = 'postgresql-%a.log'
log_connections = on
log_disconnections = on
log_checkpoints = on
log_lock_waits = on
log_statement = ddl
log_truncate_on_rotation = on
log_rotation_age = 1440
wal_level = 'replica'
wal_log_hints = 'on'
archive_mode = 'on'
archive_command = 'cp %p /pgdata/pgwal/archive_wals/%f'
archive_timeout = '1800s'
cluster_name = 'PGCluster'
hot_standby = 'on'
synchronous_commit = remote_write
hba_file = '/pgdata/pg14/pg_hba.conf'
ident_file = '/pgdata/pg14/pg_ident.conf'
restore_command = 'cp /pgdata/pgwal/archive_wals/%f %p'
recovery_target_timeline = 'latest'
主库认证参数配置
vi $PGDATA/pg_hba.conf
host replication replicator 192.168.75.91/32 md5
host replication replicator 192.168.75.92/32 md5
host replication replicator 192.168.75.93/32 md5
host all all 0.0.0.0/0 md5
启动主库，并创建流复制用户
pg_ctl -D /pgdata/pg14 -l /pgdata/pg14/postgresql.log start
## 主库创建 replicator 流复制用户用于后面创建同步备库使用
psql -U postgres -h /pgdata/pg14/ -c "CREATE USER replicator REPLICATION ENCRYPTED PASSWORD 'replicator';"
备库配置
## 在备库上使用 pg_basebackup 从主库同步数据
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 192.168.75.91 -p 5432 -U replicator
## 12版本之后将参数都放到了postgresql.conf文件中并编辑 standby.signal 文件（12之前参数都配置在recovery.conf文件中）
vi postgresql.conf
primary_conninfo = 'application_name=pg2 host=192.168.75.91 port=5432 user=replicator password=replicator'
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
## 编辑 standby.signal 文件
vi standby.signal
standby_mode = on ## async：表示备库为异步同步方式。potential：表示备库当前为异步同步方式，如果当前的同步备库宕机后，异步备库可升级成为同步备库。sync：当前备库为同步方式。quorum：表示备库为quorum standbys的候选
## 启动备库
pg_ctl -D /pgdata/pg14 -l /pgdata/pg14/postgresql.log start
主库查看同步状态
postgres=# \x
postgres=# select *from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 4674
usesysid | 16384
usename | replicator
application_name | pg2
client_addr | 192.168.75.92
client_hostname |
client_port | 33402
backend_start | 2022-06-10 18:44:18.111761+08
backend_xmin | 734
state | streaming
sent_lsn | 0/4000148
write_lsn | 0/4000148
flush_lsn | 0/4000148
replay_lsn | 0/4000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-06-10 18:45:38.830602+08
postgres=#
