How to keep PostgreSQL sequences' value in sync via script/automation

 Sample scripts to demonstrate how to generate DDLs to keep sequences in sync between 2 PostgreSQL databases.

One assumption is made that both database contains same sequence definitions, and the only differences are these last_value for these sequences.

Method 1 - Generate DDL on source

1. In order to retrieve last_value for different sequence, create following helper function.

create or replace function get_sequence_last_value 
(schema_name name, sequence_name name)
returns table (sequence_value bigint)
language 'plpgsql'
AS
$$
begin
   return query execute format ('select last_value from %I.%I',schema_name,sequence_name);
end;
$$;

Test the function, make sure it works.

mytest=# select get_sequence_last_value('public','s1');
 get_sequence_last_value
-------------------------
                      35
(1 row)

2. Generate DDL scripts.

select 'alter sequence '||seq.schema_name||'.'||seq.sequence_name||' restart with '||v.sequence_value||';'
from
(select s.nspname schema_name, c.relname sequence_name
       from pg_class c, pg_namespace s
       where c.relnamespace=s.oid 
         and c.relkind='S'
         and s.nspname in ('public')) seq 
join lateral get_sequence_last_value(seq.schema_name,seq.sequence_name) v
on true
order by seq.schema_name,seq.sequence_name;

Sample output:

mytest=# select 'alter sequence '||seq.schema_name||'.'||seq.sequence_name||' restart with '||v.sequence_value||';'
mytest-# from
mytest-# (select s.nspname schema_name, c.relname sequence_name
mytest(#        from pg_class c, pg_namespace s
mytest(#        where c.relnamespace=s.oid
mytest(#          and c.relkind='S'
mytest(#          and s.nspname in ('public')) seq
mytest-# join lateral get_sequence_last_value(seq.schema_name,seq.sequence_name) v
mytest-# on true
mytest-# order by seq.schema_name,seq.sequence_name;
                          ?column?
-------------------------------------------------------------
 alter sequence public.s1 restart with 35;
 alter sequence public.s2 restart with 28;
 alter sequence public.sporting_event_id_seq restart with 1;
(3 rows)

3. Apply these DDLs to the target database

Method 2 - Reset sequence values via dblink

In below script, it create dblink, retrieve latest value from over the dblink and set the restart value for these sequences in the current database.

do
$$
declare
  seq_value bigint;
  rec_seq record;
  cur_seq cursor for 
       select s.nspname schema_name, c.relname sequence_name
       from pg_class c, pg_namespace s
       where c.relnamespace=s.oid 
         and c.relkind='S'
         and s.nspname in ('public');
begin
       begin
         execute 'SELECT dblink_connect($1,$2)' using 'myconn','dbname=mytest';
       exception when others then 
         raise notice 'Connection creation exception, safely ignore if subsequent code works';
       end;
  
       open cur_seq;
       loop
         fetch cur_seq into rec_seq;
         exit when not found;
         raise notice 'Processing: %', rec_seq.schema_name||'.'||rec_seq.sequence_name;
         -- process for each sequence found on the target
         begin
           select id into seq_value 
           from dblink('myconn', 'select last_value from '||rec_seq.schema_name||'.'||rec_seq.sequence_name) 
           as p(id int);
           
           begin
                      raise notice 'alter sequence %.% restart with %', rec_seq.schema_name,rec_seq.sequence_name,seq_value;
             execute format('alter sequence %I.%I restart with %s',rec_seq.schema_name,rec_seq.sequence_name,seq_value);
           exception when others then 
             raise notice 'Exception occurred to set sequence %', rec_seq.schema_name||'.'||rec_seq.sequence_name; 
           end;
           
         exception when others then 
           raise notice 'Exception occurred to fetch sequence %', rec_seq.schema_name||'.'||rec_seq.sequence_name;
         end;
     
       end loop;
       begin
         execute 'SELECT dblink_disconnect($1)' using 'myconn';
       exception when others then 
         raise notice 'Connection disconnect exception, safely ignore.';
       end;
       
end;
$$ language plpgsql;

Sample output:

NOTICE:  Processing: public.sporting_event_id_seq
NOTICE:  alter sequence public.sporting_event_id_seq restart with 1
NOTICE:  Processing: public.s3
NOTICE:  Exception occurred to fetch sequence public.s3
NOTICE:  Processing: public.s1
NOTICE:  alter sequence public.s1 restart with 35
NOTICE:  Processing: public.s2
NOTICE:  alter sequence public.s2 restart with 28
DO

Query returned successfully in 87 msec.