Note – this assumes you’ve enabled the pgbackup plugin in Heroku – as it does the full database backup for you for free – I suggest you do it right now!
Oh, the fun I just had with this one. One of my clients’ systems started throwing this message over the last week or so.
I’m lazy so I was using sqlite for my development database and Heroku’s Postgres for live.
So, thinks I, time to switch to using Postgres in my development environment, because it must be the database, mustn’t it?
I’d installed it on my Macbook ages ago but hadn’t actually used it with Rails. So I found a couple of blog posts and changed things around. I had already told Postgres to use passwords for validation.
It took me ages to realise I needed to set a password for the postgres Unix user – this was the password I needed to connect to the database. Then I followed these instructions and got my dev database set up.
So – pull stuff down from heroku and then import it to my dev environment.
Right – everything works perfectly – right!
So where is the error?
I googled for it and found a forum posting suggesting that you could have a date stamp that is outside the range of time stamps and the coercion of date stamps is going boom. Did a lot of messing around in the heroku console to no avail, trying to issue commands like analyze table and count things.
Also, the column is a timestamp anyway, so you can’t store nonsense in it in the first place.
So, I put the app into maintenance mode, took another backup, restored that backup over the existing database, and all was well.
This might be a better solution than trying to rescue the database in any event, because all of the statistics will have been reset too.
I was a little bit hampered by heroku not letting you connect to a shared database because I was trying to issue commands using Active Record – but hey – it’s free. I do wish that heroku console was slightly more informative than server error.