Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgres PG::Error: ERROR: new encoding (UTF8) is incompatible
======= Prolbem =================================================================================================================
I have installed : ruby-2.0.0,postgres-9.2 , now in rails app when I execute:
rake db:create , command I get:
PG::InvalidParameterValue: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT: Use the same encoding as in the template database, or use template0 as template.
: CREATE DATABASE "my_db_name" ENCODING = 'unicode'.......
bin/rake:16:in `load'
bin/rake:16:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_db", "host"=>"localhost", "pool"=>5, "username" =>"my_user", "password"=>"my_password"}
=================================================================================================================================
Solution
=================================================================================================================================
Ok, below steps resolved the problem:
First, we need to drop template1. Templates can’t be dropped, so we first modify it so t’s an ordinary database:
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
Now we can drop it:
DROP DATABASE template1;
Now its time to create database from template0, with a new default encoding:
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
Now modify template1 so it’s actually a template:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
Now switch to template1 and VACUUM FREEZE the template:
\c template1
VACUUM FREEZE;
Problem should be resolved.
@yonkeltron

This comment has been minimized.

Copy link

yonkeltron commented Feb 27, 2014

Awesome! This helped me solve an issue with default installation config issues with Puppet for a Vagrant dev environment box. Thanks for posting this!

@emaraschio

This comment has been minimized.

Copy link

emaraschio commented Aug 6, 2014

Thanks man! this fix a tiny issue with: https://github.com/rails/rails-dev-box

@engineer

This comment has been minimized.

Copy link

engineer commented Aug 17, 2014

Thanks bro :)

@sethyanow

This comment has been minimized.

Copy link

sethyanow commented Sep 17, 2014

Thanks! This really helped out with an issue setting up a cloud9 workspace.

@thogg4

This comment has been minimized.

Copy link

thogg4 commented Oct 7, 2014

Great solution. thanks.

@eliotsykes

This comment has been minimized.

Copy link

eliotsykes commented Oct 13, 2014

Discovered this error can also be fixed in database.yml.

Add this line to database.yml for each db to specify the template:

template: template0

e.g.

development:
  adapter: postgresql
  host: localhost
  encoding: unicode
  database: appstore_development
  pool: 5
  username: someone
  password: some-password
  template: template0

test:
  adapter: postgresql
  host: localhost
  encoding: unicode
  database: appstore_test
  pool: 5
  username: someone
  password: some-password
  template: template0

Worked with Rails 4.1.x (with rails-dev-box postgres like @emaraschio)

@frankie-loves-jesus

This comment has been minimized.

Copy link

frankie-loves-jesus commented Oct 17, 2014

Nice !!

@chemica

This comment has been minimized.

Copy link

chemica commented Dec 9, 2014

Awesome work, best solution I've found to this recurring issue!

@scientist1642

This comment has been minimized.

Copy link

scientist1642 commented Apr 21, 2015

thank you @eliotsykes

@rkcitrus

This comment has been minimized.

Copy link

rkcitrus commented Jun 11, 2015

@dcousette

This comment has been minimized.

Copy link

dcousette commented Jun 15, 2015

Thanks!!!

@zhrivodkin

This comment has been minimized.

Copy link

zhrivodkin commented Jun 24, 2015

Awesome! Thanks a lot!

@zzeni

This comment has been minimized.

Copy link

zzeni commented Sep 30, 2015

@kalilz4485

This comment has been minimized.

Copy link

kalilz4485 commented Dec 15, 2015

Thank you a lot !

@yevgenko

This comment has been minimized.

Copy link

yevgenko commented Jan 7, 2016

👍

@Efeinan

This comment has been minimized.

Copy link

Efeinan commented Jan 20, 2016

@eliotsykes 's soulution is easier, thanks both.

@kyptin

This comment has been minimized.

Copy link

kyptin commented Feb 5, 2016

If you do your own call to initdb, you can also specify the encoding with the -E option.

@miminashi

This comment has been minimized.

Copy link

miminashi commented Mar 6, 2016

Thanks!
This helps me to use PostgreSQL on Cloud9!

@dminchev

This comment has been minimized.

Copy link

dminchev commented Mar 18, 2016

Thanks!

@rendon

This comment has been minimized.

Copy link

rendon commented Mar 18, 2016

👍

@AkashChandrakar

This comment has been minimized.

Copy link

AkashChandrakar commented Mar 22, 2016

Thanks! well explained.

@murtuzamvista

This comment has been minimized.

Copy link

murtuzamvista commented Apr 14, 2016

@maneto

This comment has been minimized.

Copy link

maneto commented May 24, 2016

thanks for @amolkhanorkar and @eliotsykes

@mcardacci

This comment has been minimized.

Copy link

mcardacci commented Jun 15, 2016

thanks for the breakdown!

@holtalanm

This comment has been minimized.

Copy link

holtalanm commented Jul 22, 2016

+1 freaking awesome.

@akshaybharwani

This comment has been minimized.

Copy link

akshaybharwani commented Jul 26, 2016

The real MVP right here people! Thank you!

@mandym-webdev

This comment has been minimized.

Copy link

mandym-webdev commented Sep 10, 2016

This is perfect! thanks for posting it!

@pdagrawal

This comment has been minimized.

Copy link

pdagrawal commented Nov 14, 2016

This works for me. Thanks for this.

@tonyvince

This comment has been minimized.

Copy link

tonyvince commented Dec 15, 2016

Thank you very much

@qqgwq

This comment has been minimized.

Copy link

qqgwq commented Jan 3, 2017

thanks

@ozanyurt

This comment has been minimized.

Copy link

ozanyurt commented Feb 3, 2017

thanks

@dnorthrupva

This comment has been minimized.

Copy link

dnorthrupva commented Mar 4, 2017

Thanks! Fixed my issue also!

@Bovojon

This comment has been minimized.

Copy link

Bovojon commented Mar 5, 2017

Thank you! Worked like a charm!

@TheWolfCoder

This comment has been minimized.

Copy link

TheWolfCoder commented Mar 12, 2017

Thank you good sir!

@p-pavlo

This comment has been minimized.

Copy link

p-pavlo commented Sep 18, 2017

Thanks a lot! Now it works perfectly with cloud9

@mshegolev

This comment has been minimized.

Copy link

mshegolev commented Dec 7, 2017

That's works, thank you. I changed ascii to utf-8.

@dimoha

This comment has been minimized.

Copy link

dimoha commented Feb 9, 2018

Thanks!

@PebaneThato

This comment has been minimized.

Copy link

PebaneThato commented Apr 10, 2018

Thank you, It worked for me

@Ex-Ark

This comment has been minimized.

Copy link

Ex-Ark commented Jul 11, 2018

thanks, it worked nicely

@jpfinlay

This comment has been minimized.

Copy link

jpfinlay commented Jul 20, 2018

@amolkhanorkar -- thank you! This solved a big headache for me! 👍

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