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

Question

実現したいこと

• 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フォルダ

Flask==2.2.3
Flask-Login==0.6.2
psycopg2==2.9.6
uwsgi==2.0.21

[uwsgi]
master=true
socket=:3031
chdir=/var/www
wsgi-file=application/testApp.py
callable=app
logto=/var/log/uwsgi.log

from flask import Flask, request, Response, redirect, render_template
from flask_login import LoginManager, UserMixin, login_user, logout_user, current_user
import psycopg2

app = Flask(__name__, static_folder='./static')
app.config['SECRET_KEY'] = 'testApp_secret_key'

login_manager = LoginManager()
login_manager.init_app(app)

class User(UserMixin):
    def __init__(self, id):
        self.id = id

@login_manager.user_loader
def load_user(user_id):
    return User(user_id)

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/new_user', methods=['GET','POST'])
def new_user():
    if request.method == 'POST':
        userID = request.form.get('userID')
        password = request.form.get('password')
        username = request.form.get('username')
        connection = psycopg2.connect(
            host="test_postgresql",
            port=5432,
            dbname="postgres",
            user="postgres",
            password="postgres"
        )
        cursor = connection.cursor()
        cursor.execute(f"INSERT INTO accounts VALUES ('{userID}','{password}','{username}')")
        connection.commit()
        connection.close()
        return redirect('/login?state=0')
    else:
        return render_template('new_user.html')

@app.route('/login', methods=['GET','POST'])
def login():
    if current_user.get_id() != None:
        logout_user()
        return redirect('/login?state=0')
    else:
        if request.method == 'POST':
            userID = request.form.get('userID')
            password = request.form.get('password')
            connection = psycopg2.connect(
                host="test_postgresql",
                port=5432,
                dbname="postgres",
                user="postgres",
                password="postgres"
            )
            cursor = connection.cursor()
            cursor.execute(f"SELECT * FROM accounts WHERE ID = '{userID}'")
            result1 = cursor.fetchone()
            connection.commit()
            if result1 == None:
                connection.close()
                return redirect('/login?state=1')
            else:
                cursor.execute(f"""SELECT * FROM accounts
                        WHERE ID = '{userID}' AND PASS = '{password}'""")
                result2 = cursor.fetchone()
                connection.commit()
                if result2 == None:
                    connection.close()
                    return redirect('/login?state=2')
                else:
                    connection.close()
                    user = User(result2[0])
                    login_user(user)
                    return redirect('/menu')
        else:
            state = request.args.get('state', default=0, type=int)
            return render_template('login.html', state=state)

@app.route('/logout')
def logout():
    if current_user.get_id() == None:
        return redirect('/login?state=0')
    else:
        logout_user()
        return redirect('/')

@app.route('/menu')
def menu():
    if current_user.get_id() == None:
        return redirect('/login?state=0')
    else:
        return render_template('menu.html')

@app.route('/english_top')
def english_top():
    if current_user.get_id() == None:
        return redirect('/login?state=0')
    else:
        return render_template('english_top.html')

@app.route('/en_wordlist', methods=['GET','POST'])
def en_wordlist():
    if current_user.get_id() == None:
        return redirect('/login?state=0')
    else:
        listNo = request.args.get('listNo', default=1, type=int)
        wordlist = []
        connection = psycopg2.connect(
            host="test_postgresql",
            port=5432,
            dbname="postgres",
            user="postgres",
            password="postgres"
        )
        cursor = connection.cursor()
        for x in range(5):
            searchNo = 5*(listNo-1)+x+1
            cursor.execute(f"SELECT * FROM en_words WHERE Num = {searchNo}")
            result = cursor.fetchone()
            wordlist.append({'No':result[0],'Word':result[1]})
        connection.commit()
        connection.close()
        return render_template('en_wordlist.html', listNo=listNo, wordlist=wordlist)

FROM python:3.11

WORKDIR /var/www
COPY . .

RUN pip install --no-cache-dir -r /var/www/config/requirements.txt

postgresqlフォルダ

create table if not exists accounts (
  ID text,
  PASS text,
  UserName text
);

create table if not exists en_words (
  Num int,
  Word text
);

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

FROM postgres:latest

nginxフォルダ

user  nginx;
worker_processes  auto;
error_log  /var/log/nginx/error.log warn;
pid        /var/run/nginx.pid;

events {
    worker_connections  1024;
}

http {

    include       /etc/nginx/mime.types;
    default_type  text/html;
    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';
    access_log  /var/log/nginx/access.log  main;
    sendfile       on;
    tcp_nopush     on;
    tcp_nodelay    on;
    keepalive_timeout  65;

    server {
        listen 8080 default;
        charset utf-8;
        server_name _;

        location / {
            client_max_body_size 1m;
            client_body_buffer_size 8k;

            include uwsgi_params;
            uwsgi_pass test_uwsgi:3031;
        }

        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   /usr/share/nginx/html;
        }
    }
}

FROM nginx:latest

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

Answer 1

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