pgsql utf8 디비 생성시 오류

실행

CREATE DATABASE coupon WITH OWNER = malluser ENCODING = 'UTF8' CONNECTION LIMIT = -1;

오류

ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (EUC_KR)

HINT:  Use the same encoding as in the template database, or use template0 as template.

해결

Ok, below steps resolved the problem:

1. 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';

2. Now we can drop it:

   DROP DATABASE template1;

3. Now its time to create database from template0, with a new default encoding:

   CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

4. Now modify template1 so it’s actually a template:

   UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

5. Now switch to template1 and VACUUM FREEZE the template:

   \c template1

   VACUUM FREEZE;

Problem should be resolved.