docker postgresql バックアップ・リストアで文字化けが起こる

Question

環境
windows10

前提・実現したいこと

docker上のpostgresql(漢字など日本語を含む)からバックアップをして、別のdocker上のpostgresqlへリストアしたいです。

発生している問題・エラーメッセージ

バックアップ先のdockerコンテナで

docker-compose exec db pg_dumpall -U postgres > dump.sql

(dbはコンテナ名)
をしてdump.sqlを作ります。

リストア先のdockerコンテナで

cat dump.sql | docker exec -i db psql -U postgres

をしてリストア

リストア先のpostgresqlを中が文字化けしてしまいます。
具体的には漢字やひらがななどの日本語部分が「?」になっています。
dump.sqlの中身を見ると日本語部分が文字化けしていました。

リストア時に下記の文章がでます。

ERROR:  syntax error at or near "Unable"
LINE 1: Unable to close the console

試したこと

バックアップ時のコマンドを変えてみました(-E SJISを追加)

docker-compose exec db pg_dumpall -U postgres -E SJIS > dump.sql

これだとdump.sqlの中身は文字化けしていませんが、リストア後に日本語部分が「?」の文字になってしまいました。

試したこと2

データベースのコンテナに入り、エンコーディングを確認しました。

docker-compose exec db bash (Docker上のPostgreSQLコンテナに入る)
su - postgres (ルートユーザーをpostgresに変更)
psql postgres (データベースに入る)
\l

上の手順で下記結果が得られました。

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

試したこと3

また、docker立ち上げの際のymlファイルを書き換えて下記の状態にもしましたが、dump.sql作成時の文字化けは解消されませんでした、、、

  db:
    build: ./db #日本語用postgres dockerfile読み込み
    container_name: db
    ports:
      - "5432:5432"
     # トップレベルで指定したvolumeをマウントする
    volumes:
      - "dbdata:/var/lib/postgresql/data"
    environment:
      POSTGRES_PASSWORD: password # settings.pyで設定した値と同じにする
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=ja_JP.UTF-8" # ←追記 Localeの初期値

と設定ファイルを書き換えて得られたものが↓

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

試したこと4

  db:
    image: postgres:11
    container_name: db
    ports:
      - "5432:5432"
     # トップレベルで指定したvolumeをマウントする
    volumes:
      - "dbdata:/var/lib/postgresql/data"
    environment:
      POSTGRES_PASSWORD: password # settings.pyで設定した値と同じにする
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C" # ←追記 Localeの初期値

と設定ファイルを書き換えて得られたものが↓

                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

この状態でもdump.sql作成時の解消されませんでした、、、

試したこと4

postgresqlの中で「SHOW client_encoding;」コマンドを打つと、
「SQL_ASCII」が出力されました。
おそらくclient_encodingの設定を変えればうまくいくのではと思います。
そのためにpostgresql.confを書き換える必要があるようです。
https://qiita.com/ihatov08/items/72bb5bd4feeef87e77a5
↑を参考にして
postgresql.confの中身を

#client_encoding = sql_ascii 
↓
client_encoding = utf8

に書き換えましたが、SHOW client_encoding;→SQL_ASCIIは変更されませんでした。
どうやらpostgresql.confよりもpostgresql.auto.confの設定のほうが優先されるらしく、postgresql.auto.confの設定を変える方法を現在調べ中です。

補足情報

また、
docker-compose exec db bash (Docker上のPostgreSQLコンテナに入る)
su - postgres (ルートユーザーをpostgresに変更)
psql postgres (データベースに入る)
\encoding

では下記の結果が得られました。

SQL_ASCII

補足情報2

下記サイトを参考にしました。
https://qiita.com/dtan4/items/5147a3f858d5919965c9

補足情報3

dump.sqlの中身は↓です。
https://github.com/masuda-u/dockerPostgresql/blob/main/dump.sql

Comments

[退会済みユーザー]

バックアップとしてとれた dump.sql で文字エンコーディングの扱いがどうなっているのでしょうか？ そもそもPostgreSQL立ち上がりでUTF-8になっているのかどうかの確認も必要かもしれません。

[neconekocat]

リストア後にログインしてDBのエンコーディングを確認したら何になっていますか？

[masuda-u]

dump.sqlを開いて中身を確認したところ
SET client_encoding = 'UTF8';
と表記されていました。

