New collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf8)

[gionn]

Cookbook version

7.1.0

Chef-client version

13.10.4

Platform Details

Ubuntu 18.04

Scenario:

Create a database

Steps to Reproduce:

postgresql_server_install 'package' do
  action [:install, :create]
  password 'postgres_user_password'
end

postgresql_user 'database_name' do
  password 'password'
end

postgresql_database 'database_name' do
  owner 'database_name'
end

Actual Result:

           createdb: database creation failed: ERROR:  new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf8)
             HINT:  Use the same collation as in the template database, or use template0 as template.
             ---- End output of "bash"  "/tmp/chef-script20180824-1987-1vugioh" ----
             Ran "bash"  "/tmp/chef-script20180824-1987-1vugioh" returned 1
             
             Resource Declaration:
             ---------------------
             # In /tmp/kitchen/cache/cookbooks/postgresql/resources/database.rb
             
       40:   bash "Create Database #{new_resource.database}" do
       41:     code createdb
       42:     user new_resource.user
       43:     not_if { slave? }
       44:     not_if { database_exists?(new_resource) }
       45:   end
       46: end
             
             Compiled Resource:
             ------------------
             # Declared in /tmp/kitchen/cache/cookbooks/postgresql/resources/database.rb:40:in `block in class_from_file'
             
             bash("Create Database database_name") do
        action [:run]
        default_guard_interpreter :default
        command nil
        backup 5
        returns 0
        user "postgres"
        interpreter "bash"
        declared_type :bash
        cookbook_name "cd-infrastructure"
        code "createdb -E UTF-8 -l en_US.UTF-8 -T template1 -O database_name -U postgres -p 5432 database_name"
        domain nil
        not_if { #code block }
        not_if { #code block }
             end

workaround

postgresql_database 'database_name' do
  locale 'en_US.utf8'
  owner 'database_name'
end

[damacus]

Hey!

thanks for opening this issue, this actually looks like you've found the fun difference between platforms and who thinks UTF-8 is the default over utf8.

In this case this isn't a bug of the cookbook, I'm glad you found the fix for locale's being created in the wrong language easily though.

Thanks,
Dan

[gionn]

Great to know, but cookbooks aren't supposed to handle even idiosyncrasies between different platforms?

This needs at least a note in the README to avoid confusing people and losing their time.

[damacus]

I've just had the same problem with CI actually

STDERR: createdb: database creation failed: ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)

It looks like postgresql takes the system DB locale as a default (which is what we often key off), we've chosen UTF8 rather than utf-8 as it was what our systems were reporting as correct.

If this isn't correct the user will need to fix this (as I've just done in tests)

That all being said, yes some guidance on this would be helpful. If you want to make a PR for we'd really appreciate the README update!

I've opened a tracking issue for a README update (#560)

[gionn]

It looks like postgresql takes the system DB locale as a default (which is what we often key off), we've chosen UTF8 rather than utf-8 as it was what our systems were reporting as correct.

This raised something in my past memories, probably this bug was present also on the early versions of the cookbook and a fix was buried on my wrapper cookbook. Now that I switched to v7 the bug is kicking back again.

That all being said, yes some guidance on this would be helpful. If you want to make a PR for we'd really appreciate the README update!

Will do

[wmantly]

I am still having this issue. Can someone post a working version for Ubuntu 16.04

postgresql_server_install 'My PostgreSQL Server install' do
  initdb_locale 'en_US.utf8'
  action :install
end

postgresql_server_install 'Setup my PostgreSQL 9.6 server' do
  initdb_locale 'en_US.utf8'
  action :create
end

postgresql_access 'local_postgres_superuser' do
  comment 'Local postgres superuser access'
  access_type 'local'
  access_db 'all'
  access_user 'postgres'
  access_addr nil
  access_method 'ident'
end

postgresql_user 'gitea' do
  password 'xxx'
end

postgresql_database 'gitea' do
  locale 'en_US.utf8'
  owner 'gitea'
end

[jimdaga]

Not sure if this is what you're looking to do, but for my setup, I set the template to template0to work around this. (I'm still on 7.1.1 running Ubuntu 16.04).

postgresql_database 'foo' do
  template 'template0'
  encoding 'SQL_ASCII'
  locale 'C'
end

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.