GCEにPostgreSQLを入れbackupをCloudStorageに保存してみた

～略～
  - name: install pip
    shell: python /opt/get-pip.py
  when: 
    - check_python|succeeded
    - check_pip|failed

- name: install pip netaddr
  pip:
    name: "{{ item }}"
  with_items:
    - netaddr
    - requests
    - google-auth

---
- name: install repository of postgresql
  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: install postgresql server
  yum:
    name: "{{ item }}"
    enablerepo: pgdg96
    disablerepo: base,updates
  with_items:
    - postgresql96-server
    - postgresql96-devel
    - postgresql96-contrib
    - python-psycopg2

- name: check postgres data exists 
  stat:
    path: /var/lib/pgsql/9.6/data/PG_VERSION 
  register: chk_postgres_data
  check_mode: no

- name: initializing database
  shell: /usr/pgsql-9.6/bin/postgresql96-setup initdb 
  when: not chk_postgres_data.stat.exists 

- name: start postgresql
  service: 
    name: postgresql-9.6
    state: started
    enabled: yes

- name: postgres user sudoers
  lineinfile:
    dest: /etc/sudoers.d/postgres
    owner: root
    group: root
    mode: 0440
    line: "%root ALL=(postgres) NOPASSWD: ALL"
    state: present
    create: yes
    validate: "visudo -cf %s"

- name: set pass postgres(os)
  user:
    name: postgres
    password: "{{ os_user_passwords.2.postgresql }}"
  no_log: true

- name: set pass postgres(db)
  become: yes
  become_user: postgres
  postgresql_user:
    db: postgres
    name: postgres
    password: "{{ os_user_passwords.2.postgresql }}"
    priv: "ALL"
    expires: infinity
    login_user: postgres
  ignore_errors: yes

- name: create database
  become: yes
  become_user: postgres
  postgresql_db:
    name: "{{ item }}"
    encoding: "UTF-8"
    login_user: postgres
    login_password: "{{ os_user_passwords.2.postgresql }}"
  with_items: "{{ dbnames }}"
  ignore_errors: yes

- name: create dbuser
  become: yes
  become_user: postgres
  postgresql_user:
    db: "{{ item.dbname }}"
    name: "{{ item.dbuser }}"
    password: "{{ item.dbpass }}"
    priv: "{{ item.priv }}"
    state: present
    login_user: postgres
    login_password: "{{ dbaccounts.0.dbpass }}"
  with_items: "{{ dbaccounts }}"
  ignore_errors: yes

#- debug: var=os_user_passwords.2.postgresql
#  tags: vartest

- name: set pass postgres
  user:
    name: postgres
    password: "{{ os_user_passwords.2.postgresql }}"
  no_log: true

- name: set configs
  template: 
    src: "{{ item.name }}.j2"
    dest: "{{ item.path }}/{{ item.name }}"
  with_items: "{{ postgresql_confs }}"
  notify: restart-postgresql
  when: ( postgresql_confs != '' and postgresql_confs is defined )
  tags: pgconf

- name: backup to gcp cloud storage.
  import_tasks: tasks/postgres_backup.yml
  when: use_gcloud_storage_bkup_script == 'yes'

###pg_hba.confのtemplate抜粋
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             {{ ansible_default_ipv4.network }}/{{ ipmask|ipv4('prefix') }}     md5

###postgresql.confのtemplate抜粋
# - Memory -

{% if ansible_memtotal_mb < 2048 %}
shared_buffers = {{ (ansible_memtotal_mb*0.2)|round|int }}MB
{% elif ansible_memtotal_mb >= 2048 and ansible_memtotal_mb < 32768 %}
shared_buffers = {{ (ansible_memtotal_mb*0.25)|round|int }}MB
{% elif ansible_memtotal_mb >= 32768 %}
shared_buffers = 8GB
{% endif %}
#shared_buffers = 128MB         # min 128kB
#～略～
# you actively intend to use prepared transactions.
work_mem = {{ (((ansible_memtotal_mb*0.25)|round|int)/max_connections)|round|int }}MB               # min 64kB
maintenance_work_mem = {{ (ansible_memtotal_mb*0.1)|round|int }}MB      # min 1MB

# postgres_backup tasks

- name: check gsuite
  stat:
    path: /var/lib/pgsql/google-cloud-sdk/bin/gcloud
  register: chk_gs
  check_mode: yes

- name: download gsuite
  get_url:
    url: "{{ gsuite_arc_uri }}/{{ gsuite_arc_file }}"
    dest: "/tmp/{{ gsuite_arc_file }}"
    mode: 0755
  when: not chk_gs.stat.exists

- name: expand gsuite
  become: yes
  become_user: postgres
  command: chdir=/tmp tar xzf {{ gsuite_arc_file }}
  when: not chk_gs.stat.exists

- name: install gsuite
  become: yes
  become_user: postgres
  command: chdir=/var/lib/pgsql /tmp/google-cloud-sdk/install.sh -q --rc-path ~/.bashrc
  when: not chk_gs.stat.exists

