1
3

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 5 years have passed since last update.

@mirawerra(mouko damashii)

Windows版PostgreSQLをdocker版に移行した話

1
Posted at

Windows 10PCで動作しているPostgreSQL9.6のDBを、同じPCのUbuntu18.04.2 LTS VM内のdocker版PostgreSQL11.2に移行したときのメモになります。

要は「Win10上のPG9.6でダンプ → Linuxのdocker版PG11.2にインポート」です。

※時々しか使わないのでVM上に、ついでにdocker学習も兼ねて移してみたかった、という簡単な動機からｯｽ...

1.環境

  • 移行元環境

    • Windows 10版PostgreSQL 9.6 (pgAdmin4も使用)
    • DBは1個
    • テーブルは3つ（だいたい各130～160万レコード）
    • マテビューは1つ。（上の3テーブル集計用）
    • DBユーザ(role)はpostgres以外には1つだけ
    • ちなみに内容は趣味で作ったセンサーデータ（Raspberry Piで取得した気温とか湿度等）

  • 移行先環境

    • VirtualBox6.04上に構築したUbuntu18.04.2 LTS VM
    • の中で、公式postgreSQL11.2のdockerイメージを使用したコンテナ
    • 表領域はそのままpg_defaultを使用（どのみちコンテナ内しか見えないし）

2. 手順(移行元/Windows 10)

