Last active
March 14, 2020 22:16
-
-
Save internetuser2008/17bbcbe07a9e1701aec7b5b9c9d39189 to your computer and use it in GitHub Desktop.
custom Ansible Playbook to deploy PostgerSql or PostgreSql BDR + Pgbackrest + Pgbouncer
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Usage | |
| # host supply IP | |
| # -t(tags) run specific tasks ie. db=master, dbslave=slave1/2, promote=update slave to master, remove= remove host | |
| # ansible-playbook pg.yml -e "host=192.169.99.2 -t db ##Build Master | |
| # ansible-playbook pg.yml -e "host=192.168.99.3" -t dbslave ##Build Slave | |
| # ansible-playbook pg.yml -e "host=192.168.99.3" -t promote ##Promoe slave to master | |
| # ansible-playbook pg.yml -e "host=192.168.99.2" -t remove ##Remove node from cluster | |
| # ansible-playbook /var/lib/pgsql/pg.yml -e "host=192.168.99.4" -s -t db --user=user1 --ask-sudo-pass ##Ubuntu | |
| # Created Bimal Patel | |
| --- | |
| - hosts: "{{ host }}" | |
| gather_facts: yes | |
| vars: | |
| mIP: 192.169.99.2 ###Provide Master IP | |
| s1IP: 192.168.99.3 ###Provide Slave IP | |
| s2IP: 192.168.99.4 ###Provide Slave2 IP | |
| pg: postgresql | |
| pgmjv: 9 #### Postgers Major Version | |
| pgmnv: 6 #### Postgres Minor Version | |
| git_user: XXXX | |
| git_pass: XXXX | |
| pg_connection: "100" | |
| pgdata: /mount/pg/postgres/ ####Postgers install Data directory | |
| xlogs: /mount/logs/pg_xlog/ ####Postgers WAL directory | |
| pgbouncer_conn: "50" ####Pgbouncer support Not included yet | |
| gitcheckout: /usr/local/pg_monitoring/ | |
| tasks: | |
| ############## | |
| #Postgres Install | |
| ############### | |
| - name: Add repository epl | |
| tags: | |
| - db | |
| - dbslave | |
| - bdr | |
| - repo | |
| yum_repository: | |
| name: epel | |
| description: EPEL YUM repo | |
| baseurl: https://download.fedoraproject.org/pub/epel/$releasever/$basearch/ | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: Add repository postgres ####Edit below yum URL for OS & Version or use private artifactory is exists | |
| tags: | |
| - db | |
| - dbslave | |
| - bdr | |
| - repo | |
| yum_repository: | |
| 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 | |
| # name: postgres96 | |
| # description: Postgresql9.6 | |
| # gpgcheck: 0 | |
| # enabled: 1 | |
| # baseurl: https://artifactory.private.com/artifactory/postgresql-yum/{{pgmjv}}.{{pgmnv}}/redhat/rhel-7.2-x86_64 | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: Add apt-repository for postgres | |
| tags: | |
| - db | |
| - dbslave | |
| - bdr | |
| - repo | |
| become: root | |
| become: yes | |
| apt_repository: | |
| repo: 'deb https://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | |
| state: present | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| - name: Install BDR postgres repo | |
| tags: | |
| - db | |
| - dbslave | |
| - bdr | |
| - repo | |
| yum: name=http://packages.2ndquadrant.com/postgresql-bdr94-2ndquadrant/yum-repo-rpms/postgresql-bdr94-2ndquadrant-redhat-latest.noarch.rpm state=present | |
| - yum: pkg={{item}} state=installed | |
| tags: | |
| - bdr | |
| with_items: | |
| - mdadm.x86_64 | |
| - postgresql-bdr94-server | |
| - postgresql-bdr94-bdr | |
| - postgresql-bdr94-contrib | |
| - postgresql-bdr94-libs | |
| - postgresql-bdr94-plpython | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: "Install: CentOS = {{ansible_distribution}} install packages" | |
| tags: | |
| - db | |
| - dbslave | |
| - postgres | |
| - repo | |
| become_user: root | |
| become: yes | |
| action: > | |
| {{ansible_pkg_mgr}} name={{item}} state=present | |
| with_items: | |
| - git | |
| - "{{pg}}{{pgmjv}}{{pgmnv}}-server" | |
| - "{{pg}}{{pgmjv}}{{pgmnv}}-contrib" | |
| - "{{pg}}{{pgmjv}}{{pgmnv}}-libs" | |
| - postgis23_* | |
| - pgbackrest | |
| - pgbouncer | |
| - python-psycopg2 #Postgres module library | |
| - nagios-plugins | |
| - nagios-plugins-nrpe | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: "Install: Ubuntu = {{ansible_distribution}} install packages" | |
| tags: | |
| - db | |
| - dbslave | |
| - postgres | |
| - repo | |
| become_user: root | |
| become: yes | |
| action: > | |
| {{ansible_pkg_mgr}} name={{item}} allow_unauthenticated=yes state=present | |
| with_items: | |
| - git | |
| - python-apt | |
| - python-pycurl | |
| - locales | |
| - "{{pg}}-{{pgmjv}}.{{pgmnv}}" | |
| - "{{pg}}-{{pgmjv}}.{{pgmnv}}-postgis-2.3" | |
| - pgbackrest | |
| - pgbouncer | |
| - libdbd-pg-perl | |
| - libio-socket-ssl-perl | |
| - libxml-libxml-perl | |
| - python-psycopg2 #Postgres module library | |
| - nagios-nrpe-server | |
| - nagios-nrpe-plugin | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| - name: Create directories | |
| tags: | |
| - db | |
| - dbslave | |
| - directory | |
| become: yes | |
| become_user: root | |
| file: | |
| path: "{{item.line}}" | |
| state: directory | |
| owner: postgres | |
| group: postgres | |
| mode: "{{item.mode}}" | |
| with_items: | |
| - { line: "{{pgdata}}", mode: '0700'} | |
| - { line: /mount/pg-backup, mode: '0700'} | |
| - { line: /mount/pg-backup/pgbackrest, mode: '0700'} | |
| - { line: /mount/pg-backup/backup, mode: '0700'} | |
| - { line: "{{gitcheckout}}", mode: '0755'} | |
| - { line: "{{xlogs}}", mode: '0700'} | |
| - { line: /var/log/markers, mode: '0755'} | |
| - name: Clone git pg_monitoring reop | |
| tags: | |
| - db | |
| - dbslave | |
| - git | |
| become: yes | |
| become_user: postgres | |
| git: | |
| repo: https://{{git_user}}:{{git_pass}}@git.github.com/<user>/<path>.git | |
| accept_hostkey: yes | |
| dest: "{{gitcheckout}}" | |
| version: master | |
| update: yes | |
| force: yes | |
| - name: set user password | |
| tags: | |
| - db | |
| - dbslave | |
| - psswd | |
| become: yes | |
| become_user: root | |
| user: | |
| name: postgres | |
| password: pgpass | |
| - name: setup ssh-key for user | |
| tags: | |
| - db | |
| - dbslave | |
| - sshkey | |
| user: | |
| name: postgres | |
| generate_ssh_key: yes | |
| ssh_key_bits: 2048 | |
| ssh_key_file: .ssh/id_rsa | |
| - name: fetch local postgres public key | |
| tags: | |
| - db | |
| - dbslave | |
| - sshkey | |
| become: yes | |
| become_user: postgres | |
| shell: cat ~/.ssh/id_rsa.pub | |
| register: ssh_keys | |
| - name: List my SSH public keys | |
| debug: msg="{{ ssh_keys.stdout }}" | |
| - name: deploy keys on all servers ####Need to work on providing remote IP to copy key over for now doing it manually | |
| tags: | |
| - db | |
| - dbslave | |
| - sshkey | |
| authorized_key: user=postgres key="{{ item[0] }}" | |
| authorized_key: user=postgres key="{{item}}" | |
| with_items: "{{ ssh_keys.stdout }}" | |
| ############## | |
| #Setup DB | |
| ############## | |
| - name: initdb DB {{ansible_distribution}} | |
| tags: | |
| - db | |
| - dbslave | |
| - initdb | |
| shell : /usr/pgsql-9.6/bin/initdb -E UTF8 -D "{{pgdata}}" | |
| become: yes | |
| become_user: postgres | |
| ignore_errors: yes | |
| args: | |
| creates: "{{pgdata}}PG_VERSION" | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: mv pgdata and create link {{ansible_distribution}} | |
| tags: | |
| - db | |
| - dbslave | |
| - initdb | |
| shell : mv /var/lib/postgresql/9.6/main/* "{{pgdata}}"; rm -rf /var/lib/postgresql/9.6/main | |
| become: yes | |
| become_user: postgres | |
| ignore_errors: yes | |
| args: | |
| creates: "{{pgdata}}PG_VERSION" | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| - name: link "{{pgdata}}" to /var/lib/pgsql/9.6 centOS = "{{ansible_distribution}}" | |
| tags: | |
| - db | |
| - dbslave | |
| - initdb | |
| file: | |
| src: "{{pgdata}}" | |
| dest: /var/lib/pgsql/9.6/data | |
| owner: postgres | |
| group: postgres | |
| state: link | |
| force: yes | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| notify: restart_postgresql | |
| - name: link "{{pgdata}}" to /var/lib/postgresql/9.6/main Ubuntu = "{{ansible_distribution}}" | |
| tags: | |
| - db | |
| - dbslave | |
| - initdb | |
| file: | |
| src: "{{pgdata}}" | |
| dest: /var/lib/postgresql/9.6/main | |
| owner: postgres | |
| group: postgres | |
| state: link | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| notify: restart_postgresql | |
| # - name: run handler #### Working on flushing handler to restart DB at this point | |
| # tags: | |
| # - db | |
| # - dbslave | |
| # - initdb | |
| # meta: flush_handlers | |
| - name: start postgresql-9.6 | |
| tags: | |
| - db | |
| - dbslave | |
| - restartdb | |
| service: name=postgresql state=started | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| - name: start postgresql-9.6 | |
| tags: | |
| - db | |
| - dbslave | |
| - restartdb | |
| service: name=postgresql-9.6 state=started | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: Configure /etc/../postgresql.conf | |
| tags: | |
| - db | |
| - dbslave | |
| - pgconf | |
| notify: restart_postgresql | |
| lineinfile: | |
| dest: /etc/postgresql/9.6/main/postgresql.conf | |
| regexp: "^{{ item.regexp }}" | |
| line: "{{ item.line}}" | |
| with_items: | |
| - { regexp: "^#?listen_addresses =", line: "listen_addresses = '*'" } | |
| # - { regexp: "^#?max_connections = ", line: "max_connections = {{ pg_connection }}" } | |
| - { regexp: "^#?wal_level = ", line: "wal_level = hot_standby" } | |
| - { regexp: "^#?archive_mode =", line: "archive_mode = on" } | |
| - { regexp: "^#?archive_command =", line: "archive_command = '/bin/pgbackrest --stanza=db archive-push %p'" } | |
| - { regexp: "^#?wal_keep_segments =", line: "wal_keep_segments = 100" } | |
| - { regexp: "^#?max_wal_senders =", line: "max_wal_senders = 6" } | |
| - { regexp: "^#?max_replication_slots =", line: "max_replication_slots = 6" } | |
| - { regexp: "^#?hot_standby =", line: "hot_standby = on" } | |
| - { regexp: "^#?log_filename =", line: "log_filename = 'postgresql-%a-%d.log'" } | |
| - { regexp: "^#?log_line_prefix =", line: "log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d,%r,%h,app=%a,transaction_id=%x '" } | |
| - { regexp: "^#?log_connections =", line: "log_connections = on" } | |
| - { regexp: "^#?log_disconnections =", line: "log_disconnections = on" } | |
| - { regexp: "^#?log_duration =", line: "log_duration = on" } | |
| - { regexp: "^#?log_lock_waits =", line: "log_lock_waits = on" } | |
| - { regexp: "^#?log_statement =", line: "log_statement = 'ddl'" } | |
| - { regexp: "^#?log_min_duration_statement =", line: "log_min_duration_statement = 3600" } | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| notify: restart_postgresql | |
| - name: Configure "{{ pgdata }}pstgresql.conf" | |
| tags: | |
| - db | |
| - dbslave | |
| - pgconf | |
| notify: restart_postgresql | |
| lineinfile: | |
| dest: "{{ pgdata }}postgresql.conf" | |
| regexp: "^{{ item.regexp }}" | |
| line: "{{ item.line}}" | |
| with_items: | |
| - { regexp: "^#?listen_addresses =", line: "listen_addresses = '*'" } | |
| # - { regexp: "^#?max_connections = ", line: "max_connections = {{ pg_connection }}" } | |
| - { regexp: "^#?wal_level = ", line: "wal_level = hot_standby" } | |
| - { regexp: "^#?archive_mode =", line: "archive_mode = on" } | |
| - { regexp: "^#?archive_command =", line: "archive_command = '/bin/pgbackrest --stanza=db archive-push %p'" } | |
| - { regexp: "^#?wal_keep_segments =", line: "wal_keep_segments = 100" } | |
| - { regexp: "^#?max_wal_senders =", line: "max_wal_senders = 6" } | |
| - { regexp: "^#?max_replication_slots =", line: "max_replication_slots = 6" } | |
| - { regexp: "^#?hot_standby =", line: "hot_standby = on" } | |
| - { regexp: "^#?log_filename =", line: "log_filename = 'postgresql-%a-%d.log'" } | |
| - { regexp: "^#?log_line_prefix =", line: "log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d,%r,%h,app=%a,transaction_id=%x '" } | |
| - { regexp: "^#?log_connections =", line: "log_connections = on" } | |
| - { regexp: "^#?log_disconnections =", line: "log_disconnections = on" } | |
| - { regexp: "^#?log_duration =", line: "log_duration = on" } | |
| - { regexp: "^#?log_lock_waits =", line: "log_lock_waits = on" } | |
| - { regexp: "^#?log_statement =", line: "log_statement = 'ddl'" } | |
| - { regexp: "^#?log_min_duration_statement =", line: "log_min_duration_statement = 3600" } | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| notify: restart_postgresql | |
| - name: Add access to the replication user in /etc/..pg_hba.conf | |
| tags: | |
| - db | |
| - dbslave | |
| - hbaconf | |
| notify: restart_postgresql | |
| become_user: postgres | |
| lineinfile: | |
| dest=/etc/postgresql/9.6/main/pg_hba.conf | |
| line="{{ item }}" | |
| with_items: | |
| - local all monitoring trust # allow monitoring | |
| - host all monitoring 127.0.0.1/32 trust | |
| - host all monitoring {{mIP}}/32 md5 | |
| - host all monitoring {{s1IP}}/0 md5 # | |
| - host replication replicator {{mIP}}/32 trust # Node0 | |
| - host replication replicator {{s1IP}}/32 trust # Node1 | |
| - host replication replicator {{s2IP}}/32 trust # Node2 | |
| - host all user1 192.168.99.0/20 md5 | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| notify: restart_postgresql | |
| - name: Add access to the replication user in "{{pgdata}}pg_hba.conf" | |
| tags: | |
| - db | |
| - dbslave | |
| - hbaconf | |
| notify: restart_postgresql | |
| become_user: postgres | |
| lineinfile: | |
| dest="{{pgdata}}pg_hba.conf" | |
| line="{{ item }}" | |
| with_items: | |
| - local all monitoring trust # allow monitoring | |
| - host all monitoring 127.0.0.1/32 trust | |
| - host all monitoring {{mIP}}/32 md5 | |
| - host all monitoring {{s1IP}}/0 md5 # | |
| - host replication replicator {{mIP}}/32 trust # Node0 | |
| - host replication replicator {{s1IP}}/32 trust # Node1 | |
| - host replication replicator {{s2IP}}/32 trust # Node2 | |
| - host all user1 192.168.99.0/20 md5 | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| notify: restart_postgresql | |
| - name: set pg password | |
| tags: | |
| - db | |
| - pguser | |
| become: yes | |
| become_user: postgres | |
| postgresql_user: | |
| db: postgres | |
| name: "{{ item.pg_user }}" | |
| password: "{{ item.pg_password }}" | |
| role_attr_flags: "{{ item.role }}" | |
| encrypted: yes | |
| with_items: | |
| - { pg_user: "postgres", pg_password: "md5bff41c6b9ff2a244f651dc5738282db5", role: "" } ###md5 is set to postgres must update | |
| - { pg_user: "replicator", pg_password: "md5bff41c6b9ff2a244f651dc5738282db5", role: "replication" } | |
| - { pg_user: "user1", pg_password: "md5bff41c6b9ff2a244f651dc5738282db5", role: "" } | |
| - { pg_user: "monitoring", pg_password: "md5bff41c6b9ff2a244f651dc5738282db5", role: "" } | |
| - name: touch .pgpass | |
| tags: | |
| - db | |
| - dbslave | |
| - pgpass | |
| become: yes | |
| become_user: postgres | |
| file: path=~/.pgpass owner=postgres group=postgres mode=0600 state=touch | |
| - name: set .pgpass content | |
| tags: | |
| - db | |
| - dbslave | |
| become_user: postgres | |
| lineinfile: | |
| dest=~/.pgpass | |
| line="{{item}}" | |
| with_items: | |
| - '#hostname:port:database:username:password' | |
| - '*:*:*:postgres:md5bff41c6b9ff2a244f651dc5738282db5' ###Md5 set to postgres must udpate | |
| - '*:*:*:monitoring:md5bff41c6b9ff2a244f651dc5738282db5' | |
| - name: Create databases | |
| tags: | |
| - db | |
| - createdb | |
| become: yes | |
| become_user: postgres | |
| postgresql_db: | |
| name: "{{ item }}" | |
| with_items: | |
| - db1 | |
| - db2 | |
| - name: Setup PostGIS in DB1 & DB2 | |
| tags: | |
| - db | |
| - postgis | |
| become: yes | |
| become_user: postgres | |
| postgresql_ext: | |
| name: "{{ item.ext }}" | |
| db: "{{ item.dbs }}" | |
| with_items: | |
| - { ext: "postgis", dbs: "db1" } | |
| - { ext: "postgis_topology", dbs: "db1" } | |
| - { ext: "postgis_tiger_geocoder", dbs: "db1" } | |
| - { ext: "plr", dbs: "db1" } | |
| - { ext: "pgcrypto", dbs: "db1" } | |
| - { ext: "plpgsql", dbs: "db1" } | |
| - { ext: "uuid-ossp", dbs: "db1" } | |
| - { ext: "pgrouting", dbs: "db1" } | |
| - { ext: "hstore", dbs: "db1" } | |
| - { ext: "fuzzystmatch", dbs: "db1"} | |
| - { ext: "postgis", dbs: "db1" } | |
| - { ext: "postgis_topology", dbs: "db2" } | |
| - { ext: "postgis_tiger_geocoder", dbs: "db2" } | |
| - { ext: "plr", dbs: "db2" } | |
| - { ext: "pgcrypto", dbs: "db2" } | |
| - { ext: "plpgsql", dbs: "db2" } | |
| - { ext: "uuid-ossp", dbs: "db2" } | |
| - { ext: "pgrouting", dbs: "db2" } | |
| - { ext: "hstore", dbs: "db2" } | |
| - { ext: "fuzzystmatch", dbs: "db2" } | |
| ############## | |
| #setup slaves | |
| ############## | |
| - name: Stopping slave DB postgresql-9.6 Ubuntu = {{ansible_distribution}} | |
| tags: | |
| - dbslave | |
| service: name=postgresql state=stopped | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| - name: Stopping slave DB postgresql-9.6 CentOS = {{ansible_distribution}} | |
| tags: | |
| - dbslave | |
| service: name=postgresql-9.6 state=stopped | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: Slave clean up old data directory | |
| tags: | |
| - dbslave | |
| become: yes | |
| become_user: postgres | |
| file: | |
| path: "{{pgdata}}" | |
| state: absent | |
| - name: re-create data directory | |
| tags: | |
| - dbslave | |
| file: | |
| path: "{{pgdata}}" | |
| owner: postgres | |
| group: postgres | |
| mode: 0700 | |
| state: directory | |
| - name: drop repliction slot on {{mIP}} | |
| tags: | |
| - dbslave | |
| - dropslot | |
| become: yes | |
| become_user: postgres | |
| command: psql -c "select pg_drop_replication_slot('"{{ansible_hostname}}"');" | |
| delegate_to: "{{mIP}}" ##delegate_to requires ssh from plybook host to mIP as user running playbook | |
| ignore_errors: True | |
| - name: create replication slot | |
| tags: | |
| - dbslave | |
| - createslot | |
| become: yes | |
| become_user: postgres | |
| command: psql -c "select pg_create_physical_replication_slot('"{{ansible_hostname}}"');" | |
| delegate_to: "{{mIP}}" | |
| - name: Slave initialize slave postgresql cluster | |
| tags: | |
| - dbslave | |
| - init_slave | |
| become: yes | |
| become_user: postgres | |
| command: pg_basebackup -Xs -P -R -v -U replicator -h "{{mIP}}" -D "{{pgdata}}" -S {{ansible_hostname}} | |
| - name: udpate recovery.conf | |
| tags: | |
| - dbslave | |
| - recoveryconf | |
| become: yes | |
| become_user: postgres | |
| lineinfile: | |
| dest="{{pgdata}}recovery.conf" | |
| line="{{item}}" | |
| with_items: | |
| - trigger_file = '/tmp/touch_to_promote_to_me_master' | |
| - recovery_target_timeline = 'latest' | |
| - "#recovery_min_apply_delay = '6h'" | |
| notify: restart_postgresql | |
| ############## | |
| #Promote Slave to Master | |
| ############## | |
| - name: step1 Promoting slave "{{host}}" to Master (shutdown master) "{{mIP}}" CentoOS = "{{ansible_distribution}}" | |
| tags: | |
| - promote | |
| command: systemctl stop postgresql-9.6 | |
| delegate_to: "{{mIP}}" | |
| register: pg_status | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: step1 Promoting slave "{{host}}" to Master (shutdown master) "{{mIP}}" CentOS = "{{ansible_distribution}}" | |
| tags: | |
| - promote | |
| command: service postgresql stop | |
| delegate_to: "{{mIP}}" | |
| register: pg_status | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| - name: step2 Promoting slave "{{host}}" to Master via touch | |
| tags: | |
| - promote | |
| become: yes | |
| become_user: postgres | |
| file: path=/tmp/touch_to_promote_to_me_master state=touch | |
| - name: step3 move remaining slaves "{{s2IP}}" to new Master "{{host}}" | |
| tags: | |
| - promote | |
| become: yes | |
| become_user: postgres | |
| lineinfile: | |
| dest="{{pgdata}}recovery.conf" | |
| regexp="{{item.regexp}}" | |
| line="{{item.line}}" | |
| with_items: | |
| - { regexp: "#?{{mIP}}", line: "primary_conninfo = 'user=replication host={{host}} port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'" } | |
| delegate_to: "{{s2IP}}" | |
| notify: restart_postgresql | |
| ignore_errors: True | |
| ############## | |
| #Remove node from cluster | |
| ############## | |
| - name: Verify the "{{host}}" is not master | |
| tags: | |
| - remove | |
| become: yes | |
| become_user: postgres | |
| command: psql -Upostgres -Atc "select pg_is_in_recovery();" | |
| register: result | |
| - name: Step2 Shutdown db on "{{host}}" CentOS = "{{ansible_distribution}}" | |
| tags: | |
| - remove | |
| command: systemctl stop postgresql-9.6 | |
| when: result.stdout == "t" and (ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux') | |
| - name: Step2 Shutdown db on "{{host}}" CentOS = "{{ansible_distribution}}" | |
| tags: | |
| - remove | |
| command: service postgresql stop | |
| when: result.stdout == "t" and (ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu') | |
| - name: Step1 Remove replication slot "{{host}}" from "{{mIP}}" node | |
| tags: | |
| - remove | |
| become: yes | |
| become_user: postgres | |
| command: psql -c "select pg_drop_replication_slot('{{ansible_hostname}}');" | |
| delegate_to: "{{mIP}}" | |
| when: result.stdout == "t" | |
| ############## | |
| #PGBACKREST BACKUP | |
| ############## | |
| - name: update pgbackrest | |
| tags: | |
| - db | |
| - dbslave | |
| - pgbackrest | |
| ini_file: | |
| path: /etc/pgbackrest.conf | |
| section: "{{item.sec}}" | |
| option: "{{item.line}}" | |
| value: "{{item.value}}" | |
| no_extra_spaces: true | |
| mode: 0664 | |
| group: postgres | |
| with_items: | |
| - { line: "backup-standby", sec: "global", value: "y" } | |
| - { line: "process-max", sec: "global", value: "3"} | |
| - { line: "repo-path",sec: "global", value: "/mount/pg-backup/pgbackrest/" } | |
| - { line: "retention-full", sec: "global", value: "1"} | |
| - { line: "stop-auto", sec: "global", value: "y"} | |
| - { line: "archive-check", sec: "global", value: "y"} | |
| - { line: "#thread-timeout", sec: "global", value: "300"} | |
| - { line: "db-timeout", sec: "global", value: "600"} | |
| - { line: "protocol-timeout", sec: "global", value: "800"} | |
| - { line: "#db-host", sec: "mydb", value: "{{ mIP }}"} | |
| - { line: "db-path", sec: "mydb", value: "{{ pgdata }}"} | |
| - { line: "#db1-user", sec: "mydb", value: "postgres" } | |
| - { line: "#db2-host", sec: "mydb", value: "{{ s1IP }}"} | |
| - { line: "#db2-path", sec: "mydb", value: "{{ pgdata }}"} | |
| - { line: "#db2-user", sec: "mydb", value: "postgres"} | |
| - { line: "#db-port", sec: "mydb", value: "5432"} | |
| - { line: "#Uncomment dbrest& update it as db only for restoringDB from remote host and run below pgbackrest command" ,sec: "dbrestore", value: "####"} | |
| - { line: "#pgbackrest --stanza=mydb --db-path=/mount/pg-backup/postgres_restore restore" , sec: "dbrestore", value: "##Run this command"} | |
| - { line: "#db-path ##Path to be Restored DB", sec: "dbrestore", value: "//mount/pg-backup/postgres_restore"} | |
| - { line: "#[mydb]", sec: "dbrestore", value: "####"} | |
| - { line: "#backup-host ##IP where the backup are held", sec: "dbrestore", value: "192.168.99.2"} | |
| - name: check if db path exists | |
| tags: | |
| - db | |
| - stanza | |
| - pgbackrest | |
| stat: path=/mount/pg-backup/pgbackrest/backup/db/ | |
| register: db_present | |
| - name: Create Pgbackrest Stanza | |
| tags: | |
| - db | |
| - stanza | |
| - pgbackrest | |
| become: yes | |
| become_user: postgres | |
| command: pgbackrest --stanza=mydb stanza-create | |
| when: db_present.stat.exists == false | |
| ignore_errors: true | |
| ############## | |
| # pgbouncer | |
| ############## | |
| ############## | |
| #Setup cron jobs for monitoring and backup | |
| ############## | |
| - name: Add cron | |
| tags: | |
| - db | |
| - dbslave | |
| - cron | |
| cron: | |
| minute="{{item.minute}}" weekday="{{item.weekday}}" hour="{{item.hour}}" job="{{item.job}}" name={{item.name}} user="postgres" | |
| with_items: | |
| - {minute: "0,30", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_disk.sh >/dev/null 2>&1", name: "Disk monitoring"} | |
| - {minute: "0,30", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_load.sh >/dev/null 2>&1", name: "OS Load"} | |
| - {minute: "*/5", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_pg_status.sh >/dev/null 2>&1", name: "Postgres status"} | |
| # - {minute: "*/5", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_pgbouncer_status.sh >/dev/null 2>&1", name: "Pgbouncer status"} | |
| - {minute: "*/15", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_pg_conn.sh >/dev/null 2>&1", name: "Postgres connection"} | |
| - {minute: "*/15", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_pg_blocking.sh >/dev/null 2>&1", name: "Pg blocking"} | |
| - {minute: "*/10", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_pg_log.sh /var/log/postgresql/ /var/log/markers/marker_postgres_log.tail >/dev/null 2>&1", name: "postgres Log monitor"} | |
| # - {minute: "0,30", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_pgbackup_log.sh >/dev/null 2>&1", name: "monitor_pgbackup_log"} | |
| - {minute: "*/5", weekday: "*", hour: "*", job: "{{gitcheckout}}monitor_standby_lag.sh >/dev/null 2>&1", name: "Repllication Lag"} | |
| - {minute: "00", weekday: "6", hour: "05", job: "pgbackrest --type=full --stanza=db backup", name: "pgbackrest full backup at 11PM PST"} | |
| - {minute: "00", weekday: "0-5", hour: "05", job: "pgbackrest --type=diff --stanza=db backup", name: "pgbackreset diff backup at 11PM PST"} | |
| # - {minute: "11", weekday: "*", hour: "*", job: "{{gitcheckout}}pg_backup.sh fsbackup", name: "PG Bckup FileLevel"} | |
| # - {minute: "09", weekday: "*", hour: "*", job: "{{gitcheckout}}pg_backup.sh dump", name: "PG Dump backup"} | |
| handlers: | |
| - name: restart_postgresql | |
| service: name=postgresql-9.6 state=restarted | |
| when: ansible_distribution == 'CentOS' or ansible_distribution == 'Red Hat Enterprise Linux' | |
| - name: restart_postgresql | |
| service: name=postgresql state=restarted | |
| when: ansible_distribution == 'Debian' or ansible_distribution == 'Ubuntu' | |
| # shell: pg_ctl -D $PGDATA restart | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment