postgres(PostgreSQL)

[uminomae]

team_docs/PostgreSQL

方針

• Docker hub公式イメージのソースをカスタマイズして使う
  • 公式imageをそのまま利用するのが基本で、課題用に少し変える感じでいけたら

  • 参考資料

• dockerfile, entrypoint.sh
  • postgres/15/bullseye/
  • ここからclone(DL)しました
  • 参考:【postgres/15/bullseye at 34d4c14c235806e57fdd5eaf197f718fccee93b0 · docker-library/postgres】 https://github.com/docker-library/postgres/tree/34d4c14c235806e57fdd5eaf197f718fccee93b0/15/bullseye
  • dockerhub
    • 参考:【postgres - Official Image | Docker Hub】 https://hub.docker.com/_/postgres
• tutorial
  • 参考:【チュートリアル】 https://www.postgresql.jp/docs/9.4/tutorial.html

カスタマイズ内容

• コミット abcd372e902c5ac9f0b6017f7a40a0947b8c7d19
  • 環境変数 POSTGRES_HOST_AUTH_METHOD
  • pg_hba.conf を.envで指定した内容に切り替える
  • 理由
    • 開発時はorーカルからのアクセスをtrustにするため
    • 本番環境では scram-sha-256 想定（rtustではwarningが出る）
  • サーバー起動の完了を(logを見なくても一目で)ホストに伝えるためにファイルを出力
  • mount先にfin_postgres_entrypoint.shを出力することにした

[uminomae]

下記logで一旦作業完了とする

2024-02-21 15:23:09 Removing existing /container_output/fin_postgres_entrypoint.txt
2024-02-21 15:23:09 The files belonging to this database system will be owned by user "postgres".
2024-02-21 15:23:09 This user must also own the server process.
2024-02-21 15:23:09 
2024-02-21 15:23:09 The database cluster will be initialized with locale "en_US.utf8".
2024-02-21 15:23:09 The default database encoding has accordingly been set to "UTF8".
2024-02-21 15:23:09 The default text search configuration will be set to "english".
2024-02-21 15:23:09 
2024-02-21 15:23:09 Data page checksums are disabled.
2024-02-21 15:23:09 
2024-02-21 15:23:09 fixing permissions on existing directory /var/lib/postgresql/data ... ok
2024-02-21 15:23:09 creating subdirectories ... ok
2024-02-21 15:23:09 selecting dynamic shared memory implementation ... posix
2024-02-21 15:23:09 selecting default max_connections ... 100
2024-02-21 15:23:09 selecting default shared_buffers ... 128MB
2024-02-21 15:23:09 selecting default time zone ... Etc/UTC
2024-02-21 15:23:10 creating configuration files ... ok
2024-02-21 15:23:11 running bootstrap script ... ok
2024-02-21 15:23:14 performing post-bootstrap initialization ... ok
2024-02-21 15:23:15 syncing data to disk ... ok
2024-02-21 15:23:15 
2024-02-21 15:23:15 
2024-02-21 15:23:15 Success. You can now start the database server using:
2024-02-21 15:23:15 
2024-02-21 15:23:15     pg_ctl -D /var/lib/postgresql/data -l logfile start
2024-02-21 15:23:15 
2024-02-21 15:23:15 waiting for server to start....2024-02-21 06:23:15.793 UTC [52] LOG:  starting PostgreSQL 15.6 (Debian 15.6-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2024-02-21 15:23:15 2024-02-21 06:23:15.795 UTC [52] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-02-21 15:23:15 2024-02-21 06:23:15.823 UTC [55] LOG:  database system was shut down at 2024-02-21 06:23:14 UTC
2024-02-21 15:23:15 2024-02-21 06:23:15.844 UTC [52] LOG:  database system is ready to accept connections
2024-02-21 15:23:15  done
2024-02-21 15:23:15 server started
2024-02-21 15:23:15 
2024-02-21 15:23:15 /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2024-02-21 15:23:15 
2024-02-21 15:23:15 waiting for server to shut down...2024-02-21 06:23:15.960 UTC [52] LOG:  received fast shutdown request
2024-02-21 15:23:15 .2024-02-21 06:23:15.962 UTC [52] LOG:  aborting any active transactions
2024-02-21 15:23:15 2024-02-21 06:23:15.966 UTC [52] LOG:  background worker "logical replication launcher" (PID 58) exited with exit code 1
2024-02-21 15:23:15 2024-02-21 06:23:15.966 UTC [53] LOG:  shutting down
2024-02-21 15:23:15 2024-02-21 06:23:15.967 UTC [53] LOG:  checkpoint starting: shutdown immediate
2024-02-21 15:23:15 2024-02-21 06:23:15.989 UTC [53] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.006 s, sync=0.002 s, total=0.024 s; sync files=2, longest=0.002 s, average=0.001 s; distance=0 kB, estimate=0 kB
2024-02-21 15:23:16 2024-02-21 06:23:16.002 UTC [52] LOG:  database system is shut down
2024-02-21 15:23:16  done
2024-02-21 15:23:16 server stopped
2024-02-21 15:23:16 
2024-02-21 15:23:16 PostgreSQL init process complete; ready for start up.
2024-02-21 15:23:16 
2024-02-21 15:23:15 initdb: warning: enabling "trust" authentication for local connections
2024-02-21 15:23:15 initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
2024-02-21 15:23:16 2024-02-21 06:23:16.100 UTC [7] LOG:  starting PostgreSQL 15.6 (Debian 15.6-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2024-02-21 15:23:16 2024-02-21 06:23:16.101 UTC [7] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-02-21 15:23:16 2024-02-21 06:23:16.101 UTC [7] LOG:  listening on IPv6 address "::", port 5432
2024-02-21 15:23:16 2024-02-21 06:23:16.105 UTC [7] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-02-21 15:23:16 2024-02-21 06:23:16.118 UTC [66] LOG:  database system was shut down at 2024-02-21 06:23:15 UTC
2024-02-21 15:23:16 2024-02-21 06:23:16.127 UTC [7] LOG:  database system is ready to accept connections

[uminomae]

sh test/postgres/sample.sh
Creating database...
ERROR:  database "mydatabase" already exists
Creating table...
ERROR:  relation "users" already exists
Inserting records...
ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (username)=(user1) already exists.
ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (username)=(user2) already exists.
Displaying data...
 id | username |       email       
----+----------+-------------------
  1 | user1    | user1@example.com
  2 | user2    | user2@example.com
(2 rows)