Migration Synchronization of PostgreSQL Sequence

This short article explains the sample code for setting SQL for sequences in PostgreSQL.

By digoal

Background

Currently, Alibaba Cloud DTS does not support the migration of sequence values. When DTS is used to migrate data from a PostgreSQL database, you must synchronize the sequence values from the source database to the target database manually after the data migration is completed. Otherwise, the sequence values generated by the business that uses the sequence will return to the previous version, and UK PK generated by the sequence will also report conflicts.

For PostgreSQL 9.6 and earlier versions, the sample code for setting SQL for sequences obtained from the original database is listed below:

do language plpgsql $$
declare
  nsp name;
  rel name;
  val int8;
begin
  for nsp,rel in select nspname,relname from pg_class t2 , pg_namespace t3 where t2.relnamespace=t3.oid and t2.relkind='S'
  loop
    execute format($_$select last_value from %I.%I$_$, nsp, rel) into val;
    raise notice '%', 
    format($_$select setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
  end loop;
end;
$$;


NOTICE:  select setval('public."Seq"'::regclass, 3);
NOTICE:  select setval('"P12"."Seq"'::regclass, 4);
DO

For PostgreSQL 10 and later versions, the sample code for setting SQL for sequences obtained from the original database is listed below:

postgres=> select format($$  
select setval('%s.%s'::regclass, %s);$$,   
relnamespace::regnamespace::text,   
oid::regclass::text,   
coalesce(pg_sequence_last_value(oid),   
  (pg_sequence_parameters(oid)).start_value) + 1)   
from pg_class where relkind='S';  
  
                   format                      
---------------------------------------------  
 select setval('public.seq'::regclass, 103);  
 select setval('public."Seq"'::regclass, 3);  
(2 rows)

Run the following commands in the target database:

 select setval('public.seq'::regclass, 103); 
 select setval('public."Seq"'::regclass, 3);

0 0 0

Share on

: digoal

282 posts | 24 followers
Follow

Community

Migration Synchronization of PostgreSQL Sequence

Background

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

Oracle Database Migration Solution

Database Migration Solution

Cloud Migration Solution

PolarDB for PostgreSQL