リストア後にPowerShellで、
docker-compose exec db bash (Docker上のPostgreSQLコンテナに入る)
su - postgres (ルートユーザーをpostgresに変更)
psql postgres (データベースに入る)
\encoding
の手順で確認したところ、「SQL_ASCII」になっていました。

よろしくお願いいたします。

[showkit]

もし、docker でない環境を作れるならば、docker でない環境でやってみるとよいかと思います。 少なくとも、docker であることが要因なのか 他に要因が あるのかを切り分けることができます。

[masuda-u]

showkitさん
windowsに直接postgresをインストールし、dockerでない環境で同じ作業をしてみました。
docker外だと、client_encodingが「SJIS」のときに文字化けせずにdumpすることができました。

docker環境だとclient_encodingがSQL_ASCIIに設定されているので、これが原因かと思います。
まだdocker環境でclient_encodingを変更する方法が分からないので、調べてみます。

Answer 1

dump.sql の中でデータベースそのものを作っているのであれば、
dump.sql 内で「CREATE DATABASE」している箇所を見つけ、
そこでエンコーディング指定がどうなっているかを確認します。

CREATE DATABASE

ENCODING句、LC_COLLATE句、LC_CTYPE句が
日本語処理に適しているものになっているかどうかです。

ENCODING = 'UTF8'
LC_COLLATE = 'C'
LC_CTYPE = 'C'

でもとりあえず日本語を格納することはできますが、
文字タイプや照合順序を適切にすることでよりふさわしい処理ができるようになるかと。

23.3.1. サポートされる文字セット
PostgreSQL Databaseまとめ - Qiita

---

「docker postgresql 日本語」などとして検索すると、
PostgreSQL上で日本語を扱えるようにする実例を紹介している記事が見つかったりしますので、参考まで。

Comments

[masuda-u]

dump.sql の中を見てみましたが、「CREATE DATABASE」「ENCODING 」「LC_COLLATE 」「LC_CTYPE 」の句はありませんでした、、、

githubにdump.sqlの中身を載せましたので、ご確認いただければ幸いです。
https://github.com/masuda-u/dockerPostgresql/blob/main/dump.sql

[退会済みユーザー]

あーたしかに。
ではどこかでcreatedbコマンドを叩いている箇所がスクリプトのどこかにあるかも調べてみてください。
postgresqlをインストールしただけだとデータベースは存在しなくて、
コンソールでcreatedbコマンドを叩くか、psqlコマンドでpostgresユーザーで接続している上でcreate database文を実行することで、データベースが出来るものと私は認識しています。
それがdockerになったからと言って、勝手にデータベースが作られるとは考えにくいのですが。
postgresっていうデータベースを使っているのだとしたら、Encoding UTF8、Collate en_US.utf8、Ctype en_US.utf8 では当然文字化けしますので、
コンテナ作成スクリプトでcreatedbコマンドにて日本語を扱えるEncoding, Collate, Ctypeなデータベースを作成することが必要です。
（英語圏のドキュメントに則ってやってると陥りがち。）

[masuda-u]

docker立ち上げの際に、ymlファイルという設定ファイルを読んで立ち上げるのですが、
db:
image: postgres:11
container_name: db
ports:
- "5432:5432"
# トップレベルで指定したvolumeをマウントする
volumes:
- "dbdata:/var/lib/postgresql/data"
environment:
POSTGRES_PASSWORD: password # settings.pyで設定した値と同じにする
↑がymlファイルのデータベース設定部分ですが、
おそらくここでデータベースの作成をしていると思います。

ymlファイルを書き換えて、environmentに
POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=ja_JP.UTF-8"を追加してみたところ

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

の状態になったのですが、
dump.sqlの文字化けは解消されませんでした、、、
(データベースを直接叩いたときは正常に表示されます。バックアップでsqlファイルにすると文字化けします)

また、POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"に書き換えて

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

も試してみましたが、解消されませんでした、、、

[masuda-u]

すみません、コメントに書くとインデントがズレるので同じものを質問文にも書きます

[退会済みユーザー]

POSTGRES_INITDB_ARGSを修正することで、エンコーディングほかが直ったのはいいと思う。
ところで、文字化けしていると認識しているのは、何をどう確認しているのだろう、そこがわからない。
en_US.utf8時代に作ったデータならもう直らないので、ja_JP.UTF-8になっているpostgresデータベースで最初からやらないと行けないのでは？

