PostgreSQLのfilebeatモジュールをRDS用に設定

RDS PostgreSQLのlog_line_prefixは変更できず、%t:%r:%u@%d:[%p]:になっている。

simulate

POST /_ingest/pipeline/_simulate
{ "pipeline": 
  {
  "description": "Pipeline for parsing PostgreSQL logs.",
  "processors": [
    {
      "grok": {
        "field": "message",
        "ignore_missing": true,
        "patterns": [
          "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}:%{IPORHOST:postgresql.log.clientip}:%{USERNAME:postgresql.log.user}@%{HOSTNAME:postgresql.log.database}:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: %{GREEDYDATA:log_message}",
          "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}:%{IPORHOST:postgresql.log.clientip}:%{USERNAME:postgresql.log.user}@%{HOSTNAME:postgresql.log.database}:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: duration: %{NUMBER:postgresql.log.duration} ms  statement: %{MULTILINEQUERY:postgresql.log.query}",
          "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}::@:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: %{GREEDYDATA:log_message}",
          "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}::@:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: duration: %{NUMBER:postgresql.log.duration} ms  statement: %{MULTILINEQUERY:postgresql.log.query}"
        ],
        "pattern_definitions": {
          "LOCALDATETIME": "[-0-9]+ %{TIME}",
          "GREEDYDATA": ".*",
          "MULTILINEQUERY" : "(.|\n|\t)*?;$"
        }
      }
    },
    {
      "date": {
        "field": "postgresql.log.timestamp",
        "target_field": "@timestamp",
        "formats": [
          "yyyy-MM-dd HH:mm:ss.SSS"
        ],
        "ignore_failure": true
      }
    }
  ],
  "on_failure": [
    {
      "set": {
        "field": "error.message",
        "value": "{{ _ingest.on_failure_message }}"
      }
    }
  ]
},
  "docs" : [
    {
      "_source" : {
        "message" : "2018-06-14 23:11:30 UTC::@:[3707]:LOG:  checkpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=0.101 s, sync=0.001 s, total=0.111 s; sync files=1, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16420 kB"
      }
    }
  ]
}

pipeline.json

{
  "description": "Pipeline for parsing PostgreSQL logs.",
  "processors": [
    {
      "grok": {
        "field": "message",
        "ignore_missing": true,
        "patterns": [
                "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}:%{IPORHOST:postgresql.log.clientip}:%{USERNAME:postgresql.log.user}@%{HOSTNAME:postgresql.log.database}:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: %{GREEDYDATA:log_message}",
                "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}:%{IPORHOST:postgresql.log.clientip}:%{USERNAME:postgresql.log.user}@%{HOSTNAME:postgresql.log.database}:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: duration: %{NUMBER:postgresql.log.duration} ms  statement: %{MULTILINEQUERY:postgresql.log.query}",
                "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}::@:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: %{GREEDYDATA:log_message}",
                "%{LOCALDATETIME:postgresql.log.timestamp} %{WORD:postgresql.log.timezone}::@:\\[%{INT:postgresql.log.process_id}\\]:%{WORD:postgresql.log.level}: duration: %{NUMBER:postgresql.log.duration} ms  statement: %{MULTILINEQUERY:postgresql.log.query}"
        ],
        "pattern_definitions": {
          "LOCALDATETIME": "[-0-9]+ %{TIME}",
          "GREEDYDATA": ".*",
          "MULTILINEQUERY" : "(.|\n|\t)*?;$"
        }
      }
    },
    {
      "date": {
        "field": "postgresql.log.timestamp",
        "target_field": "@timestamp",
        "formats": [
          "yyyy-MM-dd HH:mm:ss.SSS"
        ],
        "ignore_failure": true
      }
    }
  ],
  "on_failure": [
    {
      "set": {
        "field": "error.message",
        "value": "{{ _ingest.on_failure_message }}"
      }
    }
  ]
}

pipelineの確認

GET _ingest/pipeline/filebeat-6.3.0-postgresql-log-pipeline

