예전에 안수찬 강사님이 페이스북 PostgreSQL Korea 그룹에 한글 정렬 문제로 질문을 올리신 걸 본 적이 있었다. 그때만 해도 그냥 남의 일로 느껴져 별로 주의깊게 보지 않았었는데, 현재 Django로 웹 개발을 하면서 딱 이 문제에 부딪히게 되었고, 그 때 안수찬 강사님이 올리신 질문과 답변을 참고하여 덕분에 쉽게(?) 해결할 수 있었다.
유저 모델에서 username을 기준으로 오름차순 정렬을 하였는데, 아래와 같이 한글 부분이 정렬된 듯하면서 정렬이 안 된(?) 결과가 출력되었다.
데이터베이스의 목록을 출력해보면,
$ psql
jupiny=# \l
Collate라는 컬럼이 있는데, 바로 이 부분이 데이터베이스의 정렬하는 순서를 결정한다. 그냥 데이터베이스를 생성하면 기본적으로 ko_KR.UTF-8 또는 en_US.utf8으로 설정되있음을 볼 수 있다.
해결법은 여기서 데이터베이스의 LC_COLLATE라는 변수 값을 'C'로 바꾸면 된다. 하지만 이는 생성된 데이터베이스에서는 바꿀 수 없으며, 데이터베이스를 최초로 생성할 때 설정을 해주어야 한다.
그래서 어쩔 수 없이 기존의 데이터베이스를 dump하고, LC_COLLATE를 'C'로 설정한 데이터베이스를 새로 만든 후, 이전에 dump한 것을 복구하는 방식으로 해결하여야 한다.
데이터베이스 dump하기
먼저 $ pg_dump 명령어를 실행하기 위해서는 설치된 postgresql의 경로를 알아야 하는데, 이를 위해 아래의 명령어를 실행한다.
$ ps aux | grep postgres
현재 나는 Mac OS X 환경을 쓰고 있고, /usr/local/opt/postgresql/bin/ 경로에 설치 돼있음을 알 수 있다.
$ cd /usr/local/opt/postgresql/bin/
$ pg_dump [DATABASE_NAME] > db.dump
db.dump 파일이 현재 경로에 생성되었음을 확인한다.
$ cat db.dump
dump 파일에 모든 SQL 기록들이 저장되있음을 볼 수 있다.
데이터베이스 새로 생성하기
dump가 성공적으로 됐다면, 기존의 데이터베이스는 삭제하고, 새로 데이터베이스를 생성한다.
$ psql
jupiny=# DROP DATABASE [DATABASE_NAME];
jupiny=# CREATE DATABASE [DATABASE_NAME] LC_COLLATE 'C';
하지만 위의 명령어로 데이터베이스를 생성하면 아래와 같은 에러가 발생한다.
데이터베이스는 생성될 때 기본적으로 template1 데이터베이스를 복제하여 생성된다. 하지만 template1는 locale 관련된 설정이 이미 초기화돼있는 데이터베이스이다. 따라서 template1가 가지고 있는 Collation 설정과 내가 설정한 Collation가 충돌하여 에러가 발생한 것이다.
그래서 template1가 아닌 template0 데이터베이스를 사용하겠다는 옵션을 명시적으로 선언하여야 한다. (template0는 locale 관련된 설정이 초기화돼있지 않은 데이터베이스이다.)
jupiny=# CREATE DATABASE [DATABASE_NAME] TEMPLATE template0 LC_COLLATE 'C';
dump한 데이터 불러오기
이제 다시 만든 데이터베이스에 기존의 데이터들을 불러와야 한다. 먼저 psql에서 나온 후,
jupiny=# \q
아래의 명령어를 실행한다.
$ psql -f db.dump [DATABASE_NAME]
새로 만든 데이터베이스에 접속해서,
$ psql
jupiny=# \connect [DATABASE_NAME]
jupiny=# \d
jupiny=# SELECT * FROM [TABLE_NAME]
기존의 테이블들과 테이블 안의 데이터들이 정상적으로 복구되었음을 확인한다.
그리고 다시 정렬된 결과를 확인해보면 올바른 순서로 정렬된 것을 볼 수 있다.