Postgresql in a Docker Container on Windows: How to persist data to a local windows folder

Question

I'm trying to run postgres in a docker container on windows. I also want keep the data in a windows folder, so I tried this:

mkdir c:\pgdata

PS > docker run --name postgres -v c:\pgdata:/var/lib/postgresql/data -d postgres
d12af76bed7f8078babc0b6d35710dfc02b12d650904ed53ca95bb99984e9b36

This appeared to work, but the container is not running and the log tells a different story:

2019-07-24 23:19:20.861 UTC [77] FATAL:  data directory "/var/lib/postgresql/data" has wrong ownership
2019-07-24 23:19:20.861 UTC [77] HINT:  The server must be started by the user that owns the data directory.
child process exited with exit code 1

If I remove the volume option, it starts up fine, but then I don't get my database files persisted where I want them. What am I doing wrong here?

Answer 1

You did nothing wrong, just have a look for the full log:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 400kB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
2019-07-25 01:28:18.301 UTC [77] FATAL:  data directory "/var/lib/postgresql/data" has wrong ownership
2019-07-25 01:28:18.301 UTC [77] HINT:  The server must be started by the user that owns the data directory.
child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/postgresql/data"
running bootstrap script ...

From above, you can see fixing permissions on existing directory /var/lib/postgresql/data ... ok which is executed in docker-entrypoint.sh to change the ownership from root to postgres, but unfortunately, this just works on linux host not on windows host.

Then, why not work on windows, see this discussion, mainly because current implementation was based on CIFS/Samba which make docker cannot improve it.

So, I guess you have no chance to persist data to windows if you insist to use bind mount.

But, if you not insist, a closer solution maybe use Named Volumes like next:

PS C:\> docker volume create my-vol
my-vol
PS C:\> docker run --name postgres -v my-vol:/var/lib/postgresql/data -d postgres
079d4b5b3f73bc0c4c586cdfee3fdefc8a27cdcd409e857de985bead254cd23f
PS C:\> docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
079d4b5b3f73        postgres            "docker-entrypoint.s…"   5 seconds ago       Up 2 seconds        5432/tcp            postgres
PS C:\> docker volume inspect my-vol
[
    {
        "CreatedAt": "2019-07-25T01:43:01Z",
        "Driver": "local",
        "Labels": {},
        "Mountpoint": "/var/lib/docker/volumes/my-vol/_data",
        "Name": "my-vol",
        "Options": {},
        "Scope": "local"
    }
]

Finally, the data will persist in /var/lib/docker/volumes/my-vol/_data, but the limit is this folder is not on windows, it's on hyper-v machine as you may know docker for windows use hyper-v to simulate linux kernel.

But it may still meet your requirement, because even you remove current container, next time if you use same volume name(here is my-vol) to mount, the data will still be in the new container, the named volume will not be delete even container deleted, it will be persist in hyper-v virtual machine.

Answer 2

in windows, once you installed docker, you also got docker-compose.exe command. so let's use it for your postgres:

step1. create an folder in your host(windows: D:\workspace\docker_folder\postgres9.5)

step2. make a docker-compose.yml and paste its content as below:

version: '3'
services:
  postgres9.5:
    container_name: "postgres9.5"
    image: postgres:9.5
    # notice here, D:\workspace should be written as: /d/workspace
    volumes:
      - /d/workspace/docker_folder/postgres9.5:/var/lib/postgresql/data
    command: 'postgres'
    ports:
      - "5432:5432"
    stdin_open: true
    tty: true
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=88888888

step3. run docker-compose up, then you will see the files in your D:\workspace\docker_folder, as below: