ApsaraDB RDS:イベントトリガーを使用して、DDLごみ箱、ファイアウォール、および増分同期機能を実装する

DDLのごみ箱

1. 次のステートメントを実行して、必要なテーブル、関数、およびトリガーを作成します。

   /* external/rds_ddl_pulication/rds_ddl_pulication -- 1.0.sql * /
   
   -- スキーマの作成
   dts_auditが存在しない場合はスキーマを作成します。-ddlレコードのテーブルを作成する
   CREATE TABLE IF NOT EXISTS dts_audit.dts_tb_ddl_command (イベントテキスト、タグテキスト, classid oid, objid oid, objsubid int,
   object_typeテキスト、schema_nameテキスト、object_identityテキスト、is_extension bool、クエリテキスト、username text default current_user、datname text default current_database() 、client_addr inet default inet_client_addr() 、crt_time timestamp default now()
   );
   -- イベントトリガーの関数を作成する
   createまたはreplace関数dts_audit.dts_func_ddl_command() は $$としてevent_triggerを返します
   v1テキストを宣言する。is_superuser bool = false;
   rレコード;
   始める
       -スーパーユーザーからのddlコマンドを記録しません 
       pg_catalog.pg_roles uからu.rolsuperをis_superuserに選択します。ここで、u.rolname = SESSION_USER;
       is_superuserの場合、
           戻る;
       end if;
       pid=pg_backend_pid() であるpg_stat_activityからv1にクエリを選択します。
       -- RAISE NOTICE 'ddl event :%、コマンド: % '、tg_event、tg_tag;
       -- NB: ddl_command_endはdropステートメントの詳細を収集できないため、sql_dropを使用します。
       TG_EVENT='ddl_command_end 'の場合、
           SELECT * into r FROM pg_event_trigger_ddl_commands(); 
           r.classid > 0の場合、
               insert into dts_audit.dts_tb_ddl_command(event, tag, classid, objid, objsubid, object_type, schema_name, object_identity, is_extension, query)
               値 (TG_EVENT、TG_TAG、r.classid、r.objid、r.objsubid、r.object_type、r.schema_name、r.object_identity、r.in_extension、v1);
            end if; 
       end if;
       TG_EVENT='sql_drop' の場合、
           -繰り返し収集を避けるために、「ALTER TABLE」と「ALTER FOREIGN TABLE」をフィルタリングしました
           もしTG_TAG! ='ALTER TABLE' とTG_TAG! =「ALTER FOREIGN TABLE」の場合
               SELECT * into r FROM pg_event_trigger_dropped_objects();
               insert into dts_audit.dts_tb_ddl_command(event, tag, classid, objid, objsubid, object_type, schema_name, object_identity, is_extension, query)
               値 (TG_EVENT、TG_TAG、r.classid、r.objid、r.objsubid、r.object_type、r.schema_name、r.object_identity、'f' 、v1);
           end if; 
       終了if;
   終了;
   $$ language plpgsql strict;
   -- ddl_command_endイベントトリガー
   イベントトリガーを作成するpg_get_ddl_command on ddl_command EXECUTE PROCEDURE dts_audit.dts_func_ddl_command();
   -- pg_get_ddl_dropイベントトリガー
   イベントトリガーを作成するpg_get_ddl_drop on sql_drop EXECUTE PROCEDURE dts_audit.dts_func_ddl_command();
   -すべてのユーザーに権限を付与する
   SCHEMA dts_auditからパブリックへの使用を承認します。選択を許可し、テーブルに挿入して公開します。

   説明 上記のステートメントを実行すると、DDLステートメントがdts_audit.dts_tb_ddl_commandテーブルに記録されます。
2. DDL文を実行して、それがdts_audit.dts_tb_ddl_commandテーブルに記録されているかどうかをテストします。

   テーブルtb_test(id int) を作成します。select * from dts_audit.dts_tb_ddl_command; 

   

DDLファイアウォール

必要に応じてイベントトリガーを作成し、ddl_command_startイベントを使用して特定のDDL文の実行をブロックできます。

1. トリガー関数を作成します。

   関数の作成または置き換えabort1()
     RETURNS event_trigger
    LANGUAGE plpgsql
     AS $$
   開始
     current_user = 'test1' の場合、
       RAISE EXCEPTION 'event :%, コマンド: % ', tg_event, tg_tag;
     end if;
    エンド;
   $$; 

2. テーブルを作成または削除するDDLステートメントをブロックするトリガーを作成します。

   タグIN ('CREATE TABLE', 'DROP TABLE') がプロシージャabort1() を実行したときにddl_command_startにイベントトリガーbを作成します。

3. ユーザーtest1としてRDSインスタンスにログインし、テーブルを作成します。
   説明 DDLステートメントがブロックされています。

増分同期

パブリケーション側では、実行されたDDL文はdts_audit.dts_tb_ddl_commandテーブルに格納されます。 パブリケーション側からサブスクリプション側にレコードを同期できます。

1. パブリケーション側でパブリケーションを作成します。

   テーブル専用の出版物my_ddl_publicationを作成します。

2. サブスクリプション側で同じテーブルを作成します。
   dts_auditが存在しない場合は

   スキーマを作成します。CREATE TABLE IF NOT EXISTS dts_audit.dts_tb_ddl_command (イベントテキスト、タグテキスト, classid oid, objid oid, objsubid int,
   object_typeテキスト、schema_nameテキスト、object_identityテキスト、is_extension bool、クエリテキスト、ユーザー名テキスト、datnameテキスト、client_addr inet、crt_time timestamp ); 

3. サブスクリプション側でサブスクリプションを作成します。

   サブスクリプションの作成my_ddl_subscriptin CONNECTION 'host=*** port=*** user=*** password=*** dbname=**'PUBLICATION my_ddl_publication;

   説明 RDSインスタンスのwal_levelパラメーターがlogicalに設定されていることを確認します。 ApsaraDB for RDSコンソールの [パラメーター] ページでパラメーター値を変更し、インスタンスを再起動して変更を有効にすることができます。 詳細については、「論理サブスクリプション機能の使用」をご参照ください。

   例：

   CREATE SUBSCRIPTION my_ddl_subscriptin CONNECTION 'host=pgm-bpxxxxx.pg.rds.aliyuncs.comポート=1433 user=test1 password=xxxxx dbname=testdb1' PUBLICATION my_ddl_publication;

4. DDL文の増分同期を実行するために、サブスクリプション側でdts_audit.dts_tb_ddl_commandテーブルのトリガーを作成します。