Skip to content

Instantly share code, notes, and snippets.

@fzero

fzero/pgfix.md

Created Dec 7, 2016
Embed
What would you like to do?
Fix Postgres encoding issue

Is you attempt to do a bin/rails db:setup or something similar and it complains like this:

PG::InvalidParameterValue: ERROR:  encoding "UTF8" does not match locale "en_US"
DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".

Run these commands in your Vagrant box:

sudo su postgres
psql

This will put you on the psql prompt as the postgres user. Then run each one of these lines, one at a time:

update pg_database set datistemplate=false where datname='template1';
drop database Template1;
create database template1 with owner=postgres encoding='UTF-8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;
update pg_database set datistemplate=true where datname='template1';

Exit psql with \q, then type exit to go back to your own user.

Rejoice, your locale is now fixed in postgresql. A vagrant image update is coming soon™.

What the above does:

  • Normally new databases copy of default settings from the template1 database. Because of it's importance, it is protected from being deleted. The first command removes that restriction.
  • We drop the database, gone for ever. No turning back now.
  • We create a new template1 database from... template0 database. We set the locale to support north american english with fancy pants UTF-8 (aka, Shit Emoji support)
  • We return template1 to its warm blanket of protection, marking it as a template once more.

The problem, for those curious, was the default install of Ubuntu does not set support for utf-8 by default (a terrible default IMHO). This changes how many programs run, including progresql. Which, by default, will initialize with the systems locale.


Adapted from James Sapara's original gist.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment