What Are the Preparations for Incremental DDL Synchronization to PostgreSQL?

Prerequisites

• Currently, the following DDL operations are supported:

  Table-level synchronization supports DROP TABLE and ALTER TABLE (including ADD COLUMN, DROP COLUMN, ALTER COLUMN and RENAME COLUMN).

• DDL statements that use Using for forcible type conversion are not supported.
• Before performing the following operations, check whether a table named hw_migration_ddl_info, a function named hw_migration_ddl_function(), and a trigger named hw_migration_ddl_event exist in the public schema of the source database to be synchronized. If they exist, delete them.

Procedure

1. Connect to the database to be synchronized as a user who has permission to create event triggers.
2. Run the following statements to create a table for storing DDL information:

   DROP TABLE IF EXISTS public.hw_migration_ddl_info;
   DROP SEQUENCE IF EXISTS public.hw_migration_ddl_info_id_seq;
   CREATE TABLE public.hw_migration_ddl_info(  
   id                             bigserial primary key,  
   ddl                            text,  
   username                       varchar(64) default current_user,    
   txid                           varchar(16) default txid_current()::varchar(16),  
   tag                            varchar(64),    
   database                       varchar(64) default current_database(),    
   schema                         varchar(64) default current_schema,  
   client_address                 varchar(64) default inet_client_addr(),  
   client_port                    integer default inet_client_port(),  
   event_time                     timestamp default current_timestamp
   );

3. Run the following statements to create a function:

   CREATE OR REPLACE FUNCTION public.hw_migration_ddl_function()
       RETURNS event_trigger
       LANGUAGE plpgsql
       SECURITY INVOKER
   AS $$
       declare ddl text;
       declare real_num int;
       declare max_num int := 50000;
   begin
     if (tg_tag in ('ALTER TABLE','DROP TABLE')) then
         select current_query() into ddl; 
         insert into public.hw_migration_ddl_info(ddl, username, txid, tag, database, schema, client_address, client_port, event_time)
         values (ddl, current_user, cast(txid_current() as varchar(16)), tg_tag, current_database(), current_schema,  inet_client_addr(), inet_client_port(), current_timestamp);
         select count(id) into real_num from public.hw_migration_ddl_info;
         if real_num > max_num then
           if current_setting('server_version_num')::int<100000 then
             delete from public.hw_migration_ddl_info where id<(select min(id)+1000 from public.hw_migration_ddl_info) and not exists (select 0 from pg_locks l join pg_database d on l.database=d.oid where d.datname=current_catalog and pid<>pg_backend_pid() and locktype='relation' and relation=to_regclass('public.hw_migration_ddl_info_pkey')::oid and mode='RowExclusiveLock');
           else 
             delete from public.hw_migration_ddl_info where id<(select min(id)+1000 from public.hw_migration_ddl_info) and (xmax=0 or coalesce(txid_status(xmax::text::bigint), '')<>'in progress');
           end if;
         end if;
     end if;
   end;
   $$;

4. Run the following statements to grant necessary permissions to the objects created in 2 and 3:

   GRANT USAGE ON SCHEMA public TO public; GRANT SELECT,INSERT,DELETE ON TABLE public.hw_migration_ddl_info TO public; GRANT SELECT,USAGE ON SEQUENCE public.hw_migration_ddl_info_id_seq TO public; GRANT EXECUTE ON FUNCTION public.hw_migration_ddl_function() TO public;

5. Run the following statement to create a DDL event trigger:

   CREATE EVENT TRIGGER hw_migration_ddl_event ON ddl_command_end EXECUTE PROCEDURE public.hw_migration_ddl_function();

6. Run the following statement to set the created event trigger to enable:

   ALTER EVENT TRIGGER hw_migration_ddl_event ENABLE ALWAYS;

7. Create a synchronization task with PostgreSQL as the source.
8. After the synchronization task is complete, if you do not need to execute the synchronization task with PostgreSQL as the source, run the following statements to delete the created tables, functions, and triggers:

   DROP EVENT trigger hw_migration_ddl_event;
   DROP FUNCTION public.hw_migration_ddl_function();
   DROP TABLE public.hw_migration_ddl_info;