クラスメソッドさんのESのIngest Nodeを使用したELB可視化を参考に手を動かしたときのメモ

ESお勉強中。

いんじぇすとのーど?

という状態だったのでまずこれまたクラスメソッドさんの記事が参考になった dev.classmethod.jp

Elasticsearch自体でインデックスする前にデータ変換/加工する機能となります。

processor

www.elastic.co

processor一覧はこちら。ちょっと気づかなかったのだけど、processor一覧は右手の目次あたりにある。

自分の環境では、デフォルトではuser_agentのprocessorはなかったため elasticsearch-plugin install ingest-user-agentでインストールした。 pluginを使用するにはESの再起動が必要っぽい。(たぶん・・・)

simulate

ついでにsimulateも試してみたかったので、

Application Load Balancer のアクセスログ - Elastic Load Balancing

こちらからHTTPS エントリ例でログ例を使って以下のようにkibanaから実行。

POST /_ingest/pipeline/_simulate
{
  "pipeline": 
  {
  "description" : "AWS Elastic Load Balancer Access log",
  "processors": [
    {
    "grok": {
      "field": "message",
      "patterns":[ "%{NOTSPACE:type} %{TIMESTAMP_ISO8601:timestamp} %{NOTSPACE:elb} %{IP:clientip}:%{INT:clientport:int} (?:(%{IP:targetip}:?:%{INT:targetport:int})|-) %{NUMBER:request_processing_time:float} %{NUMBER:target_processing_time:float} %{NUMBER:response_processing_time:float} %{INT:elb_status_code:int} %{INT:target_status_code:int} %{INT:received_bytes:int} %{INT:sent_bytes:int} \"(?:%{WORD:verb} %{URIPROTO:proto}://?(?:%{URIHOST:urihost})?(?:%{URIPATH:path}(?:%{URIPARAM:params})?)?(?: HTTP/%{NUMBER:httpversion})?|%{DATA:rawrequest})\" \"%{DATA:agent}\"" ],
      "ignore_missing": true
    }
   },{
    "remove":{
      "field": "message"
    }
  }, {
    "user_agent": {
      "field": "agent",
      "target_field": "user_agent",
      "ignore_failure": true
    }
  }, {
    "remove": {
      "field": "agent",
      "ignore_failure": true
    }
  }]
  },
  "docs" : [
    {
      "_source" : {
        "message" : "https 2016-08-10T23:39:43.065466Z app/my-loadbalancer/50dc6c495c0c9188 192.168.131.39:2817 10.0.0.1:80 0.086 0.048 0.037 200 200 0 57 \"GET https://www.example.com:443/ HTTP/1.1\" \"curl/7.46.0\" ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2 arn:aws:elasticloadbalancing:us-east-2:123456789012:targetgroup/my-targets/73e2d6bc24d8a067 \"Root=1-58337281-1d84f3d73c47ec4e58577259\" www.example.com arn:aws:acm:us-east-2:123456789012:certificate/12345678-1234-1234-1234-123456789012"
      }
    }
  ]
}

これの実行結果で、うまく処理できているのが分かる。ふむふむ。

PUT /_ingest/pipeline/elblog
{
  "processors": [
    {
    "grok": {
      "field": "message",
      "patterns":[ "%{NOTSPACE:type} %{TIMESTAMP_ISO8601:timestamp} %{NOTSPACE:elb} %{IP:clientip}:%{INT:clientport:int} (?:(%{IP:targetip}:?:%{INT:targetport:int})|-) %{NUMBER:request_processing_time:float} %{NUMBER:target_processing_time:float} %{NUMBER:response_processing_time:float} %{INT:elb_status_code:int} %{INT:target_status_code:int} %{INT:received_bytes:int} %{INT:sent_bytes:int} \"(?:%{WORD:verb} %{URIPROTO:proto}://?(?:%{URIHOST:urihost})?(?:%{URIPATH:path}(?:%{URIPARAM:params})?)?(?: HTTP/%{NUMBER:httpversion})?|%{DATA:rawrequest})\" \"%{DATA:agent}\"" ],
      "ignore_missing": true
    }
   },{
    "remove":{
      "field": "message"
    }
  }, {
    "user_agent": {
      "field": "agent",
      "target_field": "user_agent",
      "ignore_failure": true
    }
  }, {
    "remove": {
      "field": "agent",
      "ignore_failure": true
    }
  }]
}