控えておくこと

  • DBの名前
  • roleの定義(pgAdminのrole画面でも分かる、CREATE USER role名 WITH何々、みたいなの）
  • roleのパスワード(postgresと自前定義のと)

で、コマンドプロンプトからこんな感じでダンプします。

C:\Program Files\PostgreSQL\9.6\bin>pg_dump.exe -U postgres DB名称 > dumpfile.sql
パスワード:(postgresロールのパスワードを入力します)

3. 手順(移行先/Ubuntu)

※dockerのインストール手順は省略（普通にapt installで）

コンテナで使うボリュームを先に作成します。
コンテナ作成時に自動的に生成もされるのですが、その場合ボリュームの名前が「7a6dc02b01ad9c8dcb1958dcf18a24b5d68cdc・・」などというふうになってしまって、他のコンテナ用ボリュームとの区別が分かりにくくなることから、「わかりやすい名前で」ボリュームをコンテナ作成より前につくっておきます。

$sudo docker volume create --name 任意のボリューム名
$sudo docker volume create --name pgvol1 とか

確認します。

$sudo docker volume ls
DRIVER              VOLUME NAME
local               pgvol1

Volume作成ヨシ、ってことで、コンテナをつくります。

PostgreSQL11.2のコンテナイメージをpullします。

$docker pull postgres:11.2

これをもとにコンテナをつくります。

$sudo docker run -it --name pg11db -v pgvol1:/var/lib/postgresql/data -v /etc/localtime:/etc/localtime:ro -p 5432:5432 -e POSTGRES_PASSWORD=passmoji -d postgres:11.2
  • コンテナ名：-name pg11db
  • ボリューム名指定1（さっき作成したDB格納用）：-v pgvol1:/var/lib/postgresql/data
  • ボリューム名指定2（お好みでコンテナでホストと同じ時刻を使いたいときに読み込み専用で）：-v /etc/localtime:/etc/localtime:ro
  • PostgreSQLに接続するためのポート番号（コロンの左がコンテナ外部から用、右がコンテナ内部用）：-p 5432:5432
  • postgresロールのパスワード（移行元と同じでも別でもお好み）：-e POSTGRES_PASSWORD=passmoji
  • コンテナをバックグラウンドで実行する：-d
  • もとにするコンテナイメージ(上でpullしたもの）：postgres:11.2

コンテナが起動したこと(STATUSがUPである)と、コンテナ生成時に別のボリュームを自動作成していないことを確認します。
（例えばマウントポイントが間違っていたような場合に「7a6dc02b01ad9c8dcb1958dcf18a24b5d68cdc・・」みたいな名前のボリュームが一覧に出てきます）

$sudo docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
59a1e695dcd0        postgres:11.2       "docker-entrypoint.s…"   16 seconds ago      Up 15 seconds       0.0.0.0:5432->5432/tcp   pg11db


$sudo docker volume ls

DRIVER              VOLUME NAME
local               pgvol1

ここらへんで、最初にWin10上で出力したダンプファイルdumpfile.sqlを適当な手段でLinux上にもってきます。（わたしは普通にWinSCPで転送しました）

コンテナのpsqlをpostgresユーザで実行します。
psqlのコマンドが表示されたら、create database文で移行元と同じ名前でデータベースを作成します。
続けて最初で控えておいたユーザー(role)の定義文を貼り付けます。
忘れずにalter roleでパスワードもセットします。

$sudo docker exec -it pg11db psql -U postgres
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

postgres=# create database DB名;
CREATE DATABASE
postgres=#
postgres=# CREATE USER USERNAME123 WITH
postgres-#   LOGIN
postgres-#   NOSUPERUSER
postgres-#   INHERIT
postgres-#   CREATEDB
postgres-#   CREATEROLE
postgres-#   NOREPLICATION;
CREATE ROLE
postgres=# ALTER ROLE USERNAME123 WITH PASSWORD 'パスワード文字列';
ALTER ROLE
postgres=#

※と書いてて、私alter user...という古い書き方でパスワード指定していたのにログ見て気づきましたｗ

\qでpsqlから抜けます。

次はコンテナ内へダンプファイルをコピーします。

（コンテナにコピーしなくても、ホスト側からリダイレクトで渡す方法もあるようです。...あとから知りました）

$sudo docker cp dumpfile.sql pg11db:/tmp/dumpfile.sql

ホストのカレントディレクトリにあるdumpfile.sqlを、pg11dbコンテナ内で見える/tmpにコピーする、という意味です。

コンテナにbashで入って、psqlコマンドでダンプファイルを読み込みます。
（ここもあとから考えるとexecしたpsqlに引数渡せば良かっただけのような）

ホストで
$sudo docker exec -i -t pg11db bash

コンテナのbash上からpsqlコマンドでインポートします。
$psql DBNAME < /tmp/dumpfile.sql

SET
～略～
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
～略～
CREATE MATERIALIZED VIEW
～略～
REFRESH MATERIALIZED VIEW
GRANT
～以下略～

特にあやしげなメッセージが無いようであれば、SQL文でDBにレコードがあるかpsqlで確認します。コンテナ内およびコンテナ外両方から同様に。

postgres=# \c DBNAME
You are now connected to database "DBNAME" as user "postgres".
DBNAME=# \coninfo
You are connected to database "DBNAME" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
DBNAME=# select count(*) from スキーマ名.適当な表名;
  count  
---------
 1356406
(1 row)

bashを抜ける前にダンプファイルdumpfile.sqlをお好みで削除します。
exitでホストに戻ります。

コンテナを再起動して、DB内容が見られる（＝ボリューム内容が消えていない）ことを確認します。

$sudo docker stop pg11db
$sudo docker start pg11db

適当にpsqlでつないでみる

インポートはここまでになります。

3. 楽しよう

ちょっとしたものを作っておいて、使用時に楽しようというアレです。

3.1 マテビュー更新

コンテナ側に

\c DBNAME
REFRESH MATERIALIZED VIEW  スキーマ名.マテビュー名 with data;

というテキストファイルを置きます。
(1行目はDBNAME、というDBに移動。2行目はマテビュー更新）
で、ホスト側から

$sudo docker exec -i -t -u postgres pg11db psql -f そのファイル名

とするとマテビュー更新が出来ます。本当はDB側の適当なトリガーで自動更新してほしいのですが。

ちなみにこのマテビュー更新、Win10環境のときの2,3倍は時間がかかる印象です。はて。

3.2 バックアップ

コンテナのDBをダンプする方法です。
ホスト側にこんなシェルスクリプト（雑）を作り、

a="`date "+dumpall%Y%m%d_%H%m%S.sql"`"
sudo docker exec -i -t -u postgres pg11db pg_dump -U postgres DBNAME >/home/XXXX/$a
gzip /home/XXXX/$a

実行すると、ホスト側にdumpall年月日_時刻.sqlって名前でダンプ出力＆圧縮できます。
（インポートのときこれの逆をやればよかったようです）

以上です。

見ていただきありがとうございました。

1
3
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
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?