- EDB
- PostgreSQL
新ツール Postgres Workload Report によるパフォーマンス診断～データベース管理の未来を共に創る！～
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
|
|
当記事はエンタープライズDB社のブログ記事の翻訳の紹介および弊社考察を加えています。
Raghavendra Rao. (2020, Aug, 24). How to secure Job scheduling with DBMS_SCHEDULER in EDB Postgres Advanced Server.
The EDB Blog.
https://www.enterprisedb.com/postgres-tutorials/how-secure-job-scheduling-dbmsscheduler-edb-postgres-advanced-server
EPAS データベースの互換性は、Oracle と同様の機能を提供しているため、開発者や運用担当者は既存の PL/SQL の開発や運用のスキルの多くを引き続き活用できます。EPAS でサポートされている ORACLE 準拠の機能の一つに、DBMS_SCHEDULER パッケージを使用したジョブがあります。
EPAS の DBMS_SCHEDULER パッケージは、Oracle と比較して部分的な実装になっています。DBMS_SCHEDULER はスケジュールされたジョブを実行するために pgAgent サービスを使用します。pgAgent は PostgreSQL のジョブスケジューリングエージェントであり、ユーザがデータベースから PL/SQL や OS シェルスクリプトを定期的に実行することができます。
Linux OS のプロセス（pgAgent）が OS ユーザーによって実行され、定期的にデータベースへの接続を行い、カタログ内に実行すべきジョブがあるかどうかを確認するアーキテクチャになります。
|
|
ジョブをスケジュールするコンポーネント：
Linux OS で EPAS 12 のジョブをスケジュールするために必要なソフトウェアは次の通りです。
以下の例では、サービスの管理に systemd を使用している Linux システムを想定しています。
前提要件に挙げたソフトウェアをインストールします。
--pgAgent インストール edb=# create extension pgagent; CREATE EXTENSION --dbms_scheduler インストール edb=# CREATE EXTENSION dbms_scheduler; CREATE EXTENSION edb=#\dx List of installed extensions Name | Version | Schema | Description ------------------+---------+------------+------------------------------------------------------ dbms_scheduler | 1.1 | sys | Creates catalog objects for DBMS_SCHEDULER package edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle edbspl | 1.0 | pg_catalog | EDB-SPL procedural language pgagent | 4.0 | pgagent | A PostgreSQL job scheduler pldbgapi | 1.1 | pg_catalog | server-side support for debugging PL/pgSQL functions plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (7 rows)
このセクションでは、データベースのスーパーユーザーとしてジョブをスケジュールする方法を見ていきます。
・スーパーユーザーとして pgAgentサービスを開始する
--Start the pgagent # systemctl start edb-pgagent-12.service
・まず、テーブルに行を INSERT するサンプルPLSQLプログラムを作成します。
1分毎にデータベースに接続されているユーザー数を収集したいとします。
最初にテーブルを作成し、次に統計情報（pg_stat_activity）を収集して
テーブルに挿入するプログラムを作成します。
--テーブルを作成 edb=# create table userinfo(at_time timestamp, total_users_in_db number(10)); CREATE TABLE --ジョブ／スケジュールから呼びだされるプログラムを定義 CREATE OR REPLACE PROCEDURE public.get_user_info () IS DECLARE BEGIN EXECUTE IMMEDIATE 'insert into public.userinfo select now(),count(*) from pg_stat_activity ;'; END;
・次に、ジョブとプログラム名を作成して、1分ごとに実行するようにスケジュールする必要があります。 まず、実行されるプログラム名を作成します。
-- プログラム名を作成
EXEC DBMS_SCHEDULER.CREATE_PROGRAM('program_logusers',
'STORED_PROCEDURE',
'"public"."get_user_info"',
0,
true,
'Program that runs every minute to insert number of users in db');
-- 有効化
EXEC DBMS_SCHEDULER.enable (name => 'program_logusers');
-- プログラムが作成されているか確認
edb=# SELECT owner, program_name, enabled FROM dba_scheduler_programs;
owner | program_name | enabled
--------------+------------------+---------
enterprisedb | program_logusers | t
(1 row)
・次に、いつ実行されるかを定義するスケジュールを作成します。
EXEC DBMS_SCHEDULER.CREATE_SCHEDULE ( schedule_name => 'every_minute', start_date => now, repeat_interval => 'FREQ=MINUTELY;', comments => 'This schedule executes every minute'); --スケジュールが作成されているか確認 edb=# SELECT owner, schedule_name FROM dba_scheduler_schedules; owner | schedule_name --------------+--------------- enterprisedb | every_minute (1 row)
・最後に、プログラムを実行するジョブを作成し、1分ごとに実行されるようにスケジュールします。
EXEC DBMS_SCHEDULER.CREATE_JOB ( job_name => 'job_loguserinfo', program_name => 'program_logusers', schedule_name => 'every_minute', enabled => true, comments => 'Job runs every minute to store users in db into a table'); -- ジョブが作成されているか確認 edb=# SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs; owner | job_name | job_class | enabled --------------+-----------------+-------------------+--------- enterprisedb | job_loguserinfo | DEFAULT_JOB_CLASS | t (1 row)
・以上でジョブは1分毎に実行されます。ログには実行された各ジョブのエントリが表示されます。
DEBUG: Creating DB connection: user=enterprisedb hostaddr=127.0.0.1 port=5444 dbname=edb DEBUG: Parsing connection information... DEBUG: Allocating new connection for the database with connection string: user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444... DEBUG: Starting job: 2 DEBUG: Creating DB connection: user=enterprisedb hostaddr=127.0.0.1 port=5444 dbname=edb DEBUG: Parsing connection information... DEBUG: Allocating new connection for the database with connection string: user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444... DEBUG: Executing SQL step 2(part of job 2) DEBUG: Parsing connection information... DEBUG: Returning the connection to the connection pool: 'user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444'... DEBUG: Parsing connection information... DEBUG: Returning the connection to the connection pool: 'user=enterprisedb dbname=edb hostaddr=127.0.0.1 port=5444'... DEBUG: Completed job: 2
・テーブルの状態を確認すると1分毎に新しい行が追加されています。
edb=# select * from userinfo ; at_time | total_users_in_db ---------------------------+------------------- 04-JUN-20 01:46:03.651164 | 10 04-JUN-20 01:47:03.677979 | 10 04-JUN-20 01:48:03.687966 | 10 (3 rows)
ほとんどの手順はスーパーユーザーの場合と同じですが、非スーパーユーザーがジョブをスケジューリングするためには、スーパーユーザーと競合しないように、そのユーザーの OS レベルで pgAgent デーモンを実行する必要があります。
非スーパーユーザーのジョブスケジューリングについては次の手順に従ってください。（「USER1」が非スーパーユーザーの名前になります）
・非スーパーユーザーでジョブを実行するための pgAgent デーモンプロセスを実行するOSユーザーを作成します。
# useradd user1
・OSユーザと同じ名前のデータベースユーザーを作成し、
pgAgent および dbms_scheduler 関連の関数／ビュー／テーブルが存在する
「PGAGENT」および「SYS」スキーマに対する権限を付与し、
非スーパーユーザーが独自のジョブ／プログラム／スケジュールを作成できるようにします。
CREATE ROLE user1 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT ALL ON SCHEMA pgagent to user1; GRANT ALL ON ALL TABLES IN SCHEMA pgagent to user1; GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO user1; GRANT ALL ON SCHEMA sys TO user1 ; GRANT ALL ON ALL TABLES IN SCHEMA sys TO user1; GRANT SELECT on sys.dba_scheduler_schedules to user1 ;
・「USER1」がテーブルとプログラムを所有しており、それを1分ごとに実行したいとします。
edb=> create table conninfo(at_time timestamp,total_conn number(3)); CREATE TABLE -- program CREATE OR REPLACE PROCEDURE public.get_conn_info () IS DECLARE BEGIN EXECUTE IMMEDIATE 'insert into public.conninfo select now(),count(*) from pg_stat_activity ;'; END;
・「USER1」のジョブ／プログラム／スケジュールを作成してみましょう。
-- プログラム名を作成
EXEC DBMS_SCHEDULER.CREATE_PROGRAM('program_logconn',
'STORED_PROCEDURE',
'"public"."get_conn_info"',
0,
true,
'Program that runs every minute to insert number of users in db');
-- 有効化
EXEC DBMS_SCHEDULER.enable (name => 'program_logconn');
-- スケジュールを作成
EXEC DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name => 'every_minute_user1',
start_date => now,
repeat_interval => 'FREQ=MINUTELY;',
comments => 'This schedule executes every minute');
-- ジョブを作成
EXEC DBMS_SCHEDULER.CREATE_JOB (job_name => 'job_logconninfo',
program_name => 'program_logconn',
schedule_name => 'every_minute_user1',
enabled => true,
comments => 'Job runs every minute to store users in db into a table');
・最後のステップとして、OSレベルでpgagentデーモンを実行し、「USER1」のジョブスケジュールを実行します。
# su - user1 -c '/usr/edb/as12/bin/pgagent hostaddr=127.0.0.1 dbname=edb user=user1 port=5444 -l 2 -s /tmp/pgagent.log &'
これで、「USER1」のジョブは、OSレベルで起動された独自のデーモン・プロセスによって実行されるようになります。これで非スーパーユーザーによるセキュアなジョブ・スケジューリング方法を実現することができます。
（以上、EDB社ブログ記事より翻訳）
DBMS_SCHEDULER によるジョブのスケジュール方法および非スーパーユーザーによるセキュアなジョブのスケジュール方法をご紹介しました。これによりサードパーティ製のスケジューラーに頼ることなく、DBのネイティブな機能だけで、非スーパーユーザーによるジョブをセキュアにスケジュールすることができます。
もし、タスクのスケジュール実行の実装方法をお悩みでしたら、DBMS_SCHEDULER の利用をご検討されてみてはいかがでしょうか。
|
|---|
2017年に中途入社。Oracle Database、EDB Postgres／PostgreSQL のサポート経験を経て、2020年からバックサポートを担当。DBとアプリケーションを繋ぐミドルウェア製品のスペシャリスト。トレンドな技術は積極的に触れるほど好奇心旺盛。最近はプロアクティブなサポートを目指して粉骨砕身。趣味はボードゲーム。...show more
■本記事の内容について
本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。
■商標に関して
・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。
EDB Postgres Workload Reportsは、Postgresデータベースのパフォーマンス診断とトラブルシューティングを強化する新しいツールです。OracleのAWRに似た詳細なレポートを提供し、データベースの問題を迅速に特定・解決できるようサポートします。本記事では概要と利用手順をご紹介します。
35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載10回目となる今回の記事では、OSS-DB Gold試験対策問題集 出版の経緯や内容を 新校長 我妻にインタビューしました。
EDB社のAjit Gadge氏を招き「PostgreSQLユーザーに捧ぐ、EDBを使ったDB機能向上とコスト削減の両立」セミナーを開催しました。DB市場の現状やトレンド、EDBの最新動向について紹介しております。アシストセッションのアーカイブ配信の視聴申し込みも可能です。ぜひご覧ください。