これで"acknowledged": trueが返ると設定成功。 設定を見るには、GET _ingest/pipeline/elblogで見れる。

CentOS7にcakePHP3.6.4を構築したときのメモ

自宅PCでvagrant使ってCentOS7にcakephpを試してみようと思ったら、詰まったので手順メモ。

詰まったのはデフォルトのphpが5.4だったので、5.6に出来てなかったため。

CentOS7のPHPのバージョンを5.4.xから5.6.x/7.0.x/7.1.xにバージョンアップする

参考通りyum remove php-*phpを消す。

php56をインストール。 yum install php56 `

Quick Start Guide - 3.6

これ通りにcakephpをインストールして、bin/cake serverすると以下のエラー

PHP Fatal error: Class 'PDO' not found in /home/php/myapp/vendor/cakephp/cakephp/src/Database/Type.php on line 103

そのため`yum install php56-pdo‘でインストールした。

そしたら今度はドライバーがねぇってこのstackoverflowの質問通りのエラーが出た。

mysql - CakePHP 3 is NOT able to connect to the database due to a missing PHP extension - Stack Overflow

なので、yum install php56-php-mysqlndでインストールしてようやくbin/cake serverが成功。

jsonファイルを整形するツールのメモ。

jsonファイルについてのメモ。

jq

  • まずはこれ。まだ使い方なれてない。json版のgrep,awk的な。

jsondiff

  • diffです。

github.com

json2yaml

  • json読みづらいからjsonからyamlにできるこいつで。
  • json2yamlだけど、こいつで、yaml2jsonもある。

github.com

yq

  • -yでyamlのjq的な。
  • yamlにして読みやすくしたけどjqみたいにほしい値だけ取りたいよなと。

github.com

pgwatch2を試したメモ

監視系を色々調べてpgwatch2というのがあったのでそれを試したメモ。 あーそんなに良くはないかも、と思いつつたまにあーあれってそういえば手順残してないしくったーってのあるか一応メモっておく。

dockerでいれれば試しできるけど、influxdbとGrafanaの試しも兼ねたかったというところで、自分で入れてく手順。

GitHub - cybertec-postgresql/pgwatch2: PostgreSQL metrics monitor/dashboard

必要物資。

# pgwatch2
yum install -y mlocate git python36.x86_64 python36-pip
git clone https://github.com/cybertec-postgresql/pgwatch2.git

# influxdb
wget https://dl.influxdata.com/influxdb/releases/influxdb-1.5.1.x86_64.rpm
sudo yum -y localinstall influxdb-1.5.1.x86_64.rpm

# Grafana (特にバージョンにこだわったわけじゃない)
sudo yum install https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-5.0.3-1.x86_64.rpm

pgwatch2はGoで書かれていて、監視対象のDBにconfig入れて実行クエリを保存している。 なんでこんなんにしたのかちょっと疑問ではあるのだけど。手元にsqlファイル置いてではない理由を知りたい・・・

なので設定を入れる。その為のユーザー。

export PGHOST=
export PGPASSWORD=

psql -U postgres -c "create user pgwatch2_grafana password 'password'"
psql -U postgres -c "grant pgwatch2_grafana to postgres;"
psql -U postgres -c "create database pgwatch2_grafana owner pgwatch2_grafana"

psql -U postgres -c "create user pgwatch2 password 'password'"
psql -U postgres -c "grant pgwatch2 to postgres;"
psql -U postgres -c "create database pgwatch2 owner pgwatch2"

