docker postgreSQL メモ #Docker

DockerでPostgreSQLを作りPython3 pandasで操作

環境　:
macOS Catalina 10.15.6 で確認、文章書き換え
Docker version 19.03.8, build afacb8b
macOSでの、shellは、zshですが、ホスト側のプロンプトは、　＄　と書いている

ホストに、DBのマウントポイントを作る

現在のディレクトリに volsホルダを作ろ

mkdir ${PWD}/vols

Containerを起動

Container name pg_docker
パスワード設定 postgres POSTGRES_PASSWORD=postgres
ポート host port:container port 5432:5432
volumeをマウント $PWD/vols:/var/lib/postgresql/
- カレントディレクトリのvolsにdockerのpostgresqlをマウント

docker run --rm   --name pg-docker -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v $PWD/vols:/var/lib/postgresql/data  postgres

container の確認

$ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
7dc570e3835c        postgres            "docker-entrypoint.s…"   16 hours ago        Up 16 hours         0.0.0.0:5432->5432/tcp   pg-docker

Container NAMEで、Containerにshellで接続　

containerに接続、Shellを立ち上げる docker exec コンテナに入るとプロンプトが変わります
- -it インターラクティブ ttyを指定
- /bin/bash　　bashを立ち上げる
postgresユーザーになる su - postgres　　また、プロンプトが変わります
一覧を表示する psql -l

$ docker container exec -it pg-docker /bin/bash
root@db8619e414e8:/# su - postgres
postgres@db8619e414e8:~$ psql -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)

createdbでmydbデータベースを作る CREATE文

mydb=# CREATE TABLE weather (
     id serial,
     day date not null,
     city varchar(80) not null,
     temp_max numeric not null,           -- 最低気温
     temp_min numeric not null,           -- 最高気温
     wind real,
    PRIMARY KEY (id)
);

DataBase mydbにpsqlで入りテーブルを作る

psqlでmydbと言うDataBaseに入る
table weatherを作る DREATE TABLE
weather TABLE定義を見る \d Table名
データーを入れる INSERT INTO
データを見る SELECT
psqlから抜ける \q
postgresユーザーから抜ける exit
docker containerから抜ける exit

weather TABLE定義
id 列 serial型　Primary key
day列日付型　空欄不可
temp_max列　numeric型　空欄不可
temp_min列　numeric型　空欄不可
wind列　Real型

postgres@db8619e414e8:~$ psql mydb
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.

mydb=# CREATE TABLE weather (
mydb(#      id serial,
mydb(#      day date not null,
mydb(#      city varchar(80) not null,
mydb(#      temp_max numeric not null,           -- 最低気温
mydb(#      temp_min numeric not null,           -- 最高気温
mydb(#      wind real,
mydb(#     PRIMARY KEY (id)
mydb(# );
CREATE TABLE
mydb=# 

mydb=# \d weather
                                    Table "public.weather"
  Column  |         Type          | Collation | Nullable |               Default               
----------+-----------------------+-----------+----------+-------------------------------------
 id       | integer               |           | not null | nextval('weather_id_seq'::regclass)
 day      | date                  |           | not null | 
 city     | character varying(80) |           | not null | 
 temp_max | numeric               |           | not null | 
 temp_min | numeric               |           | not null | 
 wind     | real                  |           |          | 
Indexes:
    "weather_pkey" PRIMARY KEY, btree (id)

mydb=# INSERT INTO weather VALUES (0,'2019-11-18', 'test',21.3,10.5,0.7);
INSERT 0 1
mydb=# INSERT INTO weather VALUES (1,'2020-07-18', 'test',11.3,7.5,0.7);
INSERT 0 1

mydb=# select * from weather;
 id |    day     | city | temp_max | temp_min | wind 
----+------------+------+----------+----------+------
  0 | 2019-11-18 | test |     21.3 |     10.5 |  0.7
  1 | 2020-07-18 | test |     11.3 |      7.5 |  0.7
(2 rows)


postgres@db8619e414e8:~$ exit
logout
root@db8619e414e8:/# exit
exit
$

imageの確認とcontainerの終了

docker image ls で、最新版のpostgres lateatが取得されている
docker container ls -a コンテナの確認　STATUSで、Up ~~~を確認
docker container stop pg-docker　コンテナを停止
docker container ls -a コンテナの確認　綺麗に片づいている
- runをしたときのオプション --rm が効いている

$ docker image ls
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              4b52913b0a3a        2 days ago          313MB
nginx               latest              0901fa9da894        2 weeks ago         132MB
centos              7                   b5b4d78bc90c        2 months ago        203MB

$ docker container ls -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
db8619e414e8        postgres            "docker-entrypoint.s…"   About an hour ago   Up About an hour    0.0.0.0:5432->5432/tcp   pg-docker

$ docker container stop pg-docker
pg-docker

$ docker container ls -a         
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES

ホストのpythonから、dockerのpostgresSQLに接続 pandas利用

これをやるは、前のcontainerの停止をやらないか、再度 docker container runを行ってください
pip install psycopg2

$ python
Python 3.7.5 (default, Oct 25 2019, 10:52:18) 
[Clang 4.0.1 (tags/RELEASE_401/final)] :: Anaconda, Inc. on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> import pandas as pd
>>> # 接続設定
... conn = psycopg2.connect("dbname=mydb host=localhost port=5432 user=postgres password=postgres")
>>> 
>>> query = "SELECT * FROM weather;"
>>> df = pd.read_sql(sql=query, con=conn)
>>> df
   id         day  city  temp_max  temp_min  wind
0   0  2019-11-18  test      21.3      10.5   0.7
1   1  2019-12-18  test      11.3       7.5   0.7
>>> exit()
$

接続設定 conn

conn = psycopg2.connect()
- dbname=DataBase名 or database=DataBase名
- user=ユーザー名
- password=パスワード
- host=URL
- port=ポート番号 defaultの場合省略可

Pandas pd.resd_sql( SQLクエリ , 接続設定 )

参照 PostgreSQL in Docker & Docker Volume Mounting
(https://medium.com/faun/postgresql-in-docker-mount-volume-3220fbd0afc4)