The database collation is not supported but no options for the postgresql to use utf-8

[Abdullah Roman]

getting the below errors 

2021-06-19 00:10:24,569 INFO [Catalina-utility-1] [com.atlassian.confluence.lifecycle] contextInitialized Starting Confluence 6.15.8 [build 8100 based on commit hash 6ef1ce95c788ac159314a8fa6387047b8d1cc9fb] - synchrony version 2.1.0-release-confluence_6.15-32f7299a
2021-06-19 00:10:30,969 INFO [Catalina-utility-1] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization started
2021-06-19 00:10:37,938 INFO [Catalina-utility-1] [com.atlassian.confluence.lifecycle] <init> Loading EhCache cache manager
2021-06-19 00:11:07,143 INFO [Catalina-utility-1] [springframework.web.context.ContextLoader] initWebApplicationContext Root WebApplicationContext: initialization completed in 36173 ms
2021-06-19 00:11:08,302 ERROR [Catalina-utility-1] [atlassian.confluence.setup.DefaultDatabaseVerifier] verifyCollationOfDatabase The collation of database is not set correctly!
2021-06-19 00:11:08,992 WARN [Catalina-utility-1] [confluence.impl.health.DefaultHealthCheckRunner] logEvent The database collation 'English_United States.1252' is not supported by Confluence. You need to use 'utf-8'.
2021-06-19 00:11:08,993 WARN [Catalina-utility-1] [confluence.impl.health.DefaultHealthCheckRunner] logEvent The database collation 'English_United States.1252' is not supported by Confluence. You need to use 'utf-8'.

I have followed the steps from the below link but no luck! https://confluence.atlassian.com/doc/database-setup-for-postgresql-173244522.html

below is the my create database script and the database created successfully:

CREATE DATABASE "CONF-DB" WITH OWNER = "CONF_USER" ENCODING 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' TEMPLATE template0;

but if i use following script to create database into postgres it fails:

CREATE DATABASE "CONF-DB" WITH OWNER = "CONF_USER" ENCODING 'UTF8' LC_COLLATE = 'UTF8' LC_CTYPE = 'UTF8' TEMPLATE template0;

[Pavel Junek]

Hi @Abdullah Roman,

Welcome to Atlassian community!

I used this procedure to create a DB:

 CREATE DATABASE conf-db WITH ENCODING 'UNICODE' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0 OWNER conf_usr;

Pavel

[Abdullah Roman]

Hi Pavel, 

Thanks for your replay. I am getting below error : My Postgresql version is -> PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit

ERROR: invalid locale name: "en_US.UTF-8"
********** Error **********

ERROR: invalid locale name: "en_US.UTF-8"
SQL state: 42809

[Pavel Junek]

Hi Abdullah,

Please check for available locales with locale -a if not found, try manually adding it with:

locale-gen en_US.UTF-8 

after this

sudo systemctl restart postgresql

and

CREATE DATABASE db WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

Hope this helps.