[masuda-u]

githubに上げましたdump.sql
https://github.com/masuda-u/dockerPostgresql/blob/main/dump.sql
これの664行目が「蛟牙ｺｫ 蟾ｴ」と文字化けしています。

本来ここには日本語が入っているのですが、dump.sqlとして出力すると文字化けしてしまいます、、
(PowerShellで直接データベースを叩いてデータを表示させたときは正常に表示されます。dump.sqlとして出力する場合に文字化けが発生します)

一応、「ja_JP.UTF-8」と「C」に変えてから新しくデータを入れなおしてみたのですが、文字化けは解消されませんでした。

[退会済みユーザー]

「蛟牙ｺｫ」はSJISで「倉庫」ですから、SJISでpg_dump（or pg_dumpall）していませんか？

[masuda-u]

docker-compose exec db pg_dumpall -U postgres -E UTF8 > dump.sql
とエンコーディングをUTF8に指定したときは「蛟牙ｺｫ」になります。

docker-compose exec db pg_dumpall -U postgres -E SJIS > dump.sql
とエンコーディングをSJISにしたときは「倉庫」にちゃんとなるのですが、

cat dump.sql | docker exec -i db psql -U postgres
でリストアするとデータベース上で「????」という文字列になってしまいます。
(SJISの場合「??」になります)

バックアップ元のDB「倉庫」→UTF8でpg_dumpall「蛟牙ｺｫ」→リストア先のDB「????」
バックアップ元のDB「倉庫」→SJISでpg_dumpall「倉庫」→リストア先のDB「??」
という状況です。

[退会済みユーザー]

もしかして、DBに日本語をSJISもしくはSJIS-winで保持している？
データベース全体でSJIS-winしているなら、当然UTF-8系じゃなくSJIS-winでやるべき、って話になりますよ？

[synydy]

エンコーディングをUTF8にしてpg_dumpallしたらdump.sqlの「倉庫」が文字化けする理由は、dump.sqlにデータをUTF8で出力しているにもかかわらず、UTF8に対応していない環境でdump.sqlの中身を確認しているからだと思います。
PowerShell上でPowerShellのエンコーディングを変更することなくdump.sqlを確認しているということはありませんか。PowerShellのデフォルトエンコーディングはUS-ASCIIなので、UTF8を正しく表示できません。
Win 10をお使いとのことですから、メモ帳はUTF8に対応しているはずです。たとえば、dump.sqlをメモ帳で開いたらUTF8のdump.sqlでも文字化けせずに読めると思います。

補足1
公式ドキュメントの表に記載の通り、PostgreSQLはSJISでデータベースにデータを格納できません。

[23.3. 文字セットサポート]
https://www.postgresql.jp/document/11/html/multibyte.html#MULTIBYTE-TRANSLATION-TABLE
> SJIS サーバの符号化方式としてはサポートしていません。

補足2
GitHubに上げているdump.sqlの最終行は、pg_dumpallが出力したものではありません。
PowerShellかDockerが出力したエラーメッセージかもしれません。

https://github.com/masuda-u/dockerPostgresql/blob/main/dump.sql#L1181
> Unable to close the console

このファイルを使ってリストアすると、Unable to close the consoleというSQL文は存在しないのでsyntax errorが発生して、リストアが成功裏に終了しないはずです。
削除しておいたほうがよいと思います。

[masuda-u]

synydyさん

dump.sqlをメモ帳で開いた場合も、文字化けは解消しませんでした、、

[masuda-u]

m6uさん

postgresql内で「SHOW client_encoding;」を打ったところ、
client_encoding が SQL_ASCII になっていたのでもしかするとここに原因があるのかもしれません。
client_encodingを変更する方法を調べてみます。

[退会済みユーザー]

psql上のメタコマンドであれば、\encoding UTF-8などとすればひとまずエンコーディングを変えられますけれども。

[masuda-u]

\encoding UTF-8はすでに試していて、一時的にエンコーディングを変えられることは確認しているのですが、dockerを立ち上げ直すと元のSQL_ASCIIに戻ってしまいます、、
docker立ち上げ時に自動で\encoding UTF-8にする設定が必要だと思いました。
postgresql.auto.confファイルにclient_encodingの設定を書けばできそうなのですが、まだ調べ中です、、