Was using `set datcollate='C'` a mistake?
The datcollate column of pg_database stores 


  LC_COLLATE for this database


An other page of the documentation about collations says:


  The collation feature allows specifying the sort order and character
  classification behavior of data per-column, or even per-operation.
  This alleviates the restriction that the LC_COLLATE and LC_CTYPE
  settings of a database cannot be changed after its creation.


That means that by updating that column you managed to cross some river that should not be crossed under any circumstances.

On the other hand, as collations affect the ordering of text, varchar and varchar typed columns (and possibly some others, check the documentation page for details).  This also means that the existing indexes on such columns will be ordered differently from what the LC_COLLATE dictates.  This will result of some weird behaviour, and as you've seen, at least a REINDEX will be needed.  I would expect that doing a 

SET enable_indexscan TO off;


would help you finding the user by username, because it would do a sequential scan instead of an index scan, so not trying to use the unusable index.

I cannot tell if this is enough to fix your DB, but I would do the following instead:


set up a new cluster with the desired collation
take a dump from your DB
restore it on the new cluster, watching for errors
test it thoroughly and then start using it instead of the old one


For the future, you cannot change the DB level collation, but you can define collation on the column level.

One can even change the collation on a column like

ALTER TABLE t ALTER COLUMN col1 TYPE text COLLATE "en_US";


where you keep the type as it is, just set a different collation.

Just a final notice: changing the system catalogs directly should always be a last resort.  Before doing that (probably except adding new enum values in pre-9.1 versions), one should carefully examine the possible outcomes from the documentation, blog or mailing list posts and testing.