ansible-playpook搭建Postgresql主从复制

准备

[root@m8-9p52-duanzhengnan templates]# cat /etc/ansible/roles/postgresql_ha/vars/main.yml 

postgresql_version: postgresql-9.6.3.tar.gz
#解压目录地址
dir: /opt
#解压目录名
postgresql_dir: /postgresql-9.6.3
#二进制编译目录
data_dir: /opt/pgsql
#数据目录
datadir: /opt/pgsql/data
#归档的文件夹,根据配置定义
file_path: /opt/pgsql/archive
#主从用户
postgres_user: replica
#主从用户密码
postgres_password: 123456

[root@m8-9p52-duanzhengnan templates]# cat /etc/ansible/roles/postgresql_ha/tasks/main.yml 

- name: "解压软件包"
  unarchive: src=/opt/software_package/dataflow{{postgresql_version}} dest={{dir}}

- name: create user
  shell: id postgres || useradd postgres && echo '123456' | passwd --stdin postgres

- name: "安装依赖包"
  yum: name="{{item}}" state=installed
  with_items:
      - gcc
      - gcc-c++
      - readline-devel
      - zlib-devel

- name: "编译源码，安装"
  shell: cd {{dir}}{{postgresql_dir}} && ./configure --prefix={{data_dir}} && make && make install

- name: "配置环境变量"
  shell: |
         echo -e "export POSTGRESQL_HOME={{data_dir}}\nexport PATH={{data_dir}}/bin:\$PATH\nexport PGDATA={{data_dir}}/data">/etc/profile.d/postgresql.sh
         source /etc/profile.d/postgresql.sh 
         source /etc/profile

- name: "修改服务属主，属组"
  shell: chown postgres:postgres -R /opt/pgsql/

- name: "初始化数据库"
  shell: ls {{datadir}} || su - postgres -c "initdb  -E utf8 -D {{datadir}}"

- name: config
  template: src="templates/{{item.key}}" dest="{{item.value}}" owner=postgres group=postgres mode=0644
  with_dict:
    - {"pg_hba.conf":"{{datadir}}/pg_hba.conf"}
    - {"postgresql_master.conf":"{{datadir}}/postgresql.conf"}
  when: inventory_hostname == groups.postgresql_ha[0]

- name: "在数据库安装目录下创建一个存放归档的文件夹"
  file:  path={{file_path}} state=directory owner=postgres group=postgres mode=0755 recurse=yes
  when: inventory_hostname == groups.postgresql_ha[0]

- name: "启动postgresql"
  shell: ss -anpt|grep 5432 && su - postgres -c "pg_ctl -D {{datadir}} -l {{datadir}}/logfile restart" || su - postgres -c "pg_ctl -D {{datadir}} -l {{datadir}}/logfile start"
  when: inventory_hostname == groups.postgresql_ha[0]

- name: "推送sql语句到目标主机"
  template: src="create_master.sql" dest="/tmp/" owner=postgres group=postgres mode=0644
  when: inventory_hostname == groups.postgresql_ha[0]      
 
- name: "远程执行推送过去的sql语句"
  shell: su - postgres -c "psql -f /tmp/create_master.sql"
  when: inventory_hostname == groups.postgresql_ha[0]

- name: "保证基础数据的一致，从主数据库将起data目录下的数据复制到从数据库的data目录下"
  shell: |
        rm -rf /opt/pgsql/data/* 
        su - postgres -c "pg_basebackup -h {{groups.postgresql_ha[0]}} -U {{postgres_user}} -D {{datadir}} -X stream"
  when: inventory_hostname == groups.postgresql_ha[1]

- name: config
  template: src="templates/{{item.key}}" dest="{{item.value}}" owner=postgres group=postgres mode=0644
  with_dict:
    - {"recovery.conf":"{{datadir}}/recovery.conf"}
    - {"postgresql_slave.conf":"{{datadir}}/postgresql.conf"}
  when: inventory_hostname == groups.postgresql_ha[1]

- name: "启动postgresql"
  shell: ss -anpt|grep 5432 && su - postgres -c "pg_ctl -D {{datadir}} -l {{datadir}}/logfile restart" || su - postgres -c "pg_ctl -D {{datadir}} -l {{datadir}}/logfile start"
  when: inventory_hostname == groups.postgresql_ha[1]

[root@m8-9p52-duanzhengnan templates]# cat /etc/ansible/roles/postgresql_ha/templates/create_master.sql 

CREATE ROLE  {{postgres_user}} login replication encrypted password '{{postgres_password}}';

[root@m8-9p52-duanzhengnan templates]# cat /etc/ansible/roles/postgresql_ha/templates/pg_hba.conf 

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host     all             all          172.18.9.0/24          md5   #允许从服务器连接到主服务器,md5加密
host   replication      replica       172.18.9.0/24          trust   #允许从服务器使用replica用户来复制,trust表示pg_basebackup同步配置文件时不用密码

[root@m8-9p52-duanzhengnan templates]# cat /etc/ansible/roles/postgresql_ha/templates/postgresql_master.conf 

listen_addresses = '*'   # 监听所有IP
archive_mode = on  # 允许归档
archive_command = 'cp %p /home/postgres/pgsql/archive/%f'  # 用该命令来归档logfile segment
wal_level = hot_standby 
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个wal_keep_segments = 256  #设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的

[root@m8-9p52-duanzhengnan postgresql_ha]# cat /etc/ansible/roles/postgresql_ha/templates/postgresql_slave.conf 

wal_level = hot_standby
max_connections = 1000 # 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on # 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

[root@m8-9p52-duanzhengnan templates]# cat /etc/ansible/roles/postgresql_ha/templates/recovery.conf 

standby_mode = on    # 说明该节点是从服务器
primary_conninfo = 'host={{groups.postgresql_ha[0]}} port=5432 user={{postgres_user}} password={{postgres_password}}'  # 主数据库的信息以及连接的用户
recovery_target_timeline = 'latest'

2、执行脚本

 ansible-playbook /etc/ansible/playbook/postgresql_ha.yml

3、测试结果

su - postgres -c "psql"

select client_addr,sync_state from pg_stat_replication;

3、通过查看两个服务器的进程来判断，分别在两个服务器上执行命令ps -ef | grep postgres,在主服务器上我们可以看到一个walsender的进程；在从服务器上我们可以看到一个walreceiver的进程，这样就证明主从配置完成。