$ brew install postgresql
$ psql -V psql (PostgreSQL) 12.3
$ psql "host=aurora-postgres-116.cluster-******.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=mydb port=5432" Password for user awsuser: psql (12.3, server 11.6) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. mydb=>
本日より、Amazon Relational Database Service (RDS) for Oracle で Oracle Active Data Guard を使用したクロスリージョンリードレプリカのサポートが開始されます。Amazon RDS for Oracle により、プライマリ DB インスタンスからさまざまな AWS リージョンで物理的スタンバイ DB インスタンスを簡単に作成することが可能となります。これは Active Data Guard の構成を完璧に管理し、さまざまなリージョンで動作するプライマリ DB インスタンスとそのレプリカの間の安全なネットワーク接続を通してデータをレプリケートします。
Amazon RDS for Oracle が、クロスリージョンリードレプリカと共に管理された災害対策およびデータ近接性のサポートを開始
If you plan changes to your logging configuration, we recommend that you complete the changes before making a DB instance the source for one or more read replicas. You can modify the logging configuration for a DB instance by using the Amazon RDS procedures rdsadmin.rdsadmin_util.add_logfile and rdsadmin.rdsadmin_util.drop_logfile. For more information, see Adding Online Redo Logs and Dropping Online Redo Logs. As a best practice, we recommend that you not modify the logging configuration after read replicas are created. Modifications can cause the online redo logging configuration to get out of sync with the standby logging configuration.
Working with Oracle Read Replicas for Amazon RDS - Amazon Relational Database Service
ログファイル配信のタイミング
アクセスログの設定および使用 - Amazon CloudFront
CloudFront は、ディストリビューションのアクセスログを 1 時間に最大で数回配信します。一般的に、ログファイルには、一定期間内に CloudFront が受信したリクエストに関する情報が含まれています。CloudFront は通常、その期間のログファイルを、ログに書き込まれたイベントの発生から 1 時間以内に Amazon S3 バケットに配信します。ただし、ある期間のログファイルエントリの一部またはすべてが、最大で 24 時間遅れることもあります。ログエントリが遅れた場合、CloudFront はこれらをログファイルに保存します。そのファイル名には、ファイルが配信された日時ではなく、リクエストが発生した期間の日時が含められます。
{ "eventVersion": "1.05", "userIdentity": { "type": "AssumedRole", "principalId": "...:yohei-a", "arn": "arn:aws:sts::234567890123:assumed-role/AdminRole/yohei-a", "accountId": "234567890123" }, "eventTime": "2020-04-30T11:08:31Z", ★ "eventSource": "signin.amazonaws.com", "eventName": "SwitchRole", "awsRegion": "us-east-1", "sourceIPAddress": "72.**.***.64", "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.129 Safari/537.36", "requestParameters": null, "responseElements": { "SwitchRole": "Success" }, "additionalEventData": { "SwitchFrom": "arn:aws:sts::123456789012:assumed-role/Admin/yohei-a", "RedirectTo": "https://console.aws.amazon.com/console/home" }, "eventID": "a3a42af4-0c3b-4654-9894-5d618767ee18", ★ "eventType": "AwsConsoleSignIn", "recipientAccountId": "234567890123" }
$ aws s3 cp --recursive s3://cloudtrail-234567890123-do-not-delete/AWSLogs/234567890123/CloudTrail/us-east-1/2020/04/30/ . $ find . -name '*.gz' -print0|xargs -0 gunzip
$ find . -name '*.json' -print0|xargs -0 grep -l a3a42af4-0c3b-4654-9894-5d618767ee18 ./234567890123_CloudTrail_us-east-1_20200430T1110Z_eP2ntD241s6Psiy1.json
（中略）
{
"eventVersion": "1.05",
"userIdentity": {
"type": "AssumedRole",
"principalId": "...:yohei-a",
"arn": "arn:aws:sts::234567890123:assumed-role/AdminRole/yohei-a",
"accountId": "234567890123"
},
"eventTime": "2020-04-30T11:08:31Z", ★
"eventSource": "signin.amazonaws.com",
"eventName": "SwitchRole",
"awsRegion": "us-east-1",
"sourceIPAddress": "72.**.***.64",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.129 Safari/537.36",
"requestParameters": null,
"responseElements": {
"SwitchRole": "Success"
},
"additionalEventData": {
"SwitchFrom": "arn:aws:sts::234567890123:assumed-role/Admin/yohei-a",
"RedirectTo": "https://console.aws.amazon.com/console/home"
},
"eventID": "a3a42af4-0c3b-4654-9894-5d618767ee18", ★
"eventType": "AwsConsoleSignIn",
"recipientAccountId": "234567890123"
}
]
}
PostgreSQL で接続・クエリ・切断のログは Process ID で紐付けることができる。今回は Aurora PostgreSQL 互換 11.6 で確認した。
2020-05-10 07:13:39 UTC:3.***.**.130(43334):[unknown]@[unknown]:[27199★]:LOG: connection received: host=3.***.**.130 port=43334 2020-05-10 07:13:39 UTC:3.***.**.130(43334):awsuser@mydb:[27199★]:LOG: connection authorized: user=awsuser database=mydb SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, compression=off) 2020-05-10 07:13:39 UTC:3.***.**.130(43334):awsuser@mydb:[27199★]:LOG: disconnection: session time: 0:00:00.029 user=awsuser database=mydb host=3.***.**.130 port=43334
log_connections (boolean)
Causes each attempted connection to the server to be logged, as well as successful completion of client authentication. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off.Note
Some client programs, like psql, attempt to connect twice while determining if a password is required, so duplicate “connection received” messages do not necessarily indicate a problem.log_disconnections (boolean)
PostgreSQL: Documentation: 11: 19.8. Error Reporting and Logging
Causes session terminations to be logged. The log output provides information similar to log_connections, plus the duration of the session. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off.
log_line_prefix (string)
This is a printf-style string that is output at the beginning of each log line. % characters begin “escape sequences” that are replaced with status information as outlined below. Unrecognized escapes are ignored. Other characters are copied straight to the log line. Some escapes are only recognized by session processes, and will be treated as empty by background processes such as the main server process. Status information may be aligned either left or right by specifying a numeric literal after the % and before the option. A negative value will cause the status information to be padded on the right with spaces to give it a minimum width, whereas a positive value will pad on the left. Padding can be useful to aid human readability in log files. This parameter can only be set in the postgresql.conf file or on the server command line. The default is '%m [%p] ' which logs a time stamp and the process ID.PostgreSQL: Documentation: 11: 19.8. Error Reporting and Logging
Escape Effect Session only %a Application name yes %u User name yes %d Database name yes %r Remote host name or IP address, and remote port yes %h Remote host name or IP address yes %p Process ID no %t Time stamp without milliseconds no %m Time stamp with milliseconds no %n Time stamp with milliseconds (as a Unix epoch) no %i Command tag: type of session's current command yes %e SQLSTATE error code no %c Session ID: see below no %l Number of the log line for each session or process, starting at 1 no %s Process start time stamp no %v Virtual transaction ID (backendID/localXID) no %x Transaction ID (0 if none is assigned) no %q Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes no %% Literal % no
pg_stat_activity.aplication_name や Aurora PostgreSQL 互換のアクティビティストリームの clientApplication にアプリ名を記録したい場合は、アプリケーションから接続時に application_name パラメータをセットしてやればよい。以下は Python で psycopg2 を使って接続するサンプル。connect 時に application_name='Python sample' のようにパラメータをセットしている。
... import psycopg2 ... def get_connection(): return psycopg2.connect( application_name='Python sample', host=os.environ['PG_HOST'], dbname=os.environ['PG_DBNAME'], # mydb port=os.environ['PG_PORT'], # 5432 user=os.environ['PG_USER'], # awsuser password=os.environ['PG_PASSWORD'] ) ...
以下はアクティビティストリームを CSV に変換したサンプル。clientApplication に "psql" や "Python sample" といったアプリケーション名が記録されている。
"logTime","serverHost","remoteHost","databaseName","serviceName","dbUserName","clientApplication","commandText","rowCount" "2020-04-30 22:29:32.633692+00","172.**.**.167","3.***.**.130","mydb","Amazon Aurora PostgreSQL-Compatible edition","awsuser","psql","select count(*) from pg_class;",1 ... "2020-05-10 07:13:44.411976+00","172.**.**.167","3.***.**.130","mydb","Amazon Aurora PostgreSQL-Compatible edition","awsuser","Python sample","select * from pg_class;",342
Example Activity Event Record of a CONNECT SQL Statement
Following is an activity event record of a login with the use of a CONNECT SQL statement (command) by a psql client (clientApplication).{ （中略） "pid": 18251, "clientApplication": "psql", （中略） }Using Database Activity Streams with Aurora PostgreSQL - Amazon Aurora
databaseActivityEventList JSON Array
The audit log payload is an encrypted databaseActivityEventList JSON array. The following table lists alphabetically the fields for each activity event in the decrypted DatabaseActivityEventList array of an audit log.Using Database Activity Streams with Aurora PostgreSQL - Amazon Aurora
Field Data Type Description clientApplication string The application the client used to connect as reported by the client. The client doesn't have to provide this information, so the value can be null.
The basic connection parameters are:
- dbname – the database name (database is a deprecated alias)
- user – user name used to authenticate
- password – password used to authenticate
- host – database host address (defaults to UNIX socket if not provided)
- port – connection port number (defaults to 5432 if not provided)
Any other connection parameter supported by the client library/server can be passed either in the connection string or as a keyword. The PostgreSQL documentation contains the complete list of the supported parameters. Also note that the same parameters can be passed to the client library using environment variables.
The psycopg2 module content — Psycopg 2.8.6.dev0 documentation
application_name Specifies a value for the application_name configuration parameter.
PostgreSQL: Documentation: 12: 33.1. Database Connection Control Functions
表 27-2. pg_stat_activityビュー
https://www.postgresql.jp/document/9.2/html/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
列 型 説明 aplication_name text バックエンドに接続したアプリケーションの名前です。
#!/usr/bin/env python3 # -*- coding: utf-8 -*- import os import csv import psycopg2 import datetime now = datetime.datetime.now() current_time='{0:%Y%m%d_%H%M%S}'.format(now - datetime.timedelta(hours=1)) def get_connection(): return psycopg2.connect( application_name='Python sample', host=os.environ['PG_HOST'], dbname=os.environ['PG_DBNAME'], # mydb port=os.environ['PG_PORT'], # 5432 user=os.environ['PG_USER'], # awsuser password=os.environ['PG_PASSWORD'] ) with get_connection() as conn: with conn.cursor() as cur: cur.execute("select * from pg_class;") with open('pg_class_' + current_time + '.csv', 'w') as f: writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC) for row in cur: new_row=map(lambda s:str(s).strip(), row) writer.writerow(new_row)
$ sudo amazon-linux-extras enable BCC $ sudo yum -y install kernel-devel-$(uname -r) $ sudo yum -y install bcc
$ git clone https://github.com/brendangregg/FlameGraph
$ top -Hc
$ cd FlameGraph/ $ sudo /usr/share/bcc/tools/offcputime -df -p `pgrep -nx top` 60 > top_out.stacks $ ./flamegraph.pl --color=io --title="Off-CPU Time Flame Graph" --countname=us < top_out.stacks > top_out.svg
