Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Mounting data volume for Postgres in docker for Windows doesn't work #445

Closed
vrindanayak opened this issue Jan 30, 2017 · 94 comments
Closed

Comments

@vrindanayak
Copy link

vrindanayak commented Jan 30, 2017

Expected behavior

Postgres should start and mount the correct windows directory as a data volume

Actual behavior

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 ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
initdb: could not fsync file "/var/lib/postgresql/data/base/1": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/base/12406": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/base/12407": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/base": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/global": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_clog": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_commit_ts": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_dynshmem": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_logical/mappings": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_logical/snapshots": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_logical": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_multixact/members": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_multixact/offsets": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_multixact": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_notify": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_replslot": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_serial": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_snapshots": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_stat": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_stat_tmp": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_subtrans": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_tblspc": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_twophase": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_xlog/archive_status": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_xlog": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data": Invalid argument
initdb: could not fsync file "/var/lib/postgresql/data/pg_tblspc": Invalid argument

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....FATAL:  data directory "/var/lib/postgresql/data" has wrong ownership
HINT:  The server must be started by the user that owns the data directory.
pg_ctl: could not start server
Examine the log output.
 stopped waiting

Information

docker-compose.yml file :

version: "2"
services:
  slapd:
    image: dcm4che/slapd-dcm4chee:2.4.40-8.1
    ports:
      - "389:389"
    env_file: docker-compose.env
    volumes:
      - //c/Users/<user>/var/local/dcm4chee-arc/ldap:/var/lib/ldap
      - //c/Users/<user>/var/local/dcm4chee-arc/slapd.d:/etc/ldap/slapd.d
  postgres:
    image: dcm4che/postgres-dcm4chee:9.6-8
    ports:
      - "5432:5432"
    env_file: docker-compose.env
    volumes:
      - //c/Users/<user>/var/local/dcm4chee-arc/db:/var/lib/postgresql/data
  dcm4chee-arc:
    image: dcm4che/dcm4chee-arc-psql:5.8.1
    ports:
      - "8080:8080"
      - "9990:9990"
      - "11112:11112"
      - "2575:2575"
    env_file: docker-compose.env
    environment:
      WILDFLY_CHOWN: /opt/wildfly/standalone /storage
      WILDFLY_WAIT_FOR: ldap:389 db:5432
      AUTH_SERVER_URL: /auth
      JAVA_OPTS: "-Xms64m -Xmx512m -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=256m -Djava.net.preferIPv4Stack=true -Djboss.modules.system.pkgs=org.jboss.byteman -Djava.awt.headless=true"
    links:
      - slapd:ldap
      - postgres:db
    volumes:
      - //c/Users/<user>/var/local/dcm4chee-arc/storage:/storage
      - //c/Users/<user>/var/local/dcm4chee-arc/wildfly:/opt/wildfly/standalone

Note : Substituted <user> with my user

docker-compose.env file

LDAP_BASE_DN=dc=dcm4che,dc=org
LDAP_ORGANISATION=dcm4che.org
LDAP_ROOTPASS=secret
LDAP_CONFIGPASS=secret
ARCHIVE_DEVICE_NAME=dcm4chee-arc
AE_TITLE=DCM4CHEE
DICOM_HOST=localhost
DICOM_PORT=11112
HL7_PORT=2575
STORAGE_DIR=/storage/fs1
POSTGRES_DB=pacsdb
POSTGRES_USER=pacs
POSTGRES_PASSWORD=pacs
WILDFLY_ADMIN_USER=admin
WILDFLY_ADMIN_PASSWORD=admin
KEYCLOAK_ADMIN_USER=admin
KEYCLOAK_ADMIN_PASSWORD=admin

Steps to reproduce the behavior

  1. Share Drive using Docker for Windows->Settings->Shared Drive -> C:
  2. Use docker-compose up -d

Similar issue was already reported on moby/moby#25908 but was redirected here to be checked/opened on https://github.com/docker/for-win/issues
I have searched for this issue in Open and Closed issues list, but couldn't find any.

I'm using

  • Windows 10 Pro, 64-bit
  • Docker version 1.13.0, build 49bf474
@friism
Copy link

friism commented Jan 30, 2017

I think this is a duplicate of #39

For the time being, this won't work. You should either just use in-container storage or use a volume not mounted from your Windows host.

Is there a particular reason you want the data to be on the windows host?

@colonelz
Copy link

colonelz commented Jul 19, 2017

Tried it with this approach: https://forums.docker.com/t/trying-to-get-postgres-to-work-on-persistent-windows-mount-two-issues/12456/5

command line

docker volume create --name postgres-data-volume -d local