- name: chk service account
  become: yes
  become_user: postgres
  shell: ~/google-cloud-sdk/bin/gcloud auth list|grep {{ vault_gcp_account }}
  register: chk_sa
  check_mode: yes
  ignore_errors: yes

- name: set gcloud credential
  template:
    src: gcloud_credential.j2
    dest: /var/lib/pgsql/.config/gcloud/application_default_credentials.json
    owner: postgres
    group: postgres
    mode: 0664
  when: chk_sa|failed

- name: activate credential
  become: yes
  become_user: postgres
  command: chdir=/var/lib/pgsql ~/google-cloud-sdk/bin/gcloud auth activate-service-account {{ vault_gcp_account }} --key-file ~/.config/gcloud/application_default_credentials.json --project {{ vault_gcp_project }}
  when: chk_sa|failed

- name: set backup script
  template: 
    src: ps-snapshot.sh.j2
    dest: /var/lib/pgsql/9.6/backups/ps-snapshot.sh
    owner: postgres
    group: postgres
    mode: 0755

- name: set crontab for backup postgresql
  cron:
    name: "postgresql backup(send to remote storage)"
    user: 'postgres'
    job: '/var/lib/pgsql/9.6/backups/ps-snapshot.sh'
    minute: '0'
    hour: '3'
    weekday: '*'

#!/bin/bash
# Requirements:
#   - gcloud/gsutil is installed on the box
#   - gcloud is logged in as a user with write access to Google Cloud Storage
#   - The file has execution rights so that it can be run in cron
#   - The Google Cloud Storage bucket already exits

# Exit on any error
set -e

BUCKET='gs://{{ postgres_backup_gcp_bucket }}'
JOB_TIMESTAMP=`date +%Y%m%d-%H%M`
DIR='{{ postgres_backup_dir }}'
GS_UTIL='{{ gs_util_path }}'

cd $DIR

GEN=10
NUMBER=`"$GS_UTIL" ls "$BUCKET/*.gz" | wc -l`
if [ $NUMBER -gt $GEN ]; then
    DELNUM=$(expr $NUMBER - $GEN)
    FILE_NAME=`"$GS_UTIL" ls "$BUCKET/*.gz" | head -n ${DELNUM}`
  for i in ${FILE_NAME}
  do
    gsutil rm -f ${i}
  done  
else
    :
fi

/usr/bin/pg_dumpall -w -f $JOB_TIMESTAMP-pad.sql

/bin/tar -czf $JOB_TIMESTAMP.tar.gz $JOB_TIMESTAMP-pad.sql
"$GS_UTIL" cp $JOB_TIMESTAMP.tar.gz "$BUCKET"

rm -f $JOB_TIMESTAMP-pad.sql $JOB_TIMESTAMP.tar.gz

#!/bin/bash
# note: create local snapshot. require gsuite tools.

DiskLIST=/tmp/dlist
INSTANCE_NAME=$(uname -n)
gcloud compute disks list >${DiskLIST}
SNAPSHOT_ZONE_NAME=$(cat ${DiskLIST} | awk -v cpfrom="${INSTANCE_NAME}" '$1 == cpfrom {print $2}')
SNAPSHOT_PREFIX=snapshot
TODAY=$(date +%Y%m%d%H%M)
GENERATION=3

## create local instance snapshot.
gcloud compute disks snapshot -q ${INSTANCE_NAME} --zone=${SNAPSHOT_ZONE_NAME} --snapshot-names=${SNAPSHOT_PREFIX}-${INSTANCE_NAME}-${TODAY}

## delete old snapshots.
SnapshotLIST=/tmp/snaplist
gcloud compute snapshots list|grep ${INSTANCE_NAME}|awk '{print $1}'|sort -r > ${SnapshotLIST}
SnapNum=$(cat ${SnapshotLIST}|wc -l)

DelNum=$(expr ${SnapNum} - ${GENERATION})
if [ ${DelNum} -gt 0 ];then
  DelSnaps=$(tail -${DelNum} ${SnapshotLIST})
else
  exit
fi

if [ -n ${DelSnaps} ]; then
  for i in `echo ${DelSnaps}`;do
    if [ ${i} !=  ${SNAPSHOT_PREFIX}-${INSTANCE_NAME}-${TODAY} ];then
      gcloud compute snapshots delete -q ${i}                                                       
    fi
  done
fi
exit 

$ gcloud auth activate-service-account <serviceaccount-id> --key-file <path_to_credential> --project <pjname>
$ gcloud auth list

