As @adamo's answer explains, nextval(..) can be out of sync and no longer incrementing upon the max value. In my case it was due to a logical replication and switch-over, where PostgreSQL leaves all sequence numbers as they were at subscription time.
For larger databases, here are some convenient functions for querying which integer primary keys are out of sync, and to update them.
Verify
CREATE OR REPLACE FUNCTION primary_id_sequences_from_all_tables()
RETURNS TABLE (table_name TEXT, column_name TEXT, data_type TEXT, max BIGINT, next BIGINT) AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT tc.table_name, kcu.column_name, c.data_type
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.columns AS c
ON kcu.table_name = c.table_name
AND kcu.column_name = c.column_name
AND c.data_type IN ('smallint', 'integer', 'bigint', 'decimal', 'numeric', 'real', 'double precision')
WHERE tc.constraint_type = 'PRIMARY KEY'
LOOP
RETURN QUERY EXECUTE 'SELECT ' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || ', ' || quote_nullable(rec.data_type) || ', (SELECT COALESCE(MAX(' || rec.column_name || '), 0)::BIGINT FROM ' || rec.table_name || '), (SELECT nextval(pg_get_serial_sequence(' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || ')))';
END LOOP;
END;
$$ LANGUAGE plpgsql;
n.b., the function calls nextval which will have a side-effect of incrementing the sequence values. It shouldn't be a problem, but if you want to avoid this you can switch this to pg_sequence_last_value. However this may only show primary keys for which nextval has already been called.
Call the function to verify the list:
SELECT table_name, column_name, data_type, max, next, (max - next) AS diff
FROM primary_id_sequences_from_all_tables()
WHERE next IS NOT NULL AND max > next
ORDER BY diff DESC;
Update
If you are satisfied and wish at your own risk to fudge the nextval() to be max(..) + 1 for all those columns you can run this function:
CREATE OR REPLACE FUNCTION fixup_primary_id_sequences_in_all_tables()
RETURNS TABLE (table_name TEXT, column_name TEXT, max BIGINT, next BIGINT, set_to BIGINT) AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT seqs.table_name, seqs.column_name, seqs.max, seqs.next
FROM primary_id_sequences_from_all_tables() AS seqs
WHERE seqs.next IS NOT NULL AND seqs.max > seqs.next
LOOP
RETURN QUERY EXECUTE 'SELECT ' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || ', ' || rec.max || '::BIGINT, ' || rec.next || '::BIGINT, setval(pg_get_serial_sequence(' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || '), (SELECT MAX(' || rec.column_name || ') FROM ' || rec.table_name || ')+1) AS set_to';
END LOOP;
END;
$$ LANGUAGE plpgsql;
... with this line:
SELECT * FROM fixup_primary_id_sequences_in_all_tables()
