How to import and manage the database in postgresql on the docker?

Question

I have my Django project with structure like this:

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

This is my docker-compose.yml file:

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:
      - [email protected]
      - 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:

I have three problems with my app:

1 - how can I import my my-database1.sql and my-database2.sql databases into postgresql? The solution (I mean ./my-database1.sql:/docker-entrypoint-initdb.d/my-database1.sql) in my code doesn't work. 2 - after successful import databases from previous step how can I see them inside pgadmin? 3 - my code should write something inside tables of my-database1.sql. How should I connect to it after import to postgresql?

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.