設定投入。 plpythonuはRDSでは使えないので、RDSにやるとエラー。 extraに必要らしいので、Grafanaで設定時にexhaustiveを選ぶとエラーになる。(後述)

# shared_preload_libraryにpg_stat_statementが入っている必要がある。
psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/stat_activity_wrapper.sql mydb

psql -U postgres -c "CREATE EXTENSION pg_stat_statements"
psql -U postgres -f pgwatch2/sql/datastore_setup/config_store.sql pgwatch2
psql -U postgres -f pgwatch2/sql/datastore_setup/metric_definitions.sql pgwatch2
psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/stat_activity_wrapper.sql mydb
psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/stat_statements_wrapper.sql mydb
psql -U postgres -f pgwatch2/sql/metric_fetching_helpers/table_bloat_approx.sql mydb

ifluxdbにユーザー作って、httpのauthを有効にしてやる。

service influxdb start
service influxdb status

influx
CREATE USER pgwatch2 WITH PASSWORD 'password' WITH ALL PRIVILEGES
SHOW USERS

/etc/influxdb/influxdb.confの設定ファイル修正

---
[http]
  # Determines whether HTTP endpoint is enabled.
  # enabled = true

  # The bind address used by the HTTP service.
  # bind-address = ":8086"

  # Determines whether user authentication is enabled over HTTP/HTTPS.
  auth-enabled = true

/etc/grafana/grafana.iniの設定。

[database]
# You can configure the database connection by specifying type, host, name, user and password
# as seperate properties or as on string using the url propertie.

# Either "mysql", "postgres" or "sqlite3", it's your choice
type = postgres
host = xxx
name = pgwatch2_grafana
user = pgwatch2_grafana
# If the password contains # or ; you have to wrap it with triple quotes. Ex """#password;"""
password = password

起動設定

service influxdb start
service influxdb status
chkconfig influxdb on

service grafana-server start
service grafana-server status
chkconfig add grafana-server
chkconfig grafana-server on

GrafanaUIにいって、Data Sourceにinfluxdb設定。 設定箇所はこんな感じ。

Name:influxdb
Type:influxdb
URL:http://localhost:8086
Access:proxy
Database:pgwatch2
User:pgwatch2
password:

pgwatch2用のweb UI。cherrypyというフレームワークだそう。 adminはpgwatch2の設定ファイル通り。 監視対象DBとinfluxdbの認証情報がいる。

pip3 install -U -r webpy/requirements.txt 
cd webpy

python3 web.py \
--admin-user admin \
--admin-password pgwatch2admin \
--host xxxx \
--database pgwatch2 \
--user pgwatch2 \
--password password \
--influx-host localhost \
--influx-user pgwatch2 \
--influx-password password \
--influx-database pgwatch2

起動後、pgwatch2の設定画面が8080のポートで見れるので、 /dbsに行って、監視対象のDBを入れる。

そして、メトリックコレクター起動

# install Go
wget https://storage.googleapis.com/golang/go1.9.2.linux-amd64.tar.gz
tar -C /usr/local -xzf go1.9.2.linux-amd64.tar.gz
export PATH=$PATH:/usr/local/go/bin

# compile the gatherer daemon
cd pgwatch2
./build_gatherer.sh


# collecting
./pgwatch2 \
--host=xxxxx \
--user=pgwatch2 \
--password=password \
--ihost=localhost \
--iuser=pgwatch2 \
--ipassword=password \

これでOK。githubにあるpgwatch2のダッシュボードをGrafanaにインポートしてあげると、メトリックが可視化されている。 ただし、Aurora PostgreSQLにこれやると以下のエラー。

