Postgres SELECT not working. Won't pull row by constraint.

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.