docker-compose.yml

...
### PostgreSQL Container ####################################

postgres:
build: ./postgres
volumes:
- postgres-data-volume:/var/lib/postgresql/data
ports:
- "${POSTGRES_PORT}:5432"
environment:
- POSTGRES_DB=${POSTGRES_DB}
- POSTGRES_USER=${POSTGRES_USER}
- POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
networks:
- backend

### PostgreSQL PostGis Container ############################

postgres-postgis:
build: ./postgres-postgis
volumes:
- postgres-data-volume:/var/lib/postgresql/data
ports:
- "${POSTGRES_PORT}:5432"
environment:
- POSTGRES_DB=${POSTGRES_DB}
- POSTGRES_USER=${POSTGRES_USER}
- POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
networks:
- backend
...

### Volumes Setup #############################################

volumes:
mysql:
driver: "local"

...

postgres-data-volume:
external: true

@redfellow
Copy link

also affected

@darkguy2008
Copy link

darkguy2008 commented Jun 11, 2018

Same here.

Ah come on, so you need a specific reason why to support a core Docker functionality? Like, well, I expect it to work with storing the data in a volume instead of being stuck in some random volume that I can't move to another server due to cryptic file paths and so I want my docker data + config to stay in the same folder so it's easy for me to backup, like a good DevOp would do? Holy Christ.

If MariaDB can do it, why Postgre can't? I don't want my data to be wiped out when I turn off the container.

+1 to this, it needs to be fixed.

@MatthewLymer
Copy link

Same issue, luckily I'm just picking my database now.

@flavienbwk
Copy link

Same issue. Apparently no solutions for 1 year now...

@redfellow
Copy link

Docker & Production ready.

That's two separate products.

@Greifenkralle
Copy link

Same problem.

@flavienbwk
Copy link

I've solved the problem by creating a new volume like that :
docker volume create postgres_database

And then you link it like that :

services:
    postgres:
        restart: always
        volumes:
            - postgres_database:/var/lib/postgresql/data:Z

volumes:
    postgres_database:
        external: true

@Greifenkralle
Copy link

flavienbwk: Your solution works. :) Thank you.

@flavienbwk
Copy link

Perfect!

@Cugap
Copy link

Cugap commented Sep 25, 2018

This work but this is bad solution, global docker volumes creating inside of docker VM on Windows and MAC, what make pack of problems, such as no option to modify or inspect data of volumes.

  • summ size of all volumes < size of virtual hard disc that used by docker, and no option to transperent volumes by physical devices

@flavienbwk
Copy link

@Cugap Maybe a bad solution but there's no proposed solution by the Docker team, that has closed the issue whereas it is not solved. Maybe they should do something ?

@marcospgp
Copy link

Please consider reopening!

@Cugap
Copy link

Cugap commented Oct 1, 2018

@friism pls reopen cause problem is still active, and maintaining of containerized DB is a real problem.

@friism
Copy link

friism commented Oct 6, 2018

ping @carlfischer1

@carlfischer1
Copy link

ping @gtardif

@beeplin
Copy link

beeplin commented Oct 12, 2018

this is really frustrating. Please reopen this issue.

@watchdogoblivion
Copy link

@hasnatbabur Yes, I tried with ${PWD}/data:/var/lib/postgresql . Unfortunately, it does not copy the contents of the data folder. Every time I made a change to my yml file, I made sure to delete all generated volumes and instances before re-composing. I even tried using pgdata environment variable to change the location of the data, but that did not work either. Are you using Docker toolbox or Docker desktop?

@hasnatbabur
Copy link

hasnatbabur commented Nov 12, 2019

@watchdogoblivion I am using Laradock and this is my final docker-compose.yml file for postgres:
postgres:
build: ./postgres
volumes:
- ./data/postgres:/var/lib/postgresql
- ./postgres/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d

@yami12376
Copy link

Why this issue is still closed?

@SuperSandro2000
Copy link

Why this issue is still closed?

#445 (comment) and number one reason: Postgres wants its data with special permissions Windows does not support. Use a volume or Linux or not Postgres. There is nothing feasible Docker can do to fix it.

@yami12376
Copy link

Use a volume

I use a volume, but i must use external one to make it work right.

For example:

    postgresqlVolume:
      external: true

in my docker-compose file. If I make it regular the database stops immediately.

@gbritton1
Copy link

gbritton1 commented Nov 22, 2019 via email

nobleator added a commit to nobleator/ims-microservices that referenced this issue Dec 2, 2019
Attempt to add volume mount to persist PSQL data (and potentially access from local DB instance). But alas, this does not seem to be supported the way I want: docker/for-win#445
@koteisaev
Copy link

