ansible 自动化部署PG主从同步

# cd /etc/ansible/roles/install/pg_ms
# cat vars/pg_ms_source_var.yml 
#pg版本
postgresql_version: postgresql-14.4.tar.gz
#解压目录地址
dir: /usr/local/
#解压目录名
postgresql_dir: /postgresql-14.4
#二进制编译目录
data_dir: /usr/local/pg144
#数据目录
PGDATA: '{{data_dir}}/data'
#归档的文件夹,根据配置定义
PGARCH: '{{data_dir}}/archive'

# cat tasks/pg_ms_source.yml 
- name: "解压软件包"
  unarchive: src=pgmedia/{{postgresql_version}} dest={{dir}}

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

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

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

- 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 {{ data_dir }}

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

- name: config
  template: src="pgms/{{item.key}}" dest="{{item.value}}" owner=postgres group=postgres mode=0644
  with_dict:
    - {"pg_hba.conf":"{{PGDATA}}/pg_hba.conf"}
    - {"postgresql.conf":"{{PGDATA}}/postgresql.conf"}
  ##when: inventory_hostname == groups.postgresql_ha[0]
  when: master is defined
  
- name: "在数据库安装目录下创建一个存放归档的文件夹"
  file:  path={{PGARCH}} state=directory owner=postgres group=postgres mode=0755 recurse=yes
  #when: inventory_hostname == groups.postgresql_ha[0]
  #when: master is defined
  
- name: "启动postgresql"
  shell: ss -anpt|grep 5432 && su - postgres -c "pg_ctl -D {{PGDATA}} -l {{PGDATA}}/logfile restart" || su - postgres -c "pg_ctl -D {{PGDATA}} -l {{PGDATA}}/logfile start"
  #when: inventory_hostname == groups.postgresql_ha[0]
  when: master is defined

- name: "拷贝自启动脚本"
  template: src="pgms/postgresql.service" dest=/usr/lib/systemd/system 

- name: "设置postgresql开机启动"
  shell: systemctl daemon-reload && systemctl enable postgresql
  
- name: "推送sql语句到目标主机"
  template: src=pgms/create_master.sql dest=/tmp/ owner=postgres group=postgres mode=0644
  #when: inventory_hostname == groups.postgresql_ha[0]      
  when: master is defined
  
- name: "远程执行推送过去的sql语句"
  shell: su - postgres -c "psql -f /tmp/create_master.sql"
  #when: inventory_hostname == groups.postgresql_ha[0]
  when: master is defined

- name: "保证基础数据的一致，从主数据库将data目录下的数据复制到从数据库的data目录下"
  shell: |
        rm -rf {{ PGDATA }}/*
        su - postgres -c "pg_basebackup -h {{ master_ip }} -p 5432 -U {{ rep_user }} -Fp -Xs -Pv -R -D {{ PGDATA }}"
  #when: inventory_hostname == groups.postgresql_ha[1]
  when: slave is defined
  
- name: config
  template: src="pgms/{{item.key}}" dest="{{item.value}}" owner=postgres group=postgres mode=0644
  with_dict:
    #- {"recovery.conf":"{{PGDATA}}/recovery.conf"} ##PG12后，不再需要这个文件
    - {"postgresql-slave.conf":"{{PGDATA}}/postgresql.conf"}
  #when: inventory_hostname == groups.postgresql_ha[1]
  when: slave is defined
  
- name: "启动postgresql"
  shell: ss -anpt|grep 5432 && su - postgres -c "pg_ctl -D {{PGDATA}} -l {{PGDATA}}/logfile restart" || su - postgres -c "pg_ctl -D {{PGDATA}} -l {{PGDATA}}/logfile start"
  #when: inventory_hostname == groups.postgresql_ha[1]
  when: slave is defined
  

  
# cat templates/pgms/create_master.sql 
CREATE ROLE  {{rep_user}} login replication encrypted password '{{rep_passwd}}';


# cat 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          {{ slave_ip }}/24             md5     #允许从服务器连接到主服务器,md5加密
host   replication      replica       {{ slave_ip }}/24             trust   #允许从服务器使用replica用户来复制,trust表示pg_basebackup同步配置文件时不用密码

# cat templates/postgresql_master.conf 

listen_addresses = '*'   # 监听所有IP
archive_mode = on  # 允许归档
archive_command = 'cp %p {{PGARCH}}/%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必须要大于主库的

# cat 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 # 如果有错误的数据复制，是否向主进行反馈