Docker Community Forums

Share and learn in the Docker community.

Postgresql as docker container not starting with initial data from mapped volume

On my macbook I have postgres running in a docker container and I use a mapped volume to persist the data. This works perfectly locally.

However, when I try to do the same on an Ubuntu server the ‘initial’ data from the mapped volume is not working. Postgres starts up in an ‘empty’ initial state. I can see that the volume mapping is working and I’ve checked and confirmed the data folder for postgres within the container. Interestingly the mapping does seem to be working, since an added table and data in that table in the default postgres database IS persistent. Furthermore it is interesting to note that I’m getting an error when I try to create a table in a new database. The new database is persistent as well, but the table cant be saved as there is an error thrown:

could not open file “base/16384/2611”: No such file or directory

This is expected as the folder base/16384 doesn’t exist.

To me this seems this is a user/rights issue perhaps or some postgres setting?

I tried a few things to solve this, all didn’t work:

  • The ‘bazooka’: I chmod 777 the mapped volume, to no effect
  • Run docker container as root, no effect
  • Run docker container as user postgres:user (this seems to be the user:group of the mapped files), to no effect
  • run chown of PGDATA folder on docker container start to set owner to postgres:postgres as this seems to be the user:group that is creating a folder when I create a new database, to no effect. even still can’t create a table even though the database folder was successfully created

I’m starting the container with either docker-compose or from the command line using;

docker run --rm --name pg -e POSTGRES_PASSWORD=[password] -d -p 5432:5432 -v /root/docker/volumes/postgres:/var/lib/postgresql/data postgres -c listen_addresses='*'

Or the docker-compose.yaml:

version: '3'
    image: redis
    restart: always
      - redis:/data
    image: postgres
    user: postgres:postgres
    restart: always
      POSTGRES_USER: postgres
    command: postgres -c listen_addresses='*'
      - 5431:5432
      - $HOME/docker/volumes/postgres:/var/lib/postgresql/data
    image: amancevice/superset
    restart: always
      - pg-nex
      - redis
      - "8091:8088"
      - /root/docker/volumes/superset:/etc/superset

Did you use the volume from your macbook on the ubuntu server to provide the initial data? Afaik, that won’t work since volumes are not portable between different operating systems.

I zipped the volume and transfered it to the server. So that could be a problem?
How would I work around this?
I tried copying (export/import) the tables, but even when I create the database in the new postgres container it can’t copy it and I get the message that the database schema doesn’t exist.

Or would it work to exec -it into the container running on my mac, zip the data there within a mapped volume and then copy to the server?

I think your best option is to use pg_dump on the container running on your mac. Then transfer the zip to your Ubuntu server and use pg_restore on the running container there. That way you’re migrating data on the DB level and Docker will sort out the volume part.

1 Like

many thanks! this did the trick :slight_smile:
however, this leaves me with another challange/question/strange issue; the data IS persistent, however it’s not written to the mapped folder on the server somehow…
when I inspect the docker container I see the mounted volume is (has become?) /var/lib/docker/volumes/8fb50fc46129199ebf27be77c0509a68c03176dd1a2a41b8a77941437b52c3d0/_data
any idea why this is (despite the defined mapped volume in the docker-compose?)

That folder indicates an unnamed volume. According to your docker-compose the persistent data from Postgres should be in $HOME/docker/volumes/postgres on the host.

Btw, you’re declaring a pg-nex volume in the compose file but not using it. This shouldn’t be the volume you mentioned since it would be under /var/lib/docker/volumes/<project>_pg-nex/_data