2018/03/24 11:37:20 11:37:20.065 ERRO MetricsFetcher: failed to fetch metrics for 'aurora', metric 'replication': pq: Function pg_stat_get_wal_senders() is currently not supported in Aurora
2018/03/24 11:37:20 11:37:20.176 ERRO MetricsFetcher: failed to fetch metrics for 'aurora', metric 'cpu_load': pq: function public.get_load_average() does not exist
2018/03/24 11:37:20 11:37:20.306 ERRO MetricsFetcher: failed to fetch metrics for 'aurora', metric 'wal': pq: Function pg_current_xlog_location() is currently not supported for Aurora

pgwatch2/metric_definitions.sql at master · cybertec-postgresql/pgwatch2 · GitHub

こちらの設定ファイルから、以下のコメントのSQLが実行できないよう。

  • / replication /
  • / cpu_load /
  • / wal /

nao3-nao3.hatenablog.com

こちらにメモった通りで、 pg_stat_get_wal_senders、pg_current_xlog_locationあたりは使えないらしいAurora get_load_averageはplpythonuがないのでだめ。 なので、こいつらは設定SQL投げるまえにコメントアウトしてあげればよいか。いらないしAuroraの時。

githubから最新版のバージョンを見つけてダウンロードするテクニックのメモ。

GitHub - cybertec-postgresql/pgwatch2: PostgreSQL metrics monitor/dashboard

こちらのやり方をメモ。

INFLUX_LATEST=$(curl -so- https://api.github.com/repos/influxdata/influxdb/tags | grep -Eo '"v[0-9\.]+"' | grep -Eo '[0-9\.]+' | sort -nr | head -1)
wget https://dl.influxdata.com/influxdb/releases/influxdb_${INFLUX_LATEST}_amd64.deb

https://api.github.com/repos/influxdata/influxdb/tagsのようにreposのあとに、アカウント名/repo名/tagsで jsonでrepoのバージョンやDLのURLが取れるようになってる。(知らなかった)

curl-s--silentで、-o-でstdoutにストリーム強制らしい。なんでこれ付けてるかはわからんけど。 curl -so- https://api.github.com/repos/influxdata/influxdb/tags | grep -Eo '"v[0-9\.]+"'だけだと結果が以下

"v1.5.1"
"v1.5.0"
"v1.4.3"
"v1.4.2"
"v1.4.1"
"v1.4.0"
"v1.3.9"
"v1.3.8"
"v1.3.7"
"v1.3.6"
"v1.3.5"
"v1.3.4"
"v1.3.3"

もう1発grep -Eo '[0-9\.]+'でその後バージョン取り出してる。

以下、Prometeusで試した。

PROMETEUS_LATEST=$(curl -so- https://api.github.com/repos/prometheus/prometheus/tags  | grep -Eo '"v[0-9\.]+"' | grep -Eo '[0-9\.]+' | sort -nr | head -1 )
wget https://github.com/prometheus/prometheus/releases/download/v${PROMETEUS_LATEST}/prometheus-${PROMETEUS_LATEST}.linux-amd64.tar.gz

Aurora PostgreSQLの制限のメモ

Auroraの仕組み上、恐らくユーザーで変更不可能になってたり、使用できないものについてメモ。

パラメータグループのAurora PostgreSQLを見るとfalseになってるのが、RDS PostgreSQLではTrue

  • checkpoint_timeout
  • effective_io_concurrency
  • hot_standby_feedback
  • max_standby_archive_delay
  • max_standby_streaming_delay

また、以下のパラメータはクラスターパラメータにもインスタンスのパラメータグループからも見えないです。

  • fsync
  • full_page_writes

Auroraに接続してみると、設定はこうなってた。

mydb=> show full_page_writes;
 full_page_writes 
------------------
 off
(1 row)

mydb=> show fsync;
 fsync 
-------
 on
(1 row)

また、pg_stat_replicationとpg_stat_wal_receiverは使用できないようです。

mydb=> SELECT * FROM pg_stat_replication;
ERROR:  Function pg_stat_get_wal_senders() is currently not supported in Aurora
mydb=> 
mydb=> SELECT * FROM pg_stat_wal_receiver;
ERROR:  Function pg_stat_get_wal_receiver() is currently not supported in Aurora