普段PostgreSQLは全くさわらんのですが、タイトルのようなことを言われたので調べました。

環境


  • PostgreSQL 10.3

また、依頼を受けた環境はRDS for PostgreSQLだったのですが、RDSだとこの方法はできません。なので今回書く方法はあくまで普通のPostgreSQLでのやり方です。

現状を確認してみる


というわけで、とりあえず一旦現状のデータベースを確認してみます。

1
2
3
4
5
6
7
8
9
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

と、まあ、こんな感じ。

要望


デフォルトでCollateCtypeja_JP.UTF-8にして欲しいというものでした。

デフォルトはどうやって決まっているのか


そもそもデフォルトはどうやって決まっているのか…ですが、とりあえず適当にテスト用のデータベースを作成してみましょう。

1
2
3
4
5
6
7
8
9
10
11
12
postgres=> CREATE DATABASE testdb;
CREATE DATABASE

postgres=> \l
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
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

これを見る限りtemplate0template1を基に作成しているっぽいという予想が付きます。

というわけでドキュメントを確認してみます。

実際のCREATE DATABASEの動作は、既存のデータベースをコピーすることです。 デフォルトでは、template1という名前の標準のシステムデータベースをコピーします。

というわけでtemplate1を基に新しいデータベースを作成するようですね。

template1を作り直す


じゃあtemplate1を作り直せばええんや!!というわけでDROPしてみます。

1
2
postgres=> DROP DATABASE template1;
ERROR: cannot drop a template database

templateデータベースはドロップできませんと怒られてしまいます。再度ドキュメントを確認すると下記のように記載されています。

template1を削除するには、pg_database.datistemplate = falseとしなければなりません。

ですので、UPDATEでtemplate1datistemplateをfalseに変更します。

1
2
postgres=> UPDATE pg_database SET datistemplate = false where datname = 'template1';
UPDATE 1

変更されたことを確認します。

1
2
3
4
5
postgres=> select * from pg_database where datname = 'template1';
datname | datdba | encoding | datcollate | datctype | datistemplate |
-----------+--------+----------+-------------+-------------+---------------+-
template1 | 16388 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | ...省略
(1 row)

これでtemplate1をDROPできるようになっているのでDROPします。

1
postgres=> DROP DATABASE template1;

その後にtemplate0を基にしてLC_COLLATELC_CTYPEを指定してtemplate1を作成します。

1
2
3
4
5
//再作成
CREATE DATABASE template1 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;

//再作成したtemplate1をテンプレートDBにする
UPDATE pg_database SET datistemplate = true where datname = 'template1';

これで確認するとtemplate1のCollateとCtypeが下記のようになっていると思います。

1
2
3
4
5
6
7
8
postgres=> \l
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 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres

後は普通にCREATE DATABASEを発行すると残りのDBはtemplate1と同様のカタログ値で作成されます。