How to reset all sequences to 1 before database migration in PostgreSQL?

Question

(PostgreSQL 9.4)

I am in the process of migrating an older database to a new schema. After using pg_restore to acquire the new schema (without data) from my development machine, I find that some sequences do not start at 1. (I had changed multiple sequences during development to work with higher values).

Before I start the database migration, is there a programmatic way of resetting all the sequences (some of which are not primary keys) back to 1?

Thanks for any help or suggestions.

Answer 1

This works simple enough for my needs, SETVAL manual. In PgAdmin where I want to restrict the sequences to all those in the public schema:

SELECT  SETVAL(c.oid, 1)
from pg_class c JOIN pg_namespace n 
on n.oid = c.relnamespace 
where c.relkind = 'S' and n.nspname = 'public'  

I post this as a help to anyone coming here.

Answer 2

You can do it with this sql code:

DO $$
DECLARE
i TEXT;
BEGIN
 FOR i IN (SELECT column_default FROM information_schema.columns WHERE column_default SIMILAR TO 'nextval%') 
  LOOP
         EXECUTE 'ALTER SEQUENCE'||' ' || substring(substring(i from '''[a-z_]*')from '[a-z_]+') || ' '||' RESTART 1;';    
  END LOOP;
END $$; 

I read information of columns and I use regex to separate squence's name. After I make query and use EXECUTE for each sequence. This code is for all sequences of your DB.

Answer 3

Sometimes the sequences do not follow a pattern.
I share the following code I hope it helps

CREATE OR REPLACE FUNCTION
 restore_sequences(schema_name in varchar)
 RETURNS void AS $$
 DECLARE
    statements CURSOR FOR
        select s.sequence_schema, s.sequence_name from 
        information_schema."sequences" s
        where s.sequence_schema = schema_name
        order by s.sequence_schema asc;
BEGIN
    FOR stmt IN statements loop
        execute 'SELECT SETVAL(' || ((E'\''||(select current_database())||'.'||stmt.sequence_schema||'.'||stmt.sequence_name)||(E'\'')) || ', 1, true);';
        execute 'ALTER SEQUENCE ' || ((select current_database())||'.'||stmt.sequence_schema||'.'||stmt.sequence_name) || ' START 1';
    END LOOP;
END;
$$ LANGUAGE plpgsql
;