2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【AWS】RDS for PostgreSQLの「Collate」と「Ctype」を「ja_JP.UTF-8」に変更してみました

Posted at

#はじめに
RDS for PostgreSQL 作成後に、デフォルトのCollateCtypeen_US.UTF-8になっていますが、en_US.UTF-8ja_JP.UTF-8に変更することが本記事の主旨です。

ちなみに、今回使用しているEC2とRDSの情報は下記となります。
・EC2: Amazon Linux 2
・RDS for PostgreSQLエンジンバージョン:12.6

#作業手順
1.postgresql接続ミドルウェアをインストールします
※バージョンはRDS postgresqlに準じてインストールすること

$ sudo amazon-linux-extras install epel -y
$ sudo amazon-linux-extras install postgresql12

2.dbtestDBの現状確認
下記ご覧のように、現在dbtestCollateCtypeen_US.UTF-8になっています。

dbtest=> \l
                                      List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
-----------+-------------+----------+-------------+-------------+-----------------------------
 dbtest    | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                +
           |             |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/XXXXXXXXXXX             +
           |             |          |             |             | XXXXXXXXXXX=CTc/XXXXXXXXXXX
(5 rows)

3.dbtestにテスト用のデータをインポートします

dbtest=> CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
CREATE TABLE
dbtest=> INSERT INTO user_tbl(name, signup_date) VALUES('田中', '2021-07-26');
INSERT 0 1
dbtest=> INSERT INTO user_tbl(name, signup_date) VALUES('鈴木', '2021-07-26');
INSERT 0 1
dbtest=> INSERT INTO user_tbl(name, signup_date) VALUES('太郎', '2021-07-26');
INSERT 0 1
dbtest=> SELECT * FROM user_tbl;
 name | signup_date
------+-------------
 田中 | 2021-07-26
 鈴木 | 2021-07-26
 太郎 | 2021-07-26
(3 rows)

4.dbtestDBのDumpファイルをエクスポートします

[root@ip-XXX-XXX-XXX-XXX ~]# pg_dump -h <RDSエンドポイント> -U<RDSユーザネーム> <DB名> > /tmp/db_bk.dump

5.Dumpファイルがエクスポートされるかどうかを確認します

[root@ip-XXX-XXX-XXX-XXX ~]# cd /tmp
[root@ip-XXX-XXX-XXX-XXX tmp]# ll db_bk.dump
-rw-r--r-- 1 root root 1213 Jul 26 09:35 db_bk.dump

6.dbtestからpostgresへ移動

dbtest=> \l
                                      List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
-----------+-------------+----------+-------------+-------------+-----------------------------
 dbtest    | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                +
           |             |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/XXXXXXXXXXX             +
           |             |          |             |             | XXXXXXXXXXX=CTc/XXXXXXXXXXX
(5 rows)

dbtest=> \c postgres
Password:
psql (12.7, server 12.6)
SSL connection (protocol: TLSv1.2, cipher: XXXX-XXXX-XXXX-XXXX-XXXX, bits: 256, compression: off)
You are now connected to database "postgres" as user "XXXXXXXX".
postgres=>

7.既存のdbtestを削除します

postgres=> DROP DATABASE dbtest;
DROP DATABASE
postgres=> \l
                                      List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
-----------+-------------+----------+-------------+-------------+-----------------------------
 postgres  | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                +
           |             |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/XXXXXXXXXXX             +
           |             |          |             |             | XXXXXXXXXXX=CTc/XXXXXXXXXXX
(4 rows)

postgres=>

8.dbtestを再作成します。
下記ご覧のように、CollateCtypeja_JP.UTF-8に変更しました。

postgres=> CREATE DATABASE dbtest LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
CREATE DATABASE
postgres=> \l
                                      List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
-----------+-------------+----------+-------------+-------------+-----------------------------
 dbtest    | XXXXXXXXXXX | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres  | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                +
           |             |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/XXXXXXXXXXX            +
           |             |          |             |             | XXXXXXXXXXX=CTc/XXXXXXXXXXX
(5 rows)

9.バックアップしたDumpファイルを上記新規作成されたdbtestへインポートします

[root@ip-XXX-XXX-XXX-XXX ~]# psql --host=<エンドポイント> --username=<ユーザネーム> --password --dbname=<DB名> < /tmp/db_bk.dump
Password:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
REVOKE
REVOKE
GRANT
GRANT

10.確認

dbtest=> \l
                                      List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
-----------+-------------+----------+-------------+-------------+-----------------------------
 dbtest    | XXXXXXXXXXX | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres  | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                +
           |             |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | XXXXXXXXXXX | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/XXXXXXXXXXX             +
           |             |          |             |             | XXXXXXXXXXX=CTc/XXXXXXXXXXX
(5 rows)

dbtest=> \d
            List of relations
 Schema |   Name   | Type  |    Owner
--------+----------+-------+-------------
 public | user_tbl | table | XXXXXXXXXXX
(1 row)

dbtest=> select * from user_tbl;
 name | signup_date
------+-------------
 田中 | 2021-07-26
 鈴木 | 2021-07-26
 太郎 | 2021-07-26
(3 rows)

#参考資料

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?