Here is an example error when trying to create a database in Postgres:
ERROR 22023 (invalid_parameter_value) encoding "UTF8" does not match locale
The chosen LC_CTYPE setting requires encoding "LATIN1".
Such an error might come up if OS locale was not set while installing Postgres. It is fixable by shuffling templates or by reinstalling Postgres after locale is set, I prefer the latest, cause having a fresh install just gives me more peace of mind. To fix that, we must first configure locale, on Debian/Ubuntu it would be something like that (the command will trigger an interactive prompt):
sudo dpkg-reconfigure locales
And then we need to remove Postgres completely, including the cluster generated after the first install. I used Ubuntu and was unable to achieve it by using apt remove or even apt purge, what worked for me is following:
sudo apt remove --purge postgresql*
That should do the trick, but just to make sure, let’s also delete few directories generated by Postgres:
sudo rm -r /etc/postgresql/
sudo rm -r /var/lib/postgresql/
sudo userdel -r postgres
That deletes a couple postgres directories and the user, there is also postgres group which will be deleted automatically cause it would be empty, but you can still try to delete it, just to be sure: “sudo groupdel postgres”.
After that, install postgres normally and it should re-create the cluster, with correct locale it will have unicode out of the box:
sudo apt install postgresql postgresql-contrib postgresql-doc