postgresql14数据库主从搭建

主从同步配置

• 主服务器：172.25.6.80
• 从服务器：172.25.6.81

1、先查看数据库版本是否一致

[appadmin@HGCAppP01 ~]$ psql -U postgres -h localhost -p 10001
Password for user postgres:
psql (14.8) <-------这里会输出具体版本号
Type "help" for help.
postgres=#

2、允许远程配置

pg_hba.conf和postgresql.conf修改这两个配置文件，配置成允许远程访问

修改pg_hba.conf配置文件

IPv4 local connections:在这行下面加入，表示允许所有ip访问且需要MD5加密的密码

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# 添加这一行
host    all             all             0.0.0.0/0               md5

postgresql.conf把下面这行配置成，表示允许访问
listen_addresses = ‘*’ # what IP address(es) to listen on;

3、主库配置

• 创建同步账号

  psql -U postgres -h localhost -p 10001
  CREATE ROLE repl login replication encrypted password '123456';
  

• 修改配置文件(pg_hba.conf)

  在该文件最后添加如下两行:
  host    replication     repl            0.0.0.0/0          md5
  host    all             repl            0.0.0.0/0          trust
  

• 修改配置文件(postgresql.conf)

  找到相应的参数进行如下配置修改
  wal_level = replica
  archive_mode = on
  # 下面路径改成自己的
  archive_command = 'cp %p /var/lib/pgsql/14/data/pg_archive/%f'
  
  max_wal_senders = 10
  # 这个配置12版本以前的才有
  wal_keep_segments = 10240
  # 12版本以后用这个配置代替
  wal_keep_size = 10240 (14版本用这个配置替换上面配置)
  wal_sender_timeout = 60s
  

• 创建归档日志目录

  mkdir -p /自己的data路径/pg_archive
  mkdir pg_archive
  # 修改权限
  chown postgres:postgres ./pg_archive/
  chmod 0700 ./pg_archive/
  

• 配置完成后，重启主库

4、从库配置

• 若从库的数据库已经在运行的话,事先将其停掉

• 准备data目录，从库安装完成后，不初始化，若已经初始化，删除其data目录，若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录（注意权限需要是postgres用户的）

• 从库同步主库的数据文件

  pg_basebackup -Fp --progress -D /app/pgsql/pgdata -h 172.25.6.80 -p 10001 -U repl --password
  # 坑：如果主库的data目录下有备份的文件或者所有者是root的文件，同步时会报权限不足
  

• 可以看到主库文件都同步过来了

  [postgres@HGCAppP02 pgdata]$ pg_basebackup -Fp --progress -D /app/pgsql/pgdata -h 172.25.6.80 -p 10001 -U repl --password
  Password:
  290617/290617 kB (100%), 1/1 tablespace
  [postgres@HGCAppP02 pgdata]$ ll
  total 340
  -rw-------. 1 postgres postgres    227 May 23 14:51 backup_label
  -rw-------. 1 postgres postgres 164836 May 23 14:51 backup_manifest
  drwx------. 5 postgres postgres   4096 May 23 14:51 base
  ......
  

• 从 12版本开始，不再有recovery.conf文件，recovery.conf配置的内容全部并入到postgesql.conf中

• 新增了两个文件recovery.signal和standby.signal，分别代表处于recovery模式还是standby模式，如果两个文件都配置了，优先为standby模式，配置语法很简单，直接新建touch recovery.signal或者touch standby.signal即可，standby_mode参数不再支持

• 修改配置文件(postgresql.conf)

  primary_conninfo = 'host=172.25.6.80 port=10001 user=repl password=123456' #这个说明这台机器对应主库的信息
  recovery_target_timeline = 'latest' #这个说明这个流复制同步到最新的数据
  # 还需修改如下内容项:
  max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大
  hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
  max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
  wal_receiver_status_interval = 1s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
  hot_standby_feedback = on #如果有错误的数据复制，是否向主进行反馈
  

• 创建模式文件

  cd /var/lib/pgsql/14/data
  touch standby.signal
  chown postgres:postgres ./standby.signal
  chmod 0700 ./standby.signal
  

• 启动从库

• 主库尝试修改或新增表数据，看是否同步到从库

• 进入到主服务器查看主从状态

  psql -U postgres -h localhost # 进入pg控制台
  # 执行sql，f代表主库 t代表备库
  select pg_is_in_recovery();
  # 执行下面这条sql
  SELECT pid,usename,application_name,client_addr,state,sync_state  FROM pg_stat_replication;
  # 输出，说明有一个从库ip是172.20.10.4
    pid  | usename | application_name | client_addr |   state   | sync_state
  -------+---------+------------------+-------------+-----------+------------
   28773 | repl    | walreceiver      | 172.20.10.4 | streaming | async
  (1 行记录)