Synchronizing Sequence Values for a Synchronization Task with GaussDB Distributed Serving as the Source Database

Procedure

1. Log in to the distributed GaussDB instance as the user used when you tested connectivity between the DRS instance and the distributed GaussDB instance.
2. Run the following statement to query the tables that have management sequences and their associated sequences in the database:

   set search_path to '';select d.refobjid::regclass::text as tablename, d.objid::regclass::text as seqname from pg_depend d where d.refclassid='pg_class'::regclass and d.objid in (select oid from pg_class where relkind in ('S','L')) UNION select a.adrelid::regclass::text,d.refobjid::regclass::text from pg_attrdef a JOIN pg_depend d ON (a.oid=d.objid) where d.refobjid in (select oid from pg_class where relkind in ('S','L')) and d.classid='pg_attrdef'::regclass order by tablename, seqname;

3. For the sequence associated with each table to be synchronized, run the following statement on the CN node of the source database as a user with the MONADMIN or SYSADMIN permission to query next_new_val corresponding to all nodes:

   execute direct on all $$select last_value, increment_by, cache_value, (last_value + increment_by * cache_value) as next_new_val from '<seqname>'$$;

   In the preceding command, <seqname> indicates the name of the sequence with schema in the query result of 2. The value of next_new_val in the query result is last_value + increment_by * cache_value. Ensure that the sequence value is unique.

4. For the sequence associated with each table to be synchronized, run the following statement to set a new sequence value in the destination database:

   select setval('<seqname>', <target_value>);

   In the preceding command, <target_value> indicates the maximum value (auto-increment sequence, increment_by > 0) or minimum value (auto-decrement, increment_by < 0) of next_new_val queried in 3.

5. Check the sequence value of the destination database.

   For the sequence associated with each table to be synchronized, run the following statement to obtain the new value of the sequence in the destination database:

   select nextval('<seqname>');

   Check whether the new value is greater than or equal to (auto-increment sequence, increment_by > 0) or less than or equal to (auto-decrement, increment_by < 0) the sequence values of all nodes in the source database.