PgSQL - 读写分离

• 演示环境介绍

   OS：   'Centos-7'
   PGSQLVERSION：  '11'
   MASTER：'192.168.188.185'
   SLAVE：'192.168.188.184'

• 安装PgSQL

    root@localhost> 'yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm'
    
    root@localhost> 'yum install postgresql11'
    
    root@localhost> 'yum install postgresql11-server'
    
    root@localhost> '/usr/pgsql-11/bin/postgresql-11-setup initdb' # slave可以不用初始化
    root@localhost> 'systemctl enable postgresql-11'
    root@localhost> 'systemctl start postgresql-11'
    

• 配置环境

    -   '主库'
        # 创建从服务器连接账户
        postgres=# 'create role 账号 replication login encrypted password '密码';'
        
        # 修改主库配置文件 (/var/lib/pgsql/11/data/postgresql.conf)
        listen_addresses = '*'
        max_connections = 1024
        password_encryption = on
        wal_level = logical 
        archive_mode = on
        max_wal_sender = 10
        wal_keep_segments = 10
        
        # 修改连接方式 (/var/lib/pgsql/11/data/pg_hba.conf)
        host    replication     账号       0.0.0.0/0               md5
        
        # 重启pgsql
        root@localhost> 'systemctl restart postgresql-11'

    -   '从库'
        
        # 停止pgsql
        root@localhost> 'systemctl stop postgresql-11'
        
        # 切换用户
        root@localhost> 'su - postgres'
        
        # 清空配置 (/var/lib/pgsql/11/data)
        -bash-4.2$ 'rm -rf ./*' # 如果在从库没有初始化这一步可以跳过
        
        # 同步配置文件
        -bash-4.2$ '/usr/pgsql-11/bin/pg_basebackup -D $PGDATA -Fp -Xstream -R -c fast -v -P -h 主库IP -U 账号 -W'
        
        # 修改配置文件 (/var/lib/pgsql/11/data/postgresql.conf)
        hot_standby = on
        
        # 启动pgsql
        root@localhost> 'systemctl start postgresql-11'