For the third time it has happened to me that a sequence has been set to an initial value (zero or one, not sure) while in the table, there are around 1500 records. When it happens, new rows cannot be inserted from my application. So I would like to know possible causes for the sequence to be out of sync like that. Because I have no idea why this problem is occurring.
There are a few possible cases where things can get out of sync.
Very old versions (unsupported) used to sometimes fail to set sequences on backup restore. If you have manual backup and restore routines, this is somewhere to look.
setval('sequence_name', 1) will set to to 1.
Those are your only two possibilities unless you have a short cycle, and are cycling.
Was your sequence created with the "CYCLE" option? You can check by querying the sequence directly
select * from your_seq;
Look at the "max_value" and "is_cycled" columns. What those attributes mean is covered in more detail at http://www.postgresql.org/docs/current/static/sql-createsequence.html
This happened to me, when I first started using a PostgreSQL variant (Greenplum). I renamed a table, created a new table under the original name, and ran an INSERT to copy the data across. I did not know that PostgreSQL implemented SERIAL columns with an associated sequence, and when the new table got a new sequence that started at 1, I got duplicates in the table when I started inserting new records.
So this is probably not what is happening to you, if the sequence is being reset to 1. What I had was a new sequence being created.