Resetting Sequences in PostgreSQL on Heroku22 Jun 2016
Today Glatering – a world-class Rails app with a Postgres database I’m running on Heroku – kicked the bucket! Time to debug… To the Heroku logs!
Looks like when we were creating a new event object the auto-assigned id for it (which has to be unique) already existed in the database. We were trying to use id 47 when the database had an event with that id already in it. This is weird because that id should be assigned and auto-incremented for us.
So what do we do? Get Postgres to check itself a.k.a. reset the value “events_pkey” to a valid value.
To connect to the database, I use my favorite Postgres GUI Postico. All the information you need to connect to the database (e.g. user, password, host, database name) is in the Heroku DATABASE_URL environmental variable in the Settings section of the app dashboard.
Once connected, click on SQL query, paste the query below (and replace events with the name of the table you’re having issues with), and hit Execute Statement.
Boom! Hopefully your database is still in one piece, your Postgres sequence should be reset it, and it won’t generate duplicate ids any more!