How to access local(backup) file from Pgadmin(Postgres editor) that is running in a docker container to populate my database

Question

I am in the process of migrating all the database records from my docker Postgres environment. I am new to docker and volumes. I have a backup SQL file of Postgres in my local system. How do I access it in my Pgadmin that is running in docker to populate my database in the Postgres container of docker?

This is my docker-compose file, is it possible to add a volume to map to my local device so that I can access files from my pc storage from pgadmin of the docker container?

version: "3.8"

services:
  postgres:
    image: postgres:11.5
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=****
      - POSTGRES_DB=nanomedicine_db
    ports:  #outside:inside(container)
      - 5432:5432
    networks:
      - shared-network
    volumes:
      - C:/profitional/nanomedicine/data:/var/lib/postgresql/data

  pgadmin:
    container_name: pgadmin4_container
    image: dpage/pgadmin4
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: ****
    ports:
      - 5050:80
    networks:
      - shared-network
    depends_on:
      - postgres
    
  server:
    container_name: nanomedicine_server
    restart: unless-stopped
    image: nanomedicine-server-image:1.0.0
    build:
      context: nanomedicine-backend 
      target: production
      dockerfile: Dockerfile
    ports:  #outside:inside(container)
      - 8080:8080
    networks:
      - shared-network
    depends_on:
      - postgres

  client:
    container_name: nanomedicine_client
    restart: unless-stopped
    image: nanomedicine-client-image:1.0.0
    build:
      context: nanomedicine-frontend 
      target: production
      dockerfile: Dockerfile
    ports:  #outside:inside(container)
      - 3000:3000

    networks:
      - shared-network
    depends_on:
      - server
volumes:
  postgres-volume:
    external: true
networks:
  shared-network:

To know a process of accesing my local files from docker container environment of pgadmin

Answer 1

is it possible to add a volume to map to my local device so that I can access files from my pc storage from pgadmin of the docker container?

Yes, that is exactly what you need to do!, and that technique is known as bind mount.

Note that there is an important difference between accessing a file from a SQL statement (using the pgAdmin editor) and from the pgAdmin GUI (for example, with the file selection utilities): if you are going to access the file from a SQL statement, you will have to perform the bind mount in the PostgreSQL container (since the SQL statement will be executed there), on the other hand, if you are going to access the file from the pgAdmin GUI, you will have to perform the bind mount in the pgAdmin container.

To perform the bind mount on any of the containers simply add a volume as follows:

volumes:
    - C:/path/to/local/directory:/path/to/container/directory

Note: make sure that /path/to/container/directory doesn't already exist in the container to avoid errors.

If you put the volume in the PostgreSQL container, you can already access the file from SQL statements (you can write them in the pgAdmin editor) using the path /path/to/container/directory/<filename>.

Finally, if you want to use pgAdmin's "Restore..." utility (I will leave images below), it is much easier for you to load the file from the GUI than to mount the volume in the pgAdmin container, as you will additionally have to set the PGADMIN_CONFIG_STORAGE_DIR environment variable in the pgAdmin container to be able to access your local files from the GUI, which is not trivial because of the operations that pgAdmin performs in its configuration.