PostgreSQL流复制搭建

 

PostgreSQL流复制

postgresql物理复制也叫流复制，通过将主节点上的是redo日志（wal日志）传送到从节点来实现数据库的同步的，参考下图（侵删），正如逻辑复制里提到的

1，物理复制数据传递的原理：传递的wal也即redo日志，从节点上通过对物理日志进行redo的方式来实现数据的还原，想对重新执行sql的逻辑日志来说效率会更高。

2，物理复制数据传递的范围：物理复制是实例级的，因此没有所谓的逻辑复制的发布（publication）~订阅（subscribution）对象，整个搭建过程相对简单不少。

 

主节点配置

主节点配置

###主节点
###复制同步模式
    synchronous_commit = on    # synchronization level; off, local, remote_write, remote_apply, or on

###主节点创建复制用户
create user replica with replication login password 'A-Strong-Password';

###主节点pg_hba连接配置
    #增加复制用户以及从库IP配置：
    host replication replica 192.168.1.2/32(从库ip) md5

###重启主节点
    systemctl restart postgresql9100

###主节点复制槽名称确认（如果存在删除）
    #原从节点上创建slot
　　SELECT * FROM pg_create_physical_replication_slot('pgstandby_slave01');
　    #查看复制槽信息
　　SELECT * FROM pg_replication_slots;
　　#（自动创建复制槽模式）请确保在删除任何复制槽之前，该槽不再被任何复制消费者使用。
　　select pg_drop_replication_slot('pgstandby_slave01');

 

主节点复制状态监控

    select * from pg_stat_replication ;
    
    id 一个 WAL                  发送进程的进程 ID
    usesysid                    登录到这个 WAL 发送进程的用户的 OID
    usename                     登录到这个 WAL 发送进程的用户的名称
    application_name            连接到这个 WAL 发送进程的应用的名称
    client_addr                 连接到这个 WAL 发送进程的客户端的 IP 地址。 如果这个域为空，它表示该客户端通过服务器机器上的一个Unix 套接字连接。
    client_hostname             连接上的客户端的主机名，由一次对client_addr的逆向 DNS 查找报告。 这个域将只对 IP 连接非空，并且只有在 log_hostname被启用时非空。
    client_port                 客户端用来与这个 WAL 发送进程通讯的 TCP 端口号，如果使用 Unix 套接字则为-1
    backend_start               这个进程开始的时间，即客户端是何时连接到这个WAL 发送进程的。
    backend_xmin                由hot_standby_feedback报告的这个后备机的xmin水平线。
    state                       当前的 WAL 发送进程状态。 可能的值是：
                                　　startup: 这个WAL发送器正在启动。
                                　　catchup: 这个WAL发送者连接的备用服务器正在赶上主服务器。
                                　　streaming: 在其连接的备用服务器赶上主服务器之后，这个WAL发送方正在流化变化。
                                　　backup: 这个WAL发送器正在发送一个备份。
                                　　stopping: 这个WAL发送器正在停止。
                                
    sent_lsn                    在这个连接上发送的最后一个预写式日志的位置
    write_lsn                   被这个后备服务器写入到磁盘的最后一个预写式日志的位置
    flush_lsn                   被这个后备服务器刷入到磁盘的最后一个预写式日志的位置
    replay_lsn                  被重放到这个后备服务器上的数据库中的最后一个预写式日志的位置
    write_lag                   从本地刷新近期的WAL与接收到此备用服务器已写入WAL的通知(但尚未刷新或应用它)之间的时间经过。 如果将此服务器配置为同步备用服务器，则可以使用此参数来衡量在提交时synchronous_commit级别remote_write所导致的延迟。
    flush_lag                   在本地刷写近期的WAL与接收到后备服务器已经写入并且刷写它（但还没有应用）的通知之间流逝的时间。 如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别on所导致的延迟。
    replay_lag                  在本地刷写近期的WAL与接收到后备服务器已经写入它、刷写它并且应用它的通知之间流逝的时间。 如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别remote_apply所导致的延迟。
    sync_priority               在基于优先的同步复制中，这台后备服务器被选为同步后备的优先级。在基于规定数量的同步复制中，这个值没有效果。
    sync_state                  这一台后备服务器的同步状态。 可能的值是：
                                　　async:         这台后备服务器是异步的。
                                　　potential:     这台后备服务器现在是异步的，但可能在当前的同步后备失效时变成同步的。
                                　　sync:          这台后备服务器是同步的。
                                　　quorum:     　　这台后备服务器被当做规定数量后备服务器的候选。           
    reply_time                     带时区的时间戳，从备用服务器收到的最后一条回复信息的发送时间。
    

 

从节点配置

从节点配置

从节点通过pg_basebackup将主节点的数据备份到当前实例下，在启动后基于pgbasebackup备份时生成的postgresql.auto.conf文件，启动时会自动接入主节点进行复制。这一点其实与MySQL的克隆插件如出一辙（clone instance from clone_user@***.***.***.***:port identified by "password";）,都是一个实例级的备份，都会保留备份/克隆完成时的主节点位点信息，以致于在启动复制时无需关注位点值，只需要启动复制即可。

###从节点
    ###关闭从节点实例服务

###备份从节点实例的数据文件
　　mv data data_bak
　　mkdir data
　　
###备份远程（主节点）数据到本地
　　/usr/local/pgsql/bin/pg_basebackup -h 1.1.1.1 -p 8888 -U replica --password -R -P -v -C --slot=pgstandby_slave01 -D /usr/local/pgsql/pg_instance_9000/data/
　　-R 说明会创建standby.signal文件，以及补充postgresql.auto.conf的内容
　　-P 显示备份进度
　　-v 显示更加详细信息
　　-C 同时创建复制槽
　　--slot 指定复制槽的名字（一个备库一个名字）
　　-D 生成备库的路径
　　-z, --gzip 压缩 tar 输出
   -Z, --compress=0-9 使用给定的压缩级别压缩 tar 输出

     # -Ft p|t，t输出tar包格式
     # -z 压缩
     # -Z5 压缩比
     /usr/local/pgsql16/bin/pg_basebackup -h 1.1.1.1 -p 8888 -U replica_user --password -R -P -v -Ft -z -Z5 --slot=pgstandby_slave01 -D /usr/local/pgsql16/pg_instance_9000/data/

　　　tar -zxvf pg_wal.tar.gz
　　　tar -zxvf base.tar.gz

###备份后会自动在数据目录下创建两个文件
　　postgresql.auto.conf和standby.signal
　　postgresql.auto.conf的内容如下，其实就是用pg_basebackup备份时的账号密码，自动生成启动流复制之后连接到主节点的配置信息
  # Do not edit this file manually!
  # It will be overwritten by the ALTER SYSTEM command.
  primary_conninfo = 'user=stream_repl_user password=''******'' channel_binding=disable host=1.1.1.1 port=9300 sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
  primary_slot_name = 'pgstandby_slave01'
  

###备份完成后重新授权文件路径权限给postgres用户
　　#1，因为pg_basebackup备份到从节点当前实例的另外一个路径，备份中包含了配置文件信息，当前配置文件中的信息还是主节点的，因此相关数据，日志，归档文件的路径要修改
　　#2，postgres用户并没有pg_basebackup执行备份备份生成的文件的权限，因此需要授权
　　chown postgres.postgres -R /usr/local/pgsql/pg_instance_9000/
　　chmod 700 -R /usr/local/pgsql/pg_instance_9000/

###从节点启动实例
    然后用systemctl重启从节点，复制自动开启
    

从节点操作一镜到底

从节点复制状态监控

###主节点上查看
    select * from pg_stat_replication;
    pid                    一个 WAL 发送进程的进程 ID
    usesysid            　　登录到这个 WAL 发送进程的用户的 OID
    usename                登录到这个 WAL 发送进程的用户的名称
    application_name    　　连接到这个 WAL 发送进程的应用的名称
    client_addr         　　连接到这个 WAL 发送进程的客户端的 IP 地址。 如果这个域为空，它表示该客户端通过服务器机器上的一个 Unix 套接字连接。
    client_hostname        连接上的客户端的主机名，由一次对client_addr 的逆向 DNS 查找报告。这个域将只对 IP 连接非空，并且只有在 log_hostname被启用时非空
    client_port            客户端用来与这个 WAL 发送进程通讯的 TCP 端口号， 如果使用 Unix 套接字则为-1
    backend_start        　这个进程开始的时间，即客户端是何时连接到这个 WAL 发送进程的
    backend_xmin        　 由hot_standby_feedback报告 的这个后备机的xmin水平线。
    state                　当前的 WAL 发送进程状态。 可能的值是：
                            startup：这个WAL发送器正在启动。
                            catchup：这个WAL发送器连接的后备机正在追赶主服务器。
                            streaming：这个WAL发送器在它连接的后备服务器追上主服务器之后用流传送更改。
                            backup：这个WAL发送器正在发送一个备份。
                            stopping：这个WAL发送器正在停止。
    sent_lsn             在这个连接上发送的最后一个预写式日志的位置
    write_lsn            被这个后备服务器写入到磁盘的最后一个预写式日志的位置
    flush_lsn            被这个后备服务器刷入到磁盘的最后一个预写式日志的位置
    replay_lsn           被重放到这个后备服务器上的数据库中的最后一个预写式日志的位置
    write_lag            在本地刷写近期的WAL与接收到后备服务器已经写入它（但还没有刷写或者应用）的通知之间流逝的时间。如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别remote_write所导致的延迟。
    flush_lag            在本地刷写近期的WAL与接收到后备服务器已经写入并且刷写它（但还没有应用）的通知之间流逝的时间。如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别on所导致的延迟。
    replay_lag           在本地刷写近期的WAL与接收到后备服务器已经写入它、刷写它并且应用它的通知之间流逝的时间。如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别remote_apply所导致的延迟。
    sync_priority        在基于优先的同步复制中，这台后备服务器被选为同步后备的优先级。在基于规定数量的同步复制中，这个值没有效果。
    sync_state           这一台后备服务器的同步状态。 可能的值是：
                            async：这台后备服务器是异步的。
                            potential：这台后备服务器现在是异步的，但可能在当前的同步后备失效时变成同步的。
                            sync：这台后备服务器是同步的。
                            quorum：这台后备服务器被当做规定数量后备服务器的候选。
    reply_time           从备用服务器收到的最后一条回复信息的发送时间

    
###从节点上查看
    select pg_is_wal_replay_paused()        #如果恢复被暂停，为真。
    select pg_wal_replay_pause()            #立即暂停恢复（仅限于超级用户）。
    select pg_wal_replay_resume()            #如果恢复被暂停，重启之（仅限于超级用户）。
　　 select pg_last_xact_replay_timestamp();
    
    select * from pg_stat_wal_receiver;
    　　pid             　　　　　　WAL 接收器进程的进程 ID
    　　status                    WAL 接收器进程的活动状态
    　　receive_start_lsn         WAL 接收器启动时使用的第一个预写日志位置
    　　receive_start_tli         WAL 接收器启动时使用的第一个时间线编号
    　　received_lsn              已经接收到并且已经被刷入磁盘的最后一个预写日志的位置，这个域的初始值是 WAL 接收器启动时使用的第一个日志位置
    　　received_tli              已经接收到并且已经被刷入磁盘的最后一个预写日志的时间线编号，这个域的初始值是 WAL 接收器启动时使用的第一个日志所在的时间线编号
    　　last_msg_send_time        从源头 WAL 发送器接收到的最后一个消息的发送时间
    　　last_msg_receipt_time     从源头 WAL 发送器接收到的最后一个消息的接收时间
    　　latest_end_lsn            报告给源头 WAL 发送器的最后一个预写日志位置
   　　 latest_end_time           报告给源头 WAL 发送器最后一个预写日志位置的时间
    　　slot_name                 这个 WAL 接收器使用的复制槽的名称
    　　conninfo                  这个 WAL 接收器使用的连接串，安全相关的域会被隐去(不显示密码明文)。

 

主从切换

###主从切换

    ###停止主库，模拟故障
    systemctl stop postgresql16
    
    ###删除备库数据目录下的standby.signal文件
    rm standby.signal
    
    ###备份后删除备库数据目录下的postgresql.auto.conf文件
    mv postgresql.auto.conf bak_postgresql.auto.conf

    ###重启从库
    systemctl restart postgresql16
    
    此时从库提升为主库
    SELECT pg_is_in_recovery();

    ###或者执行执行promote命令后，无需手动移除standby.signal文件
    pg_ctl promote -D /usr/local/pgsql16/pg_instance_9300/data/

原主节点重新启动后作为从节点

    ###原主节点重新启动后作为从节点
        ###修改原从节点的pg_hba.conf，设置复制用户的IP为主节点
    
        ###原从节点上创建slot
        SELECT * FROM pg_create_physical_replication_slot('pgstandby_slave01');

        ###此时将原始从节点的postgresql.auto.conf文件复制到从节点，修改IP地址为原从节点
    
        ####创建standby.signal文件
            echo "standby_mode = on" > standby.signal

        #启动原主节点，此时原主节点作为从节点身份运行
            systemctl restart postgresql16
    
        ###此时原主节点作为从节点运行
        SELECT pg_is_in_recovery();
        select * from pg_stat_wal_receiver;