How to use a PostgreSQL container with existing data?

Question

I am trying to set up a PostgreSQL container (https://hub.docker.com/_/postgres/). I have some data from a current PostgreSQL instance. I copied it from /var/lib/postgresql/data and want to set it as a volume to a PostgreSQL container.

My part from docker-compose.yml file about PostgreSQL:

db:
    image: postgres:9.4
    ports:
        - 5432:5432
    environment:
        POSTGRES_PASSWORD: postgres
        POSTGRES_USER: postgres
        PGDATA : /var/lib/postgresql/data
    volumes:
        - /projects/own/docker_php/pgdata:/var/lib/postgresql/data

When I make docker-compose up I get this message:

db_1  | initdb: directory "/var/lib/postgresql/data" exists but is not empty
db_1  | If you want to create a new database system, either remove or empty
db_1  | the directory "/var/lib/postgresql/data" or run initdb
db_1  | with an argument other than "/var/lib/postgresql/data".

I tried to create my own image from the container, so my Dockerfile is:

FROM postgres:9.4
COPY pgdata /var/lib/postgresql/data

But I got the same error, what am I doing wrong?

Update

I got SQL using pg_dumpall and put it in /docker-entrypoint-initdb.d, but this file executes every time I do docker-compose up.

Have you ever solved this with data volumes instead of data-only containers? — Anton, Oct 22 '16 at 16:36

johntellsall · Accepted Answer · 2017-03-22T19:21:28.760

To build on irakli's answer, here's an updated solution:

use newer version 2 Docker Compose file
separate volumes section
extra settings deleted

docker-compose.yml

version: '2'

services:
  postgres9:
    image: postgres:9.4
    expose:
      - 5432
    volumes:
      - data:/var/lib/postgresql/data

volumes:
  data: {}

demo

Start Postgres database server:

$ docker-compose up

Show all tables in the database. In another terminal, talk to the container's Postgres:

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'

It'll show nothing, as the database is blank. Create a table:

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c 'create table beer()'

List the newly-created table:

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'

                         Access privileges
 Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
 public | beer      | table |                   |

Yay! We've now started a Postgres database using a shared storage volume, and stored some data in it. Next step is to check that the data actually sticks around after the server stops.

Now, kill the Postgres server container:

$ docker-compose stop

Start up the Postgres container again:

$ docker-compose up

We expect that the database server will re-use the storage, so our very important data is still there. Check:

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'
                         Access privileges
 Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
public | beer      | table |                   |

We've successfully used a new-style Docker Compose file to run a Postgres database using an external data volume, and checked that it keeps our data safe and sound.

storing data

First, make a backup, storing our data on the host:

$ docker exec -it $(docker-compose ps -q postgres9 ) pg_dump -Upostgres > backup.sql

Zap our data from the guest database:

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c 'drop table beer'

Restore our backup (stored on the host) into the Postgres container.

Note: use "exec -i", not "-it", otherwise you'll get a "input device is not a TTY" error.

$ docker exec -i $(docker-compose ps -q postgres9 ) psql -Upostgres < backup.sql

List the tables to verify the restore worked:

$ docker exec -it $(docker-compose ps -q postgres9 ) psql -Upostgres -c '\z'
                         Access privileges
Schema |   Name    | Type  | Access privileges | Column access privileges 
--------+-----------+-------+-------------------+--------------------------
public | beer      | table |                   |

To sum up, we've verified that we can start a database, the data persists after a restart, and we can restore a backup into it from the host.

Thanks Tomasz!

Great example, thank you, one observation under "Zap our data ... " there should be `'drop table beer'` rather than `'delete table beer'`, shouldn't it? — tomasz_kusmierczyk, Mar 21 '17 at 15:15
Hi @johntellsall , Thanks for demo. Do you mind you let me know, the volumes `data: {}` is actually pointing to which host machine directory ? — Cheok Yan Cheng, Feb 06 '18 at 18:47
For what it's worth, in case your data is not persisting, you can look [here](https://stackoverflow.com/a/41603871/3705667) for answers — Lasf, Feb 12 '18 at 00:27
I had the same but i was getting problems with a clean database. I have to put `- PGDATA=/tmp` for solve it. — SalahAdDin, Jun 07 '18 at 13:56

score 12 · Answer 2 · edited Jul 25 '18 at 05:08

12

It looks like the PostgreSQL image is having issues with mounted volumes. FWIW, it is probably more of a PostgreSQL issue than Dockers, but that doesn't matter because mounting disks is not a recommended way for persisting database files, anyway.

You should be creating data-only Docker containers, instead. Like this:

postgres9:
  image: postgres:9.4
  ports:
    - 5432:5432
  volumes_from:
    - pg_data
  environment:
    POSTGRES_PASSWORD: postgres
    POSTGRES_USER: postgres
    PGDATA : /var/lib/postgresql/data/pgdata

pg_data:
  image: alpine:latest
  volumes:
    - /var/lib/postgresql/data/pgdata
  command: "true"

which I tested and worked fine. You can read more about data-only containers here: Why Docker Data Containers (Volumes!) are Good

As for: how to import initial data, you can either:

docker cp, into the data-only container of the setup, or
Use an SQL dump of the data, instead of moving binary files around (which is what I would do).

edited Jul 25 '18 at 05:08

Peter Mortensen

28,342
21
95
123

answered Feb 29 '16 at 02:27

irakli

811
6
7

7

Thanks, it seems to be correct. But according to UPDATE 5 http://stackoverflow.com/questions/18496940/how-to-deal-with-persistent-storage-e-g-databases-in-docker. Data container is not needed now, instead we should use new volume api https://docs.docker.com/engine/reference/commandline/volume_create/ . How can we interpret your example with this new api ? – Albert Tobac Mar 01 '16 at 08:04
2

Any idea how to SQL dump if your Postgres Docker container is not starting due to the error mentioned by OP? – Maksim Luzik Dec 13 '17 at 13:26
update : volumes_from is depreciated. https://github.com/wodby/docker4drupal/issues/92 – Abhijit Gujar Jul 21 '19 at 07:10

How to use a PostgreSQL container with existing data?

Update

2 Answers2

docker-compose.yml

demo

storing data

Linked