搭建pg_monz和Fluentd的试验环境

2 年 ago
清, 扬
7 minutes

文章的概要

给大家介绍一个能够轻松监控和操作 PostgreSQL 的工具的演讲，演讲是在2015年6月13日的北海道开源会议2015上进行的。在演讲中，我们介绍了通过使用 pg_monz 和 fluentd 来构建监控环境的步骤。

构图

構成図.png

服务器的角色

サーバ名IPアドレス役割zabbix192.168.1.20Zabbix監視サーバ,PostgreSQLログ管理サーバpgpool01192.168.1.11pgpool-II稼働サーバ。Active用の仮想IP割当て済pqpool02192.168.1.12pgpool-II稼働サーバ(Standby)pgsql01192.168.1.13PostgreSQL稼働サーバ。SRのprimarypgsql02192.168.1.14PostgreSQL稼働サーバ。SRの同期Standbypgsql03192.168.1.15PostgreSQL稼働サーバ。SRの非同期Standby

运行环境

以下是我在使用的产品和笔者环境中确认过的版本。

主机电脑

プロダクト名バージョンVirtualBox4.3.26Vagrant1.7.2Ansible1.9.0.1

客座操作系统

プロダクト名バージョンCentOS6.6PostgreSQL9.4.3Zabbix2.4.5Fluentd(td-agent)0.12.7(2.2.0)fluent-plugin-parser0.5.0fluent-plugin-record-reformer0.6.3fluent-plugin-pgjson0.0.7

构建过程

使用pgpool-II构建PostgreSQL SR集群（在主机PC上进行工作）。

    • ホストPCにVirtualBox,Vagrant,Ansibleをインストール（詳細省略）

 

    Vagrant作業用のディレクトリ、Vagrantfileを生成
$ mkdir PgMonzTest
$ cd PgMonzTest
$ vagrant init
    Vagrantfileを編集
Vagrant.configure(2) do |config|
  config.vm.define :pgpool01 do |h1|
    h1.vm.provider "virtualbox" do |v|
      v.customize ["modifyvm", :id, "--memory", 512]
    end
    h1.vm.network :private_network, ip: "192.168.1.11"
    h1.vm.box = "chef/centos-6.6"
    h1.vm.hostname = "pgpool01"
  end
  config.vm.define :pgpool02 do |h1|
    h1.vm.provider "virtualbox" do |v|
      v.customize ["modifyvm", :id, "--memory", 512]
    end
    h1.vm.network :private_network, ip: "192.168.1.12"
    h1.vm.box = "chef/centos-6.6"
    h1.vm.hostname = "pgpool02"
  end
  config.vm.define :pgsql01 do |h1|
    h1.vm.provider "virtualbox" do |v|
      v.customize ["modifyvm", :id, "--memory", 512]
    end
    h1.vm.network :private_network, ip: "192.168.1.13"
    h1.vm.box = "chef/centos-6.6"
    h1.vm.hostname = "pgsql01"
  end
  config.vm.define :pgsql02 do |h1|
    h1.vm.provider "virtualbox" do |v|
      v.customize ["modifyvm", :id, "--memory", 512]
    end
    h1.vm.network :private_network, ip: "192.168.1.14"
    h1.vm.box = "chef/centos-6.6"
    h1.vm.hostname = "pgsql02"
  end
  config.vm.define :pgsql03 do |h1|
    h1.vm.provider "virtualbox" do |v|
      v.customize ["modifyvm", :id, "--memory", 512]
    end
    h1.vm.network :private_network, ip: "192.168.1.15"
    h1.vm.box = "chef/centos-6.6"
    h1.vm.hostname = "pgsql03"
  end
  config.vm.define :zabbix do |h1|
    h1.vm.provider "virtualbox" do |v|
      v.customize ["modifyvm", :id, "--memory", 512]
    end
    h1.vm.network :private_network, ip: "192.168.1.20"
    h1.vm.box = "chef/centos-6.6"
    h1.vm.hostname = "zabbix"
  end
end
    VM を起動
$ vagrant up
$ vagrant status
Current machine states:

pgpool01                  running (virtualbox)
pgpool02                  running (virtualbox)
pgsql01                   running (virtualbox)
pgsql02                   running (virtualbox)
pgsql03                   running (virtualbox)
zabbix                    running (virtualbox)
    pgpool-II,PostgreSQL SRクラスタを構築するAnsible Playbookをダウンロード
$ git clone https://github.com/pg-monz/ansible-pgool-pgsql-cluster.git
    group_vars/all.ymlを編集
# 編集箇所
synchronous_standby_names: '192.168.1.14,192.168.1.15'
vip: 192.168.1.100
pgpool_active_ip: 192.168.1.11
pgpool_standby_ip: 192.168.1.12
pgsql_primary_ip: 192.168.1.13
pgsql_standby01_ip: 192.168.1.14
pgsql_standby02_ip: 192.168.1.15
    そのままではうまく動かない部分があったのでPlaybookを修正
# roles/spred_selinux_settings/tasks/main.ymlを新規作成
---
- name: Check if selinux is installed
  command: getenforce
  register: command_result
  ignore_errors: True

- name: Install libselinux-python
  yum: name={{ item }}
  with_items:
    - epel-release
    - libselinux-python
  when: command_result|success and command_result.stdout != 'Disabled'

# prepare.ymlにspred_selinux_settingsを追加
---
- hosts: all
  sudo: yes
  gather_facts: no
  roles:
   - spred_selinux_settings
   - spred_ssh_settings
   - spred_pgdg_rpms
   - spred_pgpool_src
   - spred_os_cmd
    Playbookを実行
$ ansible-playbook --ask-pass --ask-sudo-pass -i hosts site.yml

在中文中，可以这样表达：

Zabbix服务器的建设（Zabbix服务器的工作）。

    サーバ zabbix にログイン。後の作業は基本的にrootユーザで実行する。
$ vagrant ssh zabbix
$ su
    PostgreSQLをインストール
$ wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
$ rpm -ivh pgdg-centos94-9.4-1.noarch.rpm
$ yum install postgresql94 postgresql94-server postgresql94-contrib
$ su - postgres
$ cd /var/lib/pgsql/9.4/data
$ /usr/pgsql-9.4/bin/initdb --encoding=UTF8 --no-locale
# ユーザを root に戻す
$ service postgresql-9.4 start
    Zabbix Serverをインストール
$ wget http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm
$ rpm -ivh zabbix-release-2.4-1.el6.noarch.rpm
$ yum install zabbix zabbix-server-pgsql zabbix-server zabbix-sender zabbix-get
$ yum install zabbix-web-pgsql zabbix-web zabbix-web-japanese
    zabbixという名前のユーザ,データベースを作成
$ su - postgres
$ psql

# create user zabbix with encrypted password 'zabbix' nocreatedb nocreateuser;
# create database zabbix owner zabbix;

$ psql -U zabbix zabbix < /usr/share/doc/zabbix-server-pgsql-2.4.5/create/schema.sql
$ psql -U zabbix zabbix < /usr/share/doc/zabbix-server-pgsql-2.4.5/create/images.sql
$ psql -U zabbix zabbix < /usr/share/doc/zabbix-server-pgsql-2.4.5/create/data.sql
    Zabbixサーバ設定ファイルを編集して起動
$ vi /etc/zabbix/zabbix_server.conf

※修改部分
DB端口=5432

$ vi /etc/php.ini

※编辑部分
日期时区 = 亚洲/东京

$ service zabbix-server start
$ service httpd start

Zabbix Webページにアクセスし、初期設定ウィザードを起動

3. Configure DB connection 画面でDBの接続情報入力

数据库名称：zabbix
用户：zabbix
密码：zabbix

    pg_monz をダウンロード
$ git clone https://github.com/pg-monz/pg_monz.git
    • templateフォルダのテンプレート(.xml)をインポートする。

 

    テンプレートTemplate App PostgreSQLのマクロを編集

{$PGLOGDIR}的值为/var/log/pgsql。

    ホストを作成し、テンプレートを割り当てる。
ホスト名テンプレート名pgpool01,pgpool02Template App pgpool-IIpgsql01,pgsql02,pgsql03Template App PostgreSQL SRPostgreSQL ClusterTemplate App pgpool-II watchdog, Template App PostgreSQL SR Cluster
    ホストグループを作成する。
ホストグループ名ホスト名pgpoolpgpool01,pgpool02PostgreSQLpgsql01,pgsql02,pgsql03PostgreSQL ClusterPostgreSQL Cluster

在除了Zabbix服务器以外的所有服务器上安装Zabbix代理（操作）

    Zabbixエージェントのインストール
$ wget http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-release-2.4-1.el6.noarch.rpm
$ rpm -ivh zabbix-release-2.4-1.el6.noarch.rpm
$ yum install zabbix-agent zabbix-sender
    Zabbixエージェントの設定ファイルを編集して起動
$ vi /etc/zabbix/zabbix_agentd.conf

※下面是需要编辑的部分（请在每个服务器的主机名下面进行填写）
Server=192.168.1.20
ServerActive=192.168.1.20
Hostname=pgpool01
AllowRoot=1

    pg_monz 設定ファイル、スクリプトを配置し、Zabbixエージェントを起動
$ git clone https://github.com/pg-monz/pg_monz.git
$ cd pg_monz/pg_monz/
$ cp usr-local-etc/* /usr/local/etc
$ cp usr-local-bin/* /usr/local/bin
$ chmod +x /usr/local/bin/*.sh
$ cp zabbix_agentd.d/userparameter_pgsql.conf /etc/zabbix/zabbix_agentd.d/
$ service zabbix-agent start

流利的導入

    td-agentのインストール（サーバzabbix,pgsql01,pgsql02,pgsql03での作業）
$ curl -L https://td-toolbelt.herokuapp.com/sh/install-redhat-td-agent2.sh | sh
    fluentdプラグインのインストール（サーバpgsql01,pgsql02,pgsql03）
$ td-agent-gem install fluent-plugin-parser
$ td-agent-gem install fluent-plugin-record-reformer
    fluentdプラグインのインストール（サーバzabbix）
$ yum install gcc
$ yum install postgresql9.4-devel
$ export PATH=/usr/pgsql-9.4/bin:$PATH
$ td-agent-gem install fluent-plugin-pgjson

修改 PostgreSQL 的日志设置

    /var/lib/pgsql/9.4/data/postgresql.confを編集し、再読み込み（サーバpgsql01,pgsql02,pgsql03での作業）

※编辑内容
log_destination = ‘stderr,csvlog’
log_line_prefix = ‘[%t][%p][%c-%l][%x][%e]%q (%u, %d, %r, %a)’
log_filename = ‘postgresql.log’
log_rotation_age = 0
log_min_duration_statement = ‘1s’
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0

※编辑部分
log_destination = ‘stderr,csvlog’
log_line_prefix = ‘[%t][%p][%c-%l][%x][%e]%q (%u, %d, %r, %a)’
log_filename = ‘postgresql.log’
log_rotation_age = 0
log_min_duration_statement = ‘1s’
log_checkpoints = 打开
log_lock_waits = 打开
log_temp_files = 0

$ service postgresql-9.4 reload

修改Fluentd的配置

    /etc/td-agent/td-agent.conf に設定を追記（サーバpgsql01,pgsql02,pgsql03での作業）
<source>
  type      tail
  path      /var/log/pgsql/postgresql.csv
  pos_file   /var/log/td-agent/postgresql.log.pos
  tag       postgresql
  format    multiline
  format_firstline  /^\d{4}-\d{2}-\d{2}/
  format1   /^(?<time>[^",]*),"?(?<user_name>(?:[^",]|"")*)"?,"?(?<database_name>(?:[^",]|"")*)"?,(?<process_id>[^",]*),"?(?<connection_from>(?:[^",]|"")*)"?,(?<session_id>[^",]*),(?<session_line_num>[^",]*),"?(?<command_tag>(?:[^",]|"")*)"?,(?<session_start_time>[^",]*),(?<virtual_transaction_id>[^",]*),(?<transaction_id>[^",]*),(?<error_severity>[^",]*),(?<sql_state_code>[^",]*),"?(?<message>(?:[^"]|"")*)"?,(?<detail>[^",]*),"?(?<hint>(?:[^",]|"")*)"?,(?<internal_query>[^",]*),(?<internal_query_pos>[^",]*),(?<context>[^",]*),"?(?<query>(?:[^"]|"")*)"?,(?<query_pos>[^",]*),(?<location>[^",]*),"?(?<application_name>(?:[^",]|"")*)"?$/
</source>

<match postgresql>
  type rewrite_tag_filter
  rewriterule1  message  ^duration:                             raw.postgresql.slow_query
  rewriterule2  message  ^checkpoints\sare\soccurring\stoo\sfrequently                                                                                  postgresql.checkpoints.frequently
  rewriterule3  message  ^checkpoint\sstarting:                 postgresql.checkpoint.start
  rewriterule4  message  ^checkpoint\scomplete:                 postgresql.checkpoint.complete
  rewriterule5  message  ^automatic                             postgresql.vacuum
  rewriterule6  message  ^temporary file:                       postgresql.tempfiles
  rewriterule7  message  ^process.*detected\sdeadlock           postgresql.deadlock
  rewriterule8  message  ^process.*(still waiting|acquired)     postgresql.lockwait
  rewriterule9  message  .*                                     postgresql.others
</match>

<match raw.postgresql.slow_query>
  type parser
  remove_prefix raw
  reserve_data  yes
  key_name      message
  format        /^duration: (?<duration>[0-9\.]+) ms  statement: (?<statement>.+)$/
</match>

<match {postgresql.**}>
  type record_reformer
  renew_record false
  enable_ruby true
  tag pgsql.${tag_suffix[1]}
  <record>
    hostname ${hostname}
  </record>
</match>

<match {pgsql.**}>
  type forward
  <server>
    host 192.168.1.20
    port 29680
  </server>
  flush_interval 10s
</match>
    /etc/td-agent/td-agent.conf に設定を追記（サーバzabbixでの作業）
<source>
  type forward
  port 29680
</source>

<match {pgsql.**}>
  type          pgjson
  host          localhost
  port          5432
  sslmode       prefer
  database      fluentd
  table         fluentd
  user          postgres
  password      postgres
  time_col      time
  tag_col       tag
  record_col    record
</match>

创建用于存储日志数据的表（Zabbix服务器）。

$ su - postgres
$ psql

# CREATE DATABASE fluentd;
# ¥c fluentd
# CREATE TABLE fluentd (tag Text, time Timestamptz, record Jsonb);

在Zabbix、pgsql01、pgsql02和pgsql03上启动Fluentd。

$ service td-agent start

用于存储登录数据的表的SQL示例。

搜索慢查询（耗时长的前10项）。

# select time, record#>>'{hostname}' as host, record#>>'{user_name}' as user, record#>>'{database_name}' as db, record#>>'{duration}' as duration, record#>>'{statement}' as statement from fluentd where tag = 'pgsql.slow_query' ORDER BY (record#>'{duration}') desc LIMIT 10;
✖
广告
将在 10 秒后关闭
bannerAds