Someone posted a dilemma to the pgsql-sql list today that involved many if not all of his sequences getting out of sync with their respective "serial" columns. In other words, something like "SELECT max(id) FROM sometable" yields 42, but the sequence nextval for sometable.id is currently set to 36. This is obviously bad (for reasons left as an exercise for the reader). So besides trying to figure out how the database ended up in this state, he needed a script to reset all of his sequences to the correct next value.

I had run into a similar need not too long ago. Namely, when setting up multi-master replication with Bucardo you need your sequences to draw different values on either master so as not to conflict. One solution is to set up all your sequences to jump by 2, and use even numbers on one master and odd numbers on the other. Again, a script makes this easier to deal with, and I had developed one for this situation. So I modified it for the problem mentioned above.

Both versions follow:

-- create "odd" and "even" sequences in multi-master scenario
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text, starteven bool)
  RETURNS text AS $$
DECLARE
  rec         record;
  startval   bigint;
  sql          text;
  fqname  text;
BEGIN
  FOR rec in EXECUTE 'select relname from pg_class where relkind = ''S''
                      and relnamespace = (select oid from pg_namespace
                      where nspname=''' || namespace || ''')' LOOP
    fqname :=  namespace || '.' ||  rec.relname;
    IF starteven THEN
      EXECUTE 'SELECT ((last_value / 2) * 2) + 2 from ' || fqname INTO startval;
    ELSE
      EXECUTE 'SELECT ((last_value / 2) * 2) + 1 from ' || fqname INTO startval;
    END If;
    sql := 'ALTER SEQUENCE ' || fqname || ' INCREMENT BY 2 RESTART WITH ' || startval;
    EXECUTE sql;
    RAISE NOTICE '%', sql;
  END LOOP;
  RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;
SELECT adjust_seqs('public', true);  -- in master1 (even)
SELECT adjust_seqs('public', false); -- in master2 (odd)

-- update sequences that have gotten out-of-sync with the
-- PK field for which they normally provide the default
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text)
  RETURNS text AS $$
DECLARE
  rec           record;
  startval     bigint;
  sql            text;
  seqname  text;
BEGIN
  FOR rec in EXECUTE 'select table_name, column_name, column_default
                      from information_schema.columns
                      where table_schema = ''' || namespace || '''
                      and column_default like ''nextval%''' LOOP

    seqname := pg_get_serial_sequence(rec.table_name, rec.column_name);
    sql := 'select max(' || rec.column_name || ') + 1 from ' || rec.table_name;
    EXECUTE sql INTO startval;
    IF startval IS NOT NULL THEN
      sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' || startval;
      EXECUTE sql;
      RAISE NOTICE '%', sql;
    END IF;
  END LOOP;
  RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;
select adjust_seqs('public');

Neither of these is heavily tested, and both make certain assumptions, so please test and modify to suit your own needs. Caveat emptor!