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

需要がありとりあえずできるところはAnsibleでやってみたりしたので備忘録です。
ポスグレはRDSやgitlabのバックエンド的なセットで勝手に入るもんくらいでまともに入れた経験がほぼなかったです。
CloudSQLじゃない理由としては最大同時接続数の問題で、ということがメインです。
パラメータグループも自由が利かなかったなどもありますが。
https://cloud.google.com/sql/docs/quotas

pip入れるansibleタスク

pipを入れないとpostgresqlモジュール使ったり設定ファイルのCIDR表記用のfilterつかえなかったのでpre_tasksでいれました

pip入れるtasks抜粋

～略～
  - 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

postgresqlサーバ入れるansibleタスク

postgresqlの設定のポイントなどこちら↓が参考になりました。
https://lets.postgresql.jp/documents/tutorial/introduction/1

postgresqlのタスクを一応ググったりなどして、templateで設定ファイルの共有メモリほかを搭載メモリから自動調整で設定される感じにするなど

serverいれるtask

---
- 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'

template抜粋

###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

ipmaskはdefaults/main.ymlとかに以下のように書いておきます。
ipmask: "{{ ansible_default_ipv4.address}}/{{ansible_default_ipv4.netmask }}"

pg_hba.confの顛末追記なんですけども、GCEのNICのネットワークって、/32みたいなんですよ。VPCやサブネット/22かなにかで切って配置してても。なんでかは知らないですがしょうがないので定数な感じにあとで修正することに。
https://blog.a-know.me/entry/2016/04/03/223308

pg_dumpallしたのをCloudStorageに転送するansibleタスク

backupについては、ローカル保存ではまずかろうとAzureBlobに投げ込むとかS3に投げ込むノリでCloudStorageに置いとけばいいかと思ってググったらブラウザ介した認証ばっかりでAnsibleやオートメーション的なものとの相性がアレだと思ったのでサービスアカウントでええやろということにしました。（毎回ブラウザ手動認証で夜明けに自動バックアップできてないといざというとき取れてなくて詰みます）

backupのansibleタスク

# 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: '*'

アカウントつくるとことバケットつくるとこは手動でやりましたが、クレデンシャルがあればクラウドモジュールもあるみたいだからバケット作るとこはどうにかはなりそうてのとTerraformとかでも再現性とテストとエビデンスとか引継ぎしやすい的な都合とかでやりやすいのかもしれない。
https://docs.ansible.com/ansible/2.7/scenario_guides/guide_gce.html

backupスクリプト

スクリプトはこちらのスクリプトを固有の情報をAnsible的に変数化してpg_dumpをpg_dumpallにかえたくらい。allじゃないともし複数スキーマつかった場合に時系列的な整合性がアレな気がして。

#!/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

これだけだとなる早復旧には足らないかんじするので頻度高めsnapshot+template化とかも考えたほうがいい気がしてとりあえずsnapshotスクリプトだけ

#!/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 

あとはpg_rmanとかきになる。
https://qiita.com/bwtakacy/items/84a446c642ffae76859b

バケット作成とサービスアカウントまわり

サービスアカウント作成方法は手動で以下のようにしました。

IAM＞サービスアカウント
用途が分かりやすい名前でつくってストレージのオブジェクト管理者ロールを与える
（世代管理で削除があるのでlsとcpだけでない都合で）
Jsonデータを選んで作成
クレデンシャルのjsonがダウンロードされるため、それをvaultに保存する
ansible-vault edit path_to_vault.yml --ask-vault-pass
サービスアカウントIDとプロジェクト名も使いたいものと違えばvault内の変数を修正します。

アクティベートはansible側にタスクを実装したけど手動でやる場合は以下のように実施

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

max_connectionが大きすぎるとカーネルセマフォを広げないとだめっぽいタスク

なんか起動しないようだったので。sysctlモジュールもあるみたいですがなんとなくlineinfile。具体的にはkernel.semの右端の数字を調整する必要があるようでした。

- 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

https://thinkit.co.jp/cert/tech/23/5/2.htm
https://www.postgresql.jp/document/9.6/html/kernel-resources.html
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraPostgreSQL.BestPractices.html

oom-killerに狙われるスコアを下げるユニットファイルを追加

- 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  

postgresql.service.j2

[Service]
OOMScoreAdjust=-1000
LimitNOFILE=65535

これ流した後にpostgresqlのプロセス番号調べて、/proc/process-number/oom_score_adjに-1000とか入ってればだいたいok。
くわしくはこちら

stackdriverによるモニタリングタスク追加

# 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>

あとはここらへんを参考にログアラート設定してく感じでしょうか。
UptimeCheckとかしたい場合はヘルスチェック元からSecurityGroup開ける必要が。
https://qiita.com/tora470/items/ddd8a80a4411bcb9a722

起動スクリプトでパラメータ調整

以下のような起動スクリプトをクラウドストレージに置いて（バケットに上げる前に文字コードと改行コード要注意）、
インスタンスの編集して、カスタムメタデータのkeyに、startup-script-urlを指定し、valueにクラウドストレージのURLを指定すると起動時に動いて標準出力は勝手にsyslogに出るのでstackdriverlogsとか入れとくとコンソールから変わった値がみられる。
https://cloud.google.com/compute/docs/startupscript
ansible的にはtagsでconfig設定してリロードするタスクだけを指定するので、計算して自動調整される値がインスタンスの搭載メモリに応じて変わる感じ。これすると休日や夜中にインスタンスサイズ変える必要にせまられときに値調整できる人がその場にいる必要がなくて休日出勤をたぶん防げるかもしれない。

#!/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 

ただし手動設定することで上書きされるとまずいみたいな事件が起こらないようにcronで差分チェックしてアラート出す仕組みを定期実行さすというフォローが要ります。マネージドのとちがって制限がないので。まあそれも上のコマンドに-Cつけてチェックモードで動かすやつをログに出してstackdriverかなにかでchenged=0じゃない場合にアラート出す感じにするといいのかな。
http://iga-ninja.hatenablog.com/entry/2015/10/25/091750
https://qiita.com/masahiro331/items/d1cc15b00efbeaf111ee

ログの指標を以下のような感じにポチポチつくってみて、ラベルの正規表現はなんか効いてなかったてのと正規表現はなんでもいけるわけでもないのとアラート設定するときに0秒間に0.01回以上でたらみたいなわかりづらい設定しないと検出できなかったりしたので追記。

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

フィルタのマニュアル↓
https://cloud.google.com/logging/docs/view/advanced_filters?hl=ja&_ga=2.125262412.-779814870.1500864087

ポスグレ的にはERROR/FATAL/PANICあたりもログ監視できるとよさそう。
https://www.slideshare.net/naka24nori/postgre-sql-55593111

あとまあ、冗長的なアレは機会があればまた。

サービスアカウント参考
http://kikumoto.hatenablog.com/entry/2015/10/05/203545
https://www.monotalk.xyz/blog/Retrieve-PostgresSQL-Backup-and-save-it-in-Google-Cloud-Storage/
https://cloud.google.com/sdk/docs/authorizing?hl=ja

gsuite参考
https://support.google.com/cloud/answer/6158849?hl=en&ref_topic=6262490#serviceaccounts
https://cloud.google.com/sdk/downloads?hl=JA#linux

ポスグレまわり参考
https://lets.postgresql.jp/documents/tutorial/introduction/1
https://qiita.com/tomlla/items/9fa2feab1b9bd8749584
http://www.cgis.biz/others/postgresql/18/
https://qiita.com/cobot00/items/8d59e0734314a88d74c7
https://www.postgresql.jp/document/10/html/app-initdb.html
https://qiita.com/rice_american/items/ceae28dad13c3977e3a8
https://www.postgresql.jp/document/9.6/html/runtime-config-resource.html
https://tdi.github.io/2016/08/13/automatic-postgresql-config-with-ansible/
https://github.com/ANXS/postgresql/blob/master/templates/postgresql.conf-9.6.j2
http://vdeep.net/centos7-postgres
https://qiita.com/progrhyme/items/6f936033b9d23efb1741
https://qiita.com/uhooi/items/44ed9370740c7521dce4
https://qiita.com/awakia/items/54503f309216c840765e
https://www.slideshare.net/naka24nori/postgre-sql-55593111

クラウドストレージまわり参考
https://remotestance.com/blog/2408/
https://www.monotalk.xyz/blog/Retrieve-PostgresSQL-Backup-and-save-it-in-Google-Cloud-Storage/
https://cloud.google.com/storage/docs/access-control/iam-permissions?hl=ja

せっかくGCEに入れたならと勧められたエクステンション
https://qiita.com/fujii_masao/items/6c116047cc134f2bed29
（ストアドプロシージャに処理を寄せられるのがうれしいんだそうです）