- name: add line sysctlconf
  lineinfile:
    path: /etc/sysctl.conf
    state: present
    regexp: '{{ item.regexp }}'
    line: '{{ item.line }}'
  with_items:
  - { regexp: '^vm.swappiness', line: 'vm.swappiness=30' }
  - { regexp: '^net.ipv4.tcp_fin_timeout', line: 'net.ipv4.tcp_fin_timeout = 10' }
  - { regexp: '^net.ipv4.tcp_keepalive_time',line: 'net.ipv4.tcp_keepalive_time = 1' }
  - { regexp: '^net.ipv4.tcp_keepalive_probes',line: 'net.ipv4.tcp_keepalive_probes = 5' }
  - { regexp: '^net.ipv4.tcp_keepalive_intvl', line: 'net.ipv4.tcp_keepalive_intvl = 1' }
  - { regexp: '^net.ipv4.tcp_tw_recycle', line: 'net.ipv4.tcp_tw_recycle = 0' }
  - { regexp: '^net.ipv4.tcp_tw_reuse', line: 'net.ipv4.tcp_tw_reuse = 0' }
  - { regexp: '^net.ipv4.tcp_max_syn_backlog', line: 'net.ipv4.tcp_max_syn_backlog = 8192' }
  - { regexp: 'net.core.somaxconn', line: 'net.core.somaxconn = 8192' }
  - { regexp: '^kernel.sem', line: 'kernel.sem = 250 32000 32 255' } #★これ
  tags: add-line-sysctlconf
  notify: sysctl-update
# update keepalive for AuloraPostgresql

- name: create systemd unit directory(for oom-killer)
  file:
    path: /etc/systemd/system/postgresql-9.6.service.d
    state: directory
    mode: 0755
    owner: root
    group: root

- name: set unit files(for oom-killer)
  template:
    src: postgresql.service.j2
    dest: /etc/systemd/system/postgresql-9.6.service.d/override.conf
  notify: run-daemon-reload

- meta: flush_handlers  

[Service]
OOMScoreAdjust=-1000
LimitNOFILE=65535

# tasks file for stackdriver
- name: download logging agent
  get_url:
    url: https://dl.google.com/cloudagents/install-logging-agent.sh
    dest: /usr/local/src/install-logging-agent.sh
    mode: 0755

- name: check loging agent installed
  shell: rpm -qa|grep google-fluentd
  register: chk_google_flientd
  check_mode: no
  ignore_errors: yes

- name: install google-fluentd
  command: chdir=/usr/local/src bash install-logging-agent.sh
  when: chk_google_flientd.rc != 0

- name: download monitoring agent 
  get_url:
    url: https://dl.google.com/cloudagents/install-monitoring-agent.sh
    dest: /usr/local/src/install-monitoring-agent.sh
    mode: 0755

- name: check monitoring agent installed
  shell: rpm -qa|grep stackdriver
  register: chk_stackdriver
  check_mode: no
  ignore_errors: yes

- name: install stackdriver
  command: chdir=/usr/local/src bash install-monitoring-agent.sh
  when: chk_stackdriver|failed

- block:
  - name: check postgresql config exists
    stat:
      path: /opt/stackdriver/collectd/etc/collectd.d/postgresql.conf
    register: chk_pg_sd_cnf
  - name: install postgresql config
    get_url:
      url: https://raw.githubusercontent.com/Stackdriver/stackdriver-agent-service-configs/master/etc/collectd.d/postgresql.conf
      dest: /opt/stackdriver/collectd/etc/collectd.d/postgresql.conf
      mode: 0644
    notify: restart-stackdriver-agent
    when: not chk_pg_sd_cnf.stat.exists
  - name: set stats user and pass
    template:
      src: postgresql.conf.j2
      dest: /opt/stackdriver/collectd/etc/collectd.d/postgresql.conf
  when: ('db' in group_names)

# This is the monitoring configuration for PostgreSQL.
# Make sure the statistics collector is enabled in your PostgreSQL configuration.
# NOTE: This configuration needs to be hand-edited in order to work.
# Look for DATABASE_NAME, STATS_USER, STATS_PASS, POSTGRESQL_HOST and POSTGRESQL_PORT to adjust your configuration file.
LoadPlugin postgresql
<Plugin "postgresql">
    # Each database needs a separate Database section.
    # Replace DATABASE_NAME in the Database section with the name of the database.
{% for dbname in dbnames %}
    <Database "{{ dbname }}">
        # Host and port are only used for TCP/IP connections.
        # Leaving them out indicates you wish to connect via domain sockets.
        # When using non-standard PostgreSQL configurations, replace the below with
        ##Host "POSTGRESQL_HOST"
        ##Port "POSTGRESQL_PORT"
        Host "127.0.0.1"
        Port "5432"
        User "{{ stackdriver_pg_user }}"
        Password "{{ stackdriver_pg_pass }}"
        Query backends
        Query transactions
        Query queries
        Query table_states
        Query disk_io
        Query disk_usage
    </Database>
{% endfor %}
</Plugin>

#!/bin/bash
## db init setting reload by total memory size change
ansibledir=/path_to_ansible
export ANSIBLE_CONFIG=${ansibledir}/ansible.cfg
cd ${ansibledir}
ansible-playbook db.yml -c local -i inventory -D -v --vault-password-file=path_to_file --tags=pgconf 

resource.type="gce_instance"
resource.labels.instance_id="＜my_instance_id＞"
logName="projects/＜my_project_id＞/logs/syslog"
"changed=" AND NOT "changed=0"