Patroni、Haproxy、Keepalivedに基づいた高信頼性のPostgreSQLクラスター

 --- - name: Install essential software yum: name={{ item }} state=latest tags: software with_items: - ntpdate - bzip2 - zip - unzip - openssl-devel - mc - vim - atop - wget - mytop - screen - net-tools - rsync - psmisc - gdb - subversion - htop - bind-utils - sysstat - nano - iptraf - nethogs - ngrep - tcpdump - lm_sensors - mtr - s3cmd - psmisc - gcc - git - python2-pip - python-devel - name: install the 'Development tools' package group yum: name: "@Development tools" state: present 

 --- - name: Install open VM tools for VMWARE yum: name={{ item }} state=latest tags: open-vm-tools with_items: - open-vm-tools - name: VmWare service start and enabling service: name=vmtoolsd.service state=started enabled=yes tags: open-vm-tools 

 --- - name: setting default timezone set_fact: timezone: name=Europe/Moscow when: timezone is not defined - name: setting TZ timezone: name={{ timezone }} when: timezone is defined tags: - tz - tweaks - ntp - ntpd - name: Configurating cron for ntpdate cron: name="ntpdate" minute="*/5" job="/usr/sbin/ntpdate pool.ntp.org" tags: - tz - tweaks - ntp - ntpd - name: ntpd stop and disable service: name=ntpd state=stopped enabled=no tags: - tz - tweaks - ntp - ntpd ignore_errors: yes - name: crond restart and enabled service: name=crond state=restarted enabled=yes tags: - tz - tweaks - ntp - ntpd 

 --- - name: set zabbix ip external set_fact: zabbix_ip: 132.xx.xx.98 tags: zabbix - name: set zabbix ip internal set_fact: zabbix_ip: 192.168.xx.98 when: ansible_all_ipv4_addresses | ipaddr('192.168.0.0/16') tags: zabbix - name: Import Zabbix3 repo yum: name=http://repo.zabbix.com/zabbix/3.0/rhel/7/x86_64/zabbix-release-3.0-1.el7.noarch.rpm state=present tags: zabbix - name: Remove old zabbix yum: name=zabbix2* state=absent tags: zabbix - name: Install zabbix-agent software yum: name={{ item }} state=latest tags: zabbix with_items: - zabbix-agent - zabbix-release - name: Creates directories file: path={{ item }} state=directory tags: - zabbix - zabbix-mysql with_items: - /etc/zabbix/externalscripts - /etc/zabbix/zabbix_agentd.d - /var/lib/zabbix - name: Copy scripts copy: src=/etc/ansible/templates/zabbix/{{ item }} dest=/etc/zabbix/externalscripts/{{ item }} owner=zabbix group=zabbix mode=0755 tags: zabbix with_items: - netstat.sh - iostat.sh - iostat2.sh - iostat_collect.sh - iostat_parse.sh - php_workers_discovery.sh - name: Copy .my.cnf copy: src=/etc/ansible/files/mysql/.my.cnf dest=/var/lib/zabbix/.my.cnf owner=zabbix group=zabbix mode=0700 tags: - zabbix - zabbix-mysql - name: remove default configs file: path={{ item }} state=absent tags: zabbix with_items: - /etc/zabbix_agentd.conf - /etc/zabbix/zabbix_agentd.conf - name: put zabbix-agentd.conf to default place template: src=/etc/ansible/templates/zabbix/zabbix_agentd.tpl dest=/etc/zabbix_agentd.conf owner=zabbix group=zabbix force=yes tags: zabbix - name: link zabbix-agentd.conf to /etc/zabbix file: src=/etc/zabbix_agentd.conf dest=/etc/zabbix/zabbix_agentd.conf state=link tags: zabbix - name: zabbix-agent start and enable service: name=zabbix-agent state=restarted enabled=yes tags: zabbix 

 --- - hosts: pgsql pre_tasks: - name: Setting system hostname hostname: name="{{ ansible_host }}" - include: tasks/essentialsoftware.yml - include: tasks/open-vm-tools.yml - include: tasks/ntpd.yml post_tasks: - include: tasks/zabbix.yml roles: - ssh.role - ansible-role-patroni 

 - include: postgres.yml - include: haproxy.yml - include: keepalived.yml 

 --- - name: Import Postgresql96 repo yum: name=https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm state=present tags: patroni when: install is defined - name: Install PGsql96 yum: name={{ item }} state=latest tags: patroni with_items: - postgresql96 - postgresql96-contrib - postgresql96-server - python-psycopg2 - repmgr96 when: install is defined - name: checkout patroni git: repo=https://github.com/zalando/patroni.git dest=/opt/patroni tags: patroni when: install is defined - name: create /etc/patroni file: state=directory dest=/etc/patroni tags: patroni when: install is defined - name: put postgres.yml template: src=postgres0.yml dest=/etc/patroni/postgres.yml backup=yes tags: patroni when: install is defined - name: install python packages pip: name={{ item }} tags: patroni with_items: - python-etcd - python-consul - dnspython - boto - mock - requests - six - kazoo - click - tzlocal - prettytable - PyYAML when: install is defined - name: put patroni.service systemd unit template: src=patroni.service dest=/etc/systemd/system/patroni.service backup=yes tags: patroni when: install is defined - name: Reload daemon definitions command: /usr/bin/systemctl daemon-reload tags: patroni - name: restart service: name=patroni state=restarted enabled=yes tags: patroni 

 name: {{ patroni_node_name }} scope: &scope {{ patroni_scope }} consul: host: consul.services.local:8500 restapi: listen: 0.0.0.0:8008 connect_address: {{ ansible_default_ipv4.address }}:8008 auth: 'username:{{ patroni_rest_password }}' bootstrap: dcs: ttl: &ttl 30 loop_wait: &loop_wait 10 maximum_lag_on_failover: 1048576 # 1 megabyte in bytes postgresql: use_pg_rewind: true use_slots: true parameters: archive_mode: "on" wal_level: hot_standby archive_command: mkdir -p ../wal_archive && cp %p ../wal_archive/%f max_wal_senders: 10 wal_keep_segments: 8 archive_timeout: 1800s max_replication_slots: 5 hot_standby: "on" wal_log_hints: "on" pg_hba: # Add following lines to pg_hba.conf after running 'initdb' - host replication replicator 192.168.0.0/16 md5 - host all all 0.0.0.0/0 md5 postgresql: listen: 0.0.0.0:5432 connect_address: {{ ansible_default_ipv4.address }}:5432 data_dir: /var/lib/pgsql/9.6/data pg_rewind: username: superuser password: {{ patroni_postgres_password }} pg_hba: - host all all 0.0.0.0/0 md5 - hostssl all all 0.0.0.0/0 md5 replication: username: replicator password: {{ patroni_replicator_password }} network: 192.168.0.0/16 superuser: username: superuser password: {{ patroni_postgres_password }} admin: username: admin password: {{ patroni_postgres_password }} restore: /opt/patroni/patroni/scripts/restore.py 

 [pgsql] cluster-pgsql-01.local cluster-pgsql-02.local cluster-pgsql-03.local [pgsql:vars] patroni_scope: "cluster-pgsql" patroni_rest_password: flsdjkfasdjhfsd patroni_postgres_password: flsdjkfasdjhfsd patroni_replicator_password: flsdjkfasdjhfsd cluster_virtual_ip: 192.xx.xx.125 </spoiler>      -   host_vars/_: <spoiler title="/etc/ansible/host_vars/pgsql-cluster-01.local/main.yml"> <source lang="yaml"> patroni_node_name: cluster_pgsql_01 keepalived_priority: 99 

 --- - name: Install haproxy yum: name={{ item }} state=latest tags: - patroni - haproxy with_items: - haproxy when: install is defined - name: put config template: src=haproxy.cfg.j2 dest=/etc/haproxy/haproxy.cfg backup=yes tags: - patroni - haproxy - name: restart and enable service: name=haproxy state=restarted enabled=yes tags: - patroni - haproxy 

 global maxconn 800 defaults log global mode tcp retries 2 timeout client 30m timeout connect 4s timeout server 30m timeout check 5s frontend ft_postgresql bind *:5000 default_backend postgres-patroni backend postgres-patroni option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 2 server {{ patroni_node_name }} {{ patroni_node_name }}.local:5432 maxconn 300 check port 8008 server {{ patroni_node_name }} {{ patroni_node_name }}.local:5432 maxconn 300 check port 8008 server {{ patroni_node_name }} {{ patroni_node_name }}.local:5432 maxconn 300 check port 8008 

 --- - name: Install keepalived yum: name={{ item }} state=latest tags: - patroni - keepalived with_items: - keepalived when: install is defined - name: put alert script template: src=alert.sh.j2 dest=/usr/local/sbin/alert.sh backup=yes mode=755 tags: - patroni - keepalived when: install is defined - name: put config template: src=keepalived.conf.j2 dest=/etc/keepalived/keepalived.conf backup=yes tags: - patroni - keepalived - name: restart and enable service: name=keepalived state=restarted enabled=yes tags: - patroni - keepalived 

 global_defs { router_id {{ patroni_node_name }} } vrrp_script chk_haproxy { script "killall -0 haproxy" interval 1 weight -20 debug fall 2 rise 2 } vrrp_instance {{ patroni_node_name }} { interface ens160 state BACKUP virtual_router_id 150 priority {{ keepalived_priority }} authentication { auth_type PASS auth_pass secret_for_vrrp_auth } track_script { chk_haproxy weight 20 } virtual_ipaddress { {{ cluster_virtual_ip }}/32 dev ens160 } notify_master "/usr/bin/sh /usr/local/sbin/alert.sh '{{ patroni_node_name }} became MASTER'" notify_backup "/usr/bin/sh /usr/local/sbin/alert.sh '{{ patroni_node_name }} became BACKUP'" notify_fault "/usr/bin/sh /usr/local/sbin/alert.sh '{{ patroni_node_name }} became FAULT'" }