Yesterday I was having a problem. A very bad one. The following was happening:
postgres=# SELECT * FROM users WHERE email='alex@alexgleason.me';
zero results!
postgres=# SELECT * FROM users WHERE email='my.friends@email.com';
she shows up...
postgres=# UPDATE users SET email='alex@alexgleason.me' WHERE id=1;
updating the row with my email... just to sanity check.
postgres=# SELECT * FROM users WHERE email='alex@alexgleason.me';
still zero results!
postgres=# SELECT * FROM users WHERE id=1;
I show up fine when selected by id...
postgres=# SELECT * FROM users WHERE email LIKE 'alex@alexgleason.me%';
I show up with a wildcard on the right. Could there be something there?
postgres=# SELECT * FROM users WHERE email LIKE '%alex@alexgleason.me';
I show up with a wildcard on the left, too...
postgres=# SELECT * FROM users WHERE email='alex@alexgleason.me';
I don't show up without a wildcard, but my friend does...
It didn't make any sense. As a consequence of this, I was unable to log into a self-hosted Ruby on Rails app, Mastodon. Rails would try to find my user by email to log me in and postgres would return no results.
The fix
I backed up the database with pg_dump, created a new database, and imported the backup with psql. This fixed my issues. Selects are working right again. Something was out of sync with postgres, and I still don't know what exactly.
This probably happened because I frantically copied the full, running database directory (/var/lib/postgresql/data
) to a new location when my site crashed. I was using Docker volumes and couldn't figure out how to dump the database from a volume in the heat of the moment so I just copied the files, and it worked! Until it started doing this.