db接続エラー(docker-compose+flask+uwsgi+nginx+postgresql)を解消したい

実現したいこと

uwsgi(flask)コンテナ、nginxコンテナ、postgresqlコンテナ、の３つをdocker-composeで連携させて、webアプリ(ログイン機能付き)を作りたいです。

前提

PC環境は、windows10+docker-desktop+wsl2+ubuntuです。
webブラウザで
”http://localhost:8080"
にアクセスして、topページを表示するのは成功しました。redirect機能も上手くいっているみたいです。htmlやcss側にも問題はないと思います。

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

新規登録やログインのフォームに入力してPOSTすると、”500 Internal Server Error"が出てしまいます。

エラーメッセージ

ブラウザ：
Internal Server Error
The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

postgresqlコンテナ(Docker-Desktop):
2023-05-03 02:59:46.176 UTC [33] FATAL: password authentication failed for user "postgres"
2023-05-03 02:59:46.176 UTC [33] DETAIL: Role "postgres" does not exist.

該当のソースコード

フォルダ構成

   test/
    ├ uwsgi/
    │  ├ config/
    │  │  ├ requirements.txt
    │  │  └ uwsgi.ini
    │  ├ application/
    │  │  ├ __pycache__/
    │  │  ├ static/
    │  │  ├ templates/
    │  │  └ testApp.py
    │  └ Dockerfile
    ├ postgresql/
    │  ├ data/
    │  ├ docker-entrypoint-initdb.d/
    │  │  ├ 1_create_table.sql
    │  │  └ 2_insert.sql
    │  └ Dockerfile
    ├ nginx/
    │  ├ config/
    │  │  └ nginx.conf
    │  └ Dockerfile
    └ docker-compose.yml

uwsgiフォルダ

requirements.text
1Flask==2.2.3
2Flask-Login==0.6.2
3psycopg2==2.9.6
4uwsgi==2.0.21
5

uwsgi.ini
1[uwsgi]
2master=true
3socket=:3031
4chdir=/var/www
5wsgi-file=application/testApp.py
6callable=app
7logto=/var/log/uwsgi.log
8

testApp.py
1from flask import Flask, request, Response, redirect, render_template
2from flask_login import LoginManager, UserMixin, login_user, logout_user, current_user
3import psycopg2
4
5app = Flask(__name__, static_folder='./static')
6app.config['SECRET_KEY'] = 'testApp_secret_key'
7
8login_manager = LoginManager()
9login_manager.init_app(app)
10
11class User(UserMixin):
12    def __init__(self, id):
13        self.id = id
14
15@login_manager.user_loader
16def load_user(user_id):
17    return User(user_id)
18
19@app.route('/')
20def index():
21    return render_template('index.html')
22
23@app.route('/new_user', methods=['GET','POST'])
24def new_user():
25    if request.method == 'POST':
26        userID = request.form.get('userID')
27        password = request.form.get('password')
28        username = request.form.get('username')
29        connection = psycopg2.connect(
30            host="test_postgresql",
31            port=5432,
32            dbname="postgres",
33            user="postgres",
34            password="postgres"
35        )
36        cursor = connection.cursor()
37        cursor.execute(f"INSERT INTO accounts VALUES ('{userID}','{password}','{username}')")
38        connection.commit()
39        connection.close()
40        return redirect('/login?state=0')
41    else:
42        return render_template('new_user.html')
43
44@app.route('/login', methods=['GET','POST'])
45def login():
46    if current_user.get_id() != None:
47        logout_user()
48        return redirect('/login?state=0')
49    else:
50        if request.method == 'POST':
51            userID = request.form.get('userID')
52            password = request.form.get('password')
53            connection = psycopg2.connect(
54                host="test_postgresql",
55                port=5432,
56                dbname="postgres",
57                user="postgres",
58                password="postgres"
59            )
60            cursor = connection.cursor()
61            cursor.execute(f"SELECT * FROM accounts WHERE ID = '{userID}'")
62            result1 = cursor.fetchone()
63            connection.commit()
64            if result1 == None:
65                connection.close()
66                return redirect('/login?state=1')
67            else:
68                cursor.execute(f"""SELECT * FROM accounts
69                        WHERE ID = '{userID}' AND PASS = '{password}'""")
70                result2 = cursor.fetchone()
71                connection.commit()
72                if result2 == None:
73                    connection.close()
74                    return redirect('/login?state=2')
75                else:
76                    connection.close()
77                    user = User(result2[0])
78                    login_user(user)
79                    return redirect('/menu')
80        else:
81            state = request.args.get('state', default=0, type=int)
82            return render_template('login.html', state=state)
83
84@app.route('/logout')
85def logout():
86    if current_user.get_id() == None:
87        return redirect('/login?state=0')
88    else:
89        logout_user()
90        return redirect('/')
91
92@app.route('/menu')
93def menu():
94    if current_user.get_id() == None:
95        return redirect('/login?state=0')
96    else:
97        return render_template('menu.html')
98
99@app.route('/english_top')
100def english_top():
101    if current_user.get_id() == None:
102        return redirect('/login?state=0')
103    else:
104        return render_template('english_top.html')
105
106@app.route('/en_wordlist', methods=['GET','POST'])
107def en_wordlist():
108    if current_user.get_id() == None:
109        return redirect('/login?state=0')
110    else:
111        listNo = request.args.get('listNo', default=1, type=int)
112        wordlist = []
113        connection = psycopg2.connect(
114            host="test_postgresql",
115            port=5432,
116            dbname="postgres",
117            user="postgres",
118            password="postgres"
119        )
120        cursor = connection.cursor()
121        for x in range(5):
122            searchNo = 5*(listNo-1)+x+1
123            cursor.execute(f"SELECT * FROM en_words WHERE Num = {searchNo}")
124            result = cursor.fetchone()
125            wordlist.append({'No':result[0],'Word':result[1]})
126        connection.commit()
127        connection.close()
128        return render_template('en_wordlist.html', listNo=listNo, wordlist=wordlist)
129

