PostgreSQL 10 환경에서 collate ko_kr.utf-8 로 database 생성하기

OS환경 : Oracle Linux 7.6 (64bit)

DB 환경 : PostgreSQL 10

방법 : PostgreSQL 10 환경에서 collate ko_kr.utf-8 로 database 생성하기

기존 운영 데이터베이스 케릭터셋 확인

$ psql
psql (12.0)
Type "help" for help.
 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

en_US.UTF-8 로 설정되어 있음

새로운 db를 생성하기 위해 데이터파일 경로 생성

$ mkdir -p /home/postgres/DATA5

데이터파일 경로인 $PGDATA 를 방금 만든 경로로 변경

1	$ export PGDATA=/home/postgres/DATA5/

ko_kr.utf-8 케릭터셋(collate)을 가진 데이터 베이스 생성

$ initdb -D $PGDATA --encoding='utf8' --locale='ko_KR.UTF-8' --lc-collate='ko_KR.UTF-8' --lc-ctype='ko_KR.UTF-8'
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
 
The database cluster will be initialized with locale "ko_KR.UTF-8".
initdb: could not find suitable text search configuration for locale "ko_KR.UTF-8"
The default text search configuration will be set to "simple".
 
Data page checksums are disabled.
 
fixing permissions on existing directory /home/postgres/DATA5 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
 
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
 
Success. You can now start the database server using:
 
    pg_ctl -D /home/postgres/DATA5/ -l logfile start

데이터베이스 기동

$ pg_ctl -D $PGDATA -l logfile start
waiting for server to start.... done
server started

데이터베이스 접속 후 확인

$ psql
psql (12.0)
Type "help" for help.
 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | 
 template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

기존 db와는 다르게 ko_kr.utf-8로 설정되어 있는 모습을 볼 수 있음

참조 :