python - 如何在docker上的postgresql中导入和管理数据库？

Question

我的 Django 项目的结构如下：

myapp/
    manage.py
    Dockerfile
    docker-compose.yml
    my-database1.sql
    my-database2.sql
    requirements.txt
    pgadmin/
    pgadmin-data/
    myapp/
        __init__.py
        settings.py
        urls.py
        wsgi.py

这是我的docker-compose.yml文件：

version: "3.9"
   
services:
  db:
    image: postgres
    volumes:
      - ./data/db:/var/lib/postgresql/data
      - ./my-database1.sql:/docker-entrypoint-initdb.d/my-database1.sql
      - ./my-database2.sql:/docker-entrypoint-initdb.d/my-database2.sql
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - PGDATA=/var/lib/postgresql/data
  
  pgadmin:
    image: dpage/pgadmin4:4.18
    restart: unless-stopped
    environment:
      - PGADMIN_DEFAULT_EMAIL=[e-mail]
      - PGADMIN_DEFAULT_PASSWORD=admin
      - PGADMIN_LISTEN_PORT=80
    ports:
      - "8090:80"
    volumes:
      - ./pgadmin-data:/var/lib/pgadmin
    links:
      - "db:pgsql-server"


  web:
    build: .
    command: python manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on:
      - db

volumes:
  db-data:
  pgadmin-data:

我的应用程序有三个问题：

1 - 如何将我的my-database1.sql和my-database2.sql数据库导入 postgresql？我的代码中的解决方案（我的意思是./my-database1.sql:/docker-entrypoint-initdb.d/my-database1.sql）不起作用。2 - 从上一步成功导入数据库后，我如何在 pgadmin 中看到它们？3 - 我的代码应该在 my-database1.sql 的表中写入一些内容。导入到 postgresql 后我应该如何连接它？

标签： pythondjangopostgresqldocker-composepgadmin

Answer 1

The postgres image will only attempt to run the files provided inside the /docker-entrypoint-initdb.d directory while running on an empty folder. By your docker-compose.yml configuration, you have a persistent volume for the database data. This means that Postgres will not take updates to the SQL files into account on later deployments. Something similar happens when one of the scripts fails. Here is the excerpt from the documentation:

Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with an empty data directory; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue with your scripts.

Check the site documentation to see how you can make your initialization scripts more robust so they can handle failures.

To solve your issue, try deleting the volume manually or by using the -v flag while running docker-compose down, and then redeploy your application:

-v, --volumes         Remove named volumes declared in the `volumes`
                      section of the Compose file and anonymous volumes
                      attached to containers.