Version upgrade error：ERROR [root] Database migration failed

[linronghua921122]

Self Checks

• This is only for bug report, if you would like to ask a question, please head to Discussions.
• I have searched for existing issues search for existing issues, including closed ones.
• I confirm that I am using English to submit this report (我已阅读并同意 Language Policy).
• [FOR CHINESE USERS] 请务必使用英文提交 Issue，否则会被关闭。谢谢！:）
• Please do not modify this template :) and fill in all the required fields.

Dify version

0.7.3

Cloud or Self Hosted

Self Hosted (Docker)

Steps to reproduce

My current version is 0.6.11, and when trying to upgrade to version 0.7.3, an error occurs:
ERROR [root] Database migration failed, error: (psycopg2.OperationalError) connection to server at "db" (172.17.53.3), port 5432 failed: FATAL: password authentication failed for user "postgres"
But I have confirmed that the database password is correct. Could you please help me check what the reason may be?

✔️ Expected Behavior

No response

❌ Actual Behavior

No response

[dosubot]

I found some similar discussions and an issue that might help you resolve the database migration error:

1. Unable to connect the postgres database ^[1]
2. Source code installation execution command:：flask db upgrade ^[2]
3. poetry run python -m flask db upgrade 数据库迁移失败 ^[3]
4. When upgrading the Docker container, it failed to connect to the database, resulting in a migration failure. (closed) ^[4]

To resolve the database migration error "psycopg2.OperationalError: connection to server at 'db' (172.17.53.3), port 5432 failed: FATAL: password authentication failed for user 'postgres'" when upgrading from version 0.6.11 to 0.7.3 in a self-hosted (Docker) environment, ensure that the database credentials in your docker-compose.yaml file are correct and consistent across all services.

From the provided docker-compose.yaml file, the database credentials are as follows:

services:
  # The pgvecto—rs database.
  pgvecto-rs:
    image: tensorchord/pgvecto-rs:pg16-v0.2.0
    restart: always
    environment:
      PGUSER: postgres
      # The password for the default postgres user.
      POSTGRES_PASSWORD: difyai123456
      # The name of the default postgres database.
      POSTGRES_DB: dify
      # postgres data directory
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./volumes/pgvectors/data:/var/lib/postgresql/data
    # uncomment to expose db(postgresql) port to host
    ports:
      - "5431:5432"
    healthcheck:
      test: [ "CMD", "pg_isready" ]
      interval: 1s
      timeout: 3s
      retries: 30

Make sure that:

1. The DB_PASSWORD for the api service matches the POSTGRES_PASSWORD for the pgvecto-rs service.
2. The DB_USERNAME for the api service matches the PGUSER for the pgvecto-rs service.
3. The DB_DATABASE for the api service matches the POSTGRES_DB for the pgvecto-rs service.

In this case, the credentials appear to be consistent. If you still encounter the error, try the following steps:

1. Restart the Docker containers to ensure that all services are using the updated credentials:

   docker-compose down
   docker-compose up -d

2. Check for any cached credentials or environment variables that might be causing conflicts. Ensure that there are no old environment variables set in your shell or Docker environment.

3. Verify the database connection manually using a PostgreSQL client to ensure that the credentials work as expected:

   psql -h 172.17.53.3 -U postgres -d dify -W

   Enter the password difyai123456 when prompted.

If the manual connection works but the error persists in the application, there might be an issue with how the application reads the environment variables. Double-check the Docker logs for any additional clues.

If the manual connection fails, there might be an issue with the PostgreSQL server configuration or network connectivity. Ensure that the PostgreSQL server is running and accessible from the Docker network.

Additionally, ensure that the MIGRATION_ENABLED environment variable is set to true to automatically execute database migrations on container startup. This is only available when launched using Docker and does not apply when running from source code. If running from source code, you need to manually run flask db upgrade in the api directory ^[5][6][7][8].

To continue talking to Dosu, mention @dosu.

[crazywoola]

see https://github.com/langgenius/dify/releases/tag/0.6.12-fix1