Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
custom Ansible Playbook to deploy PostgerSql or PostgreSql BDR + Pgbackrest + Pgbouncer
# 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