Login/Register
rake db:create encoding error with postgresql
Asked Answered
Solved ruby-on-rails-3postgresqlencodingrake-task
I

5

45

I'm importing an existing rails project that I was working on into my new arch linux system, I already installed all gems and postgresql correctly, but I having some issues when runing:

rake db:create

I get the following error

PGError: 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 "System_test" ENCODING = 'unicode'

I created manually the database with the correct encoding and the migrations worked well, but I can run the

rake db:test:clone

command because It tries to create a database and I don't suppose to create manually the databases either. So, someone knows to fix this?

greetings

EDIT: here's my database.yml

development:
  adapter: postgresql
  encoding: unicode
  database: System_development
  pool: 5
  username: forellana
  password:

test: &test
  adapter: postgresql
  encoding: unicode
  database: System_test
  pool: 5
  username: forellana
  password:

cucumber:
  <<: *test

and here's the complete output of the command

(in /home/fespinoza/Workspace/TLI)
PGError: 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 "System_test" ENCODING = 'unicode'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:207:in `rescue in log'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:199:in `log'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:493:in `execute'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:572:in `create_database'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/railties/databases.rake:92:in `rescue in create_database'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/railties/databases.rake:39:in `create_database'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/railties/databases.rake:33:in `block (2 levels) in <top (required)>'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:636:in `call'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:636:in `block in execute'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:631:in `each'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:631:in `execute'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:597:in `block in invoke_with_call_chain'
/home/fespinoza/.rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:590:in `invoke_with_call_chain'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:583:in `invoke'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2051:in `invoke_task'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2029:in `block (2 levels) in top_level'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2029:in `each'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2029:in `block in top_level'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2023:in `top_level'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2001:in `block in run'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:1998:in `run'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/bin/rake:31:in `<top (required)>'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/bin/rake:19:in `load'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/bin/rake:19:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"System_test", "pool"=>5, "username"=>"forellana", "password"=>nil}
PGError: 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 "System_development" ENCODING = 'unicode'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:207:in `rescue in log'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:199:in `log'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:493:in `execute'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:572:in `create_database'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/railties/databases.rake:92:in `rescue in create_database'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/railties/databases.rake:39:in `create_database'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.0.5/lib/active_record/railties/databases.rake:35:in `block (2 levels) in <top (required)>'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:636:in `call'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:636:in `block in execute'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:631:in `each'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:631:in `execute'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:597:in `block in invoke_with_call_chain'
/home/fespinoza/.rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:590:in `invoke_with_call_chain'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:583:in `invoke'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2051:in `invoke_task'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2029:in `block (2 levels) in top_level'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2029:in `each'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2029:in `block in top_level'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2023:in `top_level'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2001:in `block in run'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/lib/rake.rb:1998:in `run'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/gems/rake-0.8.7/bin/rake:31:in `<top (required)>'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/bin/rake:19:in `load'
/home/fespinoza/.rvm/gems/ruby-1.9.2-p180/bin/rake:19:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"System_development", "pool"=>5, "username"=>"forellana", "password"=>nil}
Imogen answered 28/4, 2011 at 15:38 Comment(2)
What's your database.yml file look like? – Theatrics
How did you install postgresql on your system? Was it from source or from a package? What does the output of psql --command="\l" look like? – Predilection
P
27

The main problem here is that your template database (template1) has been created with an ASCII encoding and you're telling PostgreSQL to create the new database with UTF8 encoding. Needless to say, it's not particularly pleased about that. What you can do is erase your template1 database and re-create it using these instructions. This can also be a problem when your hosting provider hasn't properly set the locale. You can read more about fixing your missing locales.

I found all of this info through this post about Fixing PostgreSQL's default encoding on Ubuntu 9.10

Predilection answered 28/4, 2011 at 16:14 Comment(4)
Thanks, I don't have the commands pg_dropcluster o I didn't know where to invoke them, but on the first comment of that post I found the right solution for me. – Imogen
Excellent, I'm glad that you found a solution that worked for you. – Predilection
Just a tip if you're initializing the database: initdb -E UTF8 – Zemstvo
Don't delete your template1 database unless you know exactly what you are doing. Use template0 as template or create a new DB cluster with your desired settings to begin with. – Iambic
D
80

To fix this in Rails, I've found that you can simply add the following line to each section (development/production etc.) of your database.yml file:

template: template0

See ActiveRecord/ConnectionAdapters/PostgreSQL/SchemaStatements#create_database for other options.

Deboer answered 23/9, 2011 at 13:29 Comment(7)
NO in rails 3.0.3 with postgresql 9.1.2 – Vizierate
Worked on Ubuntu 10.04, postgresql 8.4.9 and rails 3.1.3. Thank you! – Zehe
Worked on Ubuntu 12.04, Rails 3.2.3, Ruby 1.9.2, and PostgreSQL 9.1.4. THANK YOU! – Jospeh
Worked on Ubuntu 12.04, Rails 3.2.12, Ruby 2.0.0 and PostgreSQL 9.2, bundled in a VM. – Butcherbird
Worked on Ubuntu 12.04, Rails 4.0.5, Ruby 2.1.1 and PostgreSQL 9.1.13 in a Vagrant VM. Thanks! – Unroll
What happens then? You have a database with US-Ascii charset? – Flask
Doesn't worked with rails 4.2.1 and Postgres 9.6.3 :-( – Governor
P
27

The main problem here is that your template database (template1) has been created with an ASCII encoding and you're telling PostgreSQL to create the new database with UTF8 encoding. Needless to say, it's not particularly pleased about that. What you can do is erase your template1 database and re-create it using these instructions. This can also be a problem when your hosting provider hasn't properly set the locale. You can read more about fixing your missing locales.

I found all of this info through this post about Fixing PostgreSQL's default encoding on Ubuntu 9.10

Predilection answered 28/4, 2011 at 16:14 Comment(4)
Thanks, I don't have the commands pg_dropcluster o I didn't know where to invoke them, but on the first comment of that post I found the right solution for me. – Imogen
Excellent, I'm glad that you found a solution that worked for you. – Predilection
Just a tip if you're initializing the database: initdb -E UTF8 – Zemstvo
Don't delete your template1 database unless you know exactly what you are doing. Use template0 as template or create a new DB cluster with your desired settings to begin with. – Iambic
D
22

You can change postgres template1 to be UTF by doing the following as posted here:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
\c template1
VACUUM FREEZE;
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
Dumortierite answered 3/12, 2011 at 23:40 Comment(2)
OSX, worked for me, installed pg with homebrew, version 9.3.2, Rails 4.0.5 – Volition
ERROR: encoding "UTF8" does not match locale "en_US" DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1". – Stygian
F
6

Similar to the other answers I found this gist to be very helpful. Using Ubuntu 14.04. I wanted to change the default template to use UTF-8.

Get into the postgres prompt:

Activate the postgres console.
su - postgres
psql

Then type the following commands:

# 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. Credits to: https://gist.github.com/amolkhanorkar-webonise/8706915

Flask answered 13/11, 2014 at 17:43 Comment(0)
G
1

Adding

template: template0

in config/database.yml worked for me :-)

Generalization answered 13/1, 2016 at 15:39 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.