Aurora PostgreSQL 互換のアクティビティストリームの clientApplication にアプリ名をセットする

pg_stat_activity.aplication_name や Aurora PostgreSQL 互換のアクティビティストリームの clientApplication にアプリ名を記録したい場合は、アプリケーションから接続時に application_name パラメータをセットしてやればよい。以下は Python で psycopg2 を使って接続するサンプル。connect 時に application_name='Python sample' のようにパラメータをセットしている。

• yoheia/postgres_query_sample.py at master · yoheia/yoheia · GitHub

...

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.

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.

Using Database Activity Streams with Aurora PostgreSQL - Amazon Aurora

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ビュー

列	型	説明
aplication_name	text	バックエンドに接続したアプリケーションの名前です。

https://www.postgresql.jp/document/9.2/html/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

• yoheia/postgres_query_sample.py at master · yoheia/yoheia · GitHub

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