Dockerfile
1FROM python:3.11
2
3WORKDIR /var/www
4COPY . .
5
6RUN pip install --no-cache-dir -r /var/www/config/requirements.txt
7

postgresqlフォルダ

1_create_teble.sql
1create table if not exists accounts (
2  ID text,
3  PASS text,
4  UserName text
5);
6
7create table if not exists en_words (
8  Num int,
9  Word text
10);
11

2_insert.sql
1insert into en_words (Num, Word) select 1, 'Word1' where not exists (select * from en_words where Num = 1);
2insert into en_words (Num, Word) select 2, 'Word2' where not exists (select * from en_words where Num = 2);
3insert into en_words (Num, Word) select 3, 'Word3' where not exists (select * from en_words where Num = 3);
4insert into en_words (Num, Word) select 4, 'Word4' where not exists (select * from en_words where Num = 4);
5insert into en_words (Num, Word) select 5, 'Word5' where not exists (select * from en_words where Num = 5);
6insert into en_words (Num, Word) select 6, 'Word6' where not exists (select * from en_words where Num = 6);
7insert into en_words (Num, Word) select 7, 'Word7' where not exists (select * from en_words where Num = 7);
8insert into en_words (Num, Word) select 8, 'Word8' where not exists (select * from en_words where Num = 8);
9insert into en_words (Num, Word) select 9, 'Word9' where not exists (select * from en_words where Num = 9);
10insert into en_words (Num, Word) select 10, 'Word10' where not exists (select * from en_words where Num = 10);
11insert into en_words (Num, Word) select 11, 'Word11' where not exists (select * from en_words where Num = 11);
12insert into en_words (Num, Word) select 12, 'Word12' where not exists (select * from en_words where Num = 12);
13insert into en_words (Num, Word) select 13, 'Word13' where not exists (select * from en_words where Num = 13);
14insert into en_words (Num, Word) select 14, 'Word14' where not exists (select * from en_words where Num = 14);
15insert into en_words (Num, Word) select 15, 'Word15' where not exists (select * from en_words where Num = 15);
16

Dockerfile
1FROM postgres:latest
2

nginxフォルダ

nginx.conf
1user  nginx;
2worker_processes  auto;
3error_log  /var/log/nginx/error.log warn;
4pid        /var/run/nginx.pid;
5
6events {
7    worker_connections  1024;
8}
9
10http {
11
12    include       /etc/nginx/mime.types;
13    default_type  text/html;
14    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
15                      '$status $body_bytes_sent "$http_referer" '
16                      '"$http_user_agent" "$http_x_forwarded_for"';
17    access_log  /var/log/nginx/access.log  main;
18    sendfile       on;
19    tcp_nopush     on;
20    tcp_nodelay    on;
21    keepalive_timeout  65;
22
23    server {
24        listen 8080 default;
25        charset utf-8;
26        server_name _;
27
28        location / {
29            client_max_body_size 1m;
30            client_body_buffer_size 8k;
31
32            include uwsgi_params;
33            uwsgi_pass test_uwsgi:3031;
34        }
35
36        error_page   500 502 503 504  /50x.html;
37        location = /50x.html {
38            root   /usr/share/nginx/html;
39        }
40    }
41}
42

Dockerfile
1FROM nginx:latest
2

docker-compose.yml

version: '3'

services:

  postgresql:
    container_name: test_postgresql
    build: ./postgresql
    ports:
      - "5432:5432"
    volumes:
      - ./postgresql/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
      - ./postgresql/data:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    networks:
      - test_network

  uwsgi:
    container_name: test_uwsgi
    build: ./uwsgi
    expose:
      - "3031"
    volumes:
      - ./uwsgi:/var/www
    depends_on:
      - postgresql
    command: uwsgi --ini /var/www/config/uwsgi.ini
    networks:
      - test_network

  nginx:
    container_name: test_nginx
    build: ./nginx
    restart: always
    volumes:
      - ./nginx/config/nginx.conf:/etc/nginx/nginx.conf
    depends_on:
      - uwsgi
    ports:
      - "8080:8080"
    networks:
      - test_network

networks:
  test_network:

試したこと

コンテナの起動の順序が接続不良の原因かもしれないと思い、waitfordbコンテナを追加したりしましたが、上手くいきませんでした。
参照：https://qiita.com/sey323/items/a4875408a67cea6a8c52#%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%AB

回答1件

自己解決

自己解決できました。
postgresqlのdataフォルダ内に余分なデータが貯まっていると、コンテナの生成や起動を行うときに、docker-entrypoint-initdb.d内のSQLが実行されないようです。
dataフォルダを削除して、ボリュームマウント方式に切り替えると、上手くいきました。

投稿2023/07/04 01:59

nicochichi

総合スコア1