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

環境
windows10

前提・実現したいこと

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

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

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

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

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

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

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

をしてリストア

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

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

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

試したこと

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

PowerShell
1docker-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

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

PowerShell
1                                 List of databases
2   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
3-----------+----------+----------+------------+------------+-----------------------
4 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
5 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
6           |          |          |            |            | postgres=CTc/postgres
7 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
8           |          |          |            |            | postgres=CTc/postgres
9(3 rows)

試したこと3

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

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

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

PowerShell
1                                  List of databases
2   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
3-----------+----------+----------+-------------+-------------+-----------------------
4 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
5 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
6           |          |          |             |             | postgres=CTc/postgres
7 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
8           |          |          |             |             | postgres=CTc/postgres
9(3 rows)

試したこと4

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

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

PowerShell
1                             List of databases
2   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
3-----------+----------+----------+---------+-------+-----------------------
4 postgres  | postgres | UTF8     | C       | C     |
5 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
6           |          |          |         |       | postgres=CTc/postgres
7 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
8           |          |          |         |       | postgres=CTc/postgres
9(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

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

PowerShell
1SQL_ASCII

補足情報2

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

補足情報3

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

退会済みユーザー

2021/06/28 00:48

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

neconekocat

2021/06/28 00:57

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

masuda-u

2021/07/05 04:28

dump.sqlを開いて中身を確認したところ SET client_encoding = 'UTF8'; と表記されていました。リストア後にPowerShellで、 docker-compose exec db bash (Docker上のPostgreSQLコンテナに入る) su - postgres (ルートユーザーをpostgresに変更) psql postgres (データベースに入る) \encoding の手順で確認したところ、「SQL_ASCII」になっていました。よろしくお願いいたします。

showkit

2021/07/13 05:47

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

masuda-u

2021/07/15 02:07

showkitさん windowsに直接postgresをインストールし、dockerでない環境で同じ作業をしてみました。 docker外だと、client_encodingが「SJIS」のときに文字化けせずにdumpすることができました。 docker環境だとclient_encodingがSQL_ASCIIに設定されているので、これが原因かと思います。まだdocker環境でclient_encodingを変更する方法が分からないので、調べてみます。

行動規範の内容に同意します

回答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上で日本語を扱えるようにする実例を紹介している記事が見つかったりしますので、参考まで。

投稿2021/07/05 06:46

編集2021/07/07 00:50

退会済みユーザー

総合スコア0

masuda-u

2021/07/07 00:37 編集

dump.sql の中を見てみましたが、「CREATE DATABASE」「ENCODING 」「LC_COLLATE 」「LC_CTYPE 」の句はありませんでした、、、 githubにdump.sqlの中身を載せましたので、ご確認いただければ幸いです。 https://github.com/masuda-u/dockerPostgresql/blob/main/dump.sql

退会済みユーザー

2021/07/07 00:47 編集

あーたしかに。ではどこかで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

2021/07/08 01:09

masuda-u

2021/07/08 01:10

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

退会済みユーザー

2021/07/08 01:20 編集

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

masuda-u

2021/07/08 01:31

githubに上げましたdump.sql https://github.com/masuda-u/dockerPostgresql/blob/main/dump.sql これの664行目が「蛟牙ｺｫ蟾ｴ」と文字化けしています。本来ここには日本語が入っているのですが、dump.sqlとして出力すると文字化けしてしまいます、、 (PowerShellで直接データベースを叩いてデータを表示させたときは正常に表示されます。dump.sqlとして出力する場合に文字化けが発生します) 一応、「ja_JP.UTF-8」と「C」に変えてから新しくデータを入れなおしてみたのですが、文字化けは解消されませんでした。

退会済みユーザー

2021/07/08 05:23

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

masuda-u

2021/07/08 06:12

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「??」という状況です。

退会済みユーザー

2021/07/08 07:32

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

synydy

2021/07/11 15:21 編集

エンコーディングを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

2021/07/15 01:18

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

masuda-u

2021/07/15 01:21

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

退会済みユーザー

2021/07/15 01:26

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

masuda-u

2021/07/15 02:16

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

行動規範の内容に同意します