koteisaev commented Dec 27, 2019

I landed into this thread as literally single search result which really relevant to me.
What I wanted to achieve is something likethis:

volumes:
        - name: keys
          hostPath:
            path: F:\keys
            type: Directory

and mount this with

          volumeMounts:
            - mountPath: /keys
              name: keys

to use the

          env:
            - name: GOOGLE_APPLICATION_CREDENTIALS
              value: /keys/project-credentials.json

And what?
I have to install full-blown vm to handle dev minicluster whose purpose is just to examine can I get our application working aboard kubernetes cluster, or GKE to be clear?
Sad to hear that.

@bolpol
Copy link

bolpol commented Jan 23, 2020

I've solved the problem by creating a new volume like that :
docker volume create postgres_database

And then you link it like that :

services:
    postgres:
        restart: always
        volumes:
            - postgres_database:/var/lib/postgresql/data:Z

volumes:
    postgres_database:
        external: true

If I need local storage on the machine? How to be?

@Jeff-Tian
Copy link

Met the same issue with Windows 10, Docker for Desktop.

Finally found the workaround, verified.

From https://forums.docker.com/t/data-directory-var-lib-postgresql-data-pgdata-has-wrong-ownership/17963/20:

Workaround: add the environment variable PGDATA: /tmp to your docker-compose file.

For example:

version: "3.1"

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
      PGDATA: /tmp # This does the trick
    volumes:
      - ./dbdata:/var/lib/postgresql/data:Z

@strarsis
Copy link

But when PGDATA points to a directory inside the docker postgres container, not mounted on host,
all data is lost after resetting the docker postgres container. This makes the whole thing pointless.

@gbritton1
Copy link

gbritton1 commented Mar 18, 2020 via email

@strarsis
Copy link

strarsis commented Mar 18, 2020

So now I have the Postgres Server Windows edition directly running and connect the containers to the Docker Host (host.docker.internal) should resolve to the Docker Host from within the container).

Note: Don't confuse this with the domain docker.host that resolves to an existing, external domain/website.

@mldisibio
Copy link

A partial workaround can be done by using Postgres tablespaces to store the database object files on the windows host system. It is not a perfect solution, but at least if you have a large database, it can be stored outside the Linux VM.

docker run --rm --name mypostgres
           -e POSTGRES_PASSWORD=pwd
           -d -p 5432:5432 
           -v /var/lib/docker/basedata:/var/lib/postgresql/data
           -v d:/data/largedb:/mnt/largedb
           postgres

This sets the default data storage to a (persistent) directory on the Linux VM (/var/lib/docker/basedata).
It also tells docker to mount your windows D:\data\largedb as a volume that is visible to the Postgres container as /mnt/largedb. (The windows directory need not exist yet.)
Log into postgres and using psql or whatever tool, execute the following DDL:

CREATE TABLESPACE winhoststorage LOCATION '/mnt/largedb';
CREATE DATABASE "my_large_db" WITH TABLESPACE = "winhoststorage";

Go ahead and create tables and data in my_large_db. Stop the containter. Navigate to D:\data\largedb in Windows Explorer and you will see data files. Restart the container and you will see the data from the previous session.

@strarsis
Copy link

strarsis commented Mar 29, 2020

@mldisibio: Your approach seems to fix the whole issue. Why is this only "partial" data? Isn't the data directory in Postgres the same as the "tablespace" data? Let's say the postgres container is completely removed, can the database still be used with another postgres container?

@mldisibio
Copy link

mldisibio commented Mar 29, 2020

@strarsis I called this a 'partial' solution because I don't know enough about PostgreSQL internals to understand well what this gets us beyond table storage outside of the LinuxVM and onto the Windows host file system. Have a look at Database File Layout in the PostgreSQL documention and its clear that the most critical configuration and control files are stored in PGDATA. In the solution I provide, PGDATA is still on the LinuxVM (at /var/lib/docker/basedata).

So my gut response to your question is No, I don't think the data in the windows host filesystem is useable if the PGDATA linux directory goes away. That said, PGDATA is at least not on the Postgres container (physically) but rather is on the LinuxVM, so yes, it's still okay if the Postgre container is completely removed. In fact, the --rm option in my example is removing the container after every run.

Before I make any further comments, let me make it abundantly clear. The only correct way to both make data portable and to recover from loss, is through proper backup and restore operations.

So I have worked with Sql Server for 18 years and am relatively new to Postgre. Given that this issue is opened by users of Docker for Windows using a linux container with Postgres, I bet most of the db developers are looking for the familiar experience with Sql Server that is much more file oriented. You specify where you want your database file (.mdf) and you log file (.ldf) and maybe some partition files. When you go to that directory, you see two large files and that is all. And again, while emphasizing that the only correct way to manage recovery and restore is proper backups, Sql Server developers know that in a true disaster situation, you can recover with limited loss by using the .mdf and .ldf files directly. Also, Sql Server has the detach and attach concepts which are also very much file based.

I am fairly certain none of this is being achieved with the TABLESPACE solution. And I am thoroughly confused by the hundreds of files I am seeing in the tablespace directory on windows. Maybe a Postgre DBA veteran can better explain any attach or recover options related to tablespace storage.

Interestingly enough however, knowing that we can mount a windows directory to the PostgreSQL container even though it is not the core PGDATA directory does offer more flexibility in backing up your entire PostgreSQL database to the windows host using pg_dump directly without having to resort to the docker copy to grab the backup off the LinuxVM.

@LiamKarlMitchell
Copy link

I think there's a post on this thread showing how to set ntfs permissions to make this work. If you can do it by hand then IMHO docker should provide an easier way to do it.

On Thu, Nov 21, 2019, 10:07 AM Sandro @.***> wrote: Why this issue is still closed? #445 (comment) <#445 (comment)> and number one reason: Postgres wants its data with special permissions Windows does not support. Use a volume or Linux or not Postgres. There is nothing feasible Docker can do to fix it. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#445?email_source=notifications&email_token=AC5R66EWENY4DF2MRXKCLRDQU2PZLA5CNFSM4C6F2NK2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEE2RBUY#issuecomment-557125843>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AC5R66EOTVNAJ2DOZIYNPRTQU2PZLANCNFSM4C6F2NKQ .

Where?

@will-quast
Copy link

I was able to solve this issue by configuring my Docker Desktop for Windows to use the new WSL 2 backend (instead of Hyper-V).
Follow the instructions at https://docs.docker.com/docker-for-windows/wsl/ then clear your old container and recreate it with WSL 2 enabled.
Example of my working docker-compose.xml: (note the data is stored on my Windows D: drive)

# docker-compose build
version: '2.1'
services:
  db:
    image: kartoza/postgis:latest
    volumes:
      - d:/postgis/data:/var/lib/postgresql
    environment:
      # If you need to create multiple database you can add coma separated databases eg gis,data
      - POSTGRES_DB=gis
      - POSTGRES_USER=admin
      - POSTGRES_PASS=admin
      - ALLOW_IP_RANGE=0.0.0.0/0
      # Add extensions you need to be enabled by default in the DB. Default are the five specified below
      - POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,postgis_raster,pgrouting
    ports:
      - 5432:5432
    restart: on-failure
    healthcheck:
      test: "exit 0"

Also note this docker compose needs to be run from PowerShell or CMD and not from your WSL linux console. The volume definition "d:/postgis/data:/var/lib/postgresql" does not work in linux.

@LiamKarlMitchell
Copy link

Nice, I ended up just running the containers from the ubuntu (WSL) console storing the code/data on the WSL share which I mapped as a network drive (Z:) seems faster and iNotify works now :)

@strarsis
Copy link

strarsis commented Jun 4, 2020

@LiamKarlMitchell: So you store the postgres datafiles on a network drive and mounted that network drive into the postgres container, and this fixes the permissions issue?

@samuelmaddock
Copy link

I followed the steps to use the WSL 2 backend for Docker Desktop on Windows and was able to resolve this issue. I installed Ubuntu from the Microsoft Store to use as my distro.

@LiamKarlMitchell
Copy link

LiamKarlMitchell commented Jun 16, 2020

Something like that?
The project files (node.js /dockerfile/docker-compose.yml etc) on Z drive which is mapped to \wsl$\ubuntu for convince so I can still use IDE and gitkraken on Windows.

So on windows
Z:\home\ubuntu\myProject

version: "3.7"
services:
  db:
    image: postgres:9.5.21-alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: myproject
    volumes:
      - db-data:/var/lib/postgresql/data
      - ./pgdata/dump.sql:/docker-entrypoint-initdb.d/dump.sql

# ..... near bottom of the file ....
volumes:
  db-data:

When I want to run the project I need to first goto Ubuntu installed from Microsoft Store.
Then I cd to myProject and run docker-compose up -d

@docker-robott
Copy link
Collaborator

Closed issues are locked after 30 days of inactivity.
This helps our team focus on active issues.

If you have found a problem that seems similar to this, please open a new issue.

Send feedback to Docker Community Slack channels #docker-for-mac or #docker-for-windows.
/lifecycle locked

@docker docker locked and limited conversation to collaborators Jul 16, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests