My data lost after removing the container

[pavelkeyzik]

Hi! Can you help me fix some problem with my database?

I have a docker-compose.yml with some configuration. It works correctly but after removing my container and start again I lose my data in the database. What am I doing wrong?

OS: Windows 10
Configuration file:

db:
    image: postgres:11.1
    restart: always
    ports:
      - 5444:5432
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=test
    volumes:
      - pgdata:/var/lib/postgresql/data

[wglambert]

How did you stop/remove the container? Using your compose file works fine and the data in pgdata will persist

$ docker-compose up -d
Creating postgres-569_db_1_2b2b84bc0b0b ... done

$ docker exec -it postgres-569_db_1_77f2c575049a psql -Upostgres
psql (11.1 (Debian 11.1-3.pgdg90+1))
Type "help" for help.

postgres=# create database mydb;
CREATE DATABASE

postgres=# exit

$ docker-compose down
Stopping postgres-569_db_1_77f2c575049a ... done
Removing postgres-569_db_1_77f2c575049a ... done

$ docker-compose up -d
Creating postgres-569_db_1_51d9eb4008c9 ... done

 docker exec -it postgres-569_db_1_65e1faefff2e psql -Upostgres
psql (11.1 (Debian 11.1-3.pgdg90+1))
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
   
-----------+----------+----------+------------+------------+--------------------
---
 mydb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres        
  +
           |          |          |            |            | postgres=CTc/postgr
es
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres        
  +
           |          |          |            |            | postgres=CTc/postgr
es
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(5 rows)

[pavelkeyzik]

What if I'll remove a container with docker rm [container]? Without removing my volume.

[wglambert]

That wouldn't delete the contents of any volumes and your volume should still show up under docker volume ls

[fayad]

I think your docker-compose is missing an additional definition for the volume.

db:
    image: postgres:11.1
    restart: always
    ports:
      - 5444:5432
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=test
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
    pgdata:

For the record this way data will persist even with docker rm unless you do a volume remove with docker rm -v

[pavelkeyzik]

@fayad nope, I have this. It's really interesting but I cannot repeat this case with data lose again. I think that we can close this issue.

[fayad]

Sorry to update on a closed issue. Hope this helps someone.

I came across one scenario that caused data loss on postgres container, missed out an important information related to DB type. Without explicitly mentioning DB vendor as Postgres it defaults to H2 that recreates the db, causing it to recreate the volume. Here is a read about it.

The environment must have DB_VENDOR: postgres to fix this.

[erulabs]

Just a heads up this can lead to data loss when moving from postgres 12 to postgres 13... It seems the pgdata directory is being removed (I see initdb: removing contents of data directory, which is more or less something I never, ever, ever want my database to print).

Trying to narrow this down and figure out what's going on - thankfully it was development boxes that moved from 12 to 13 - so no worries about data loss - but this certainly doesn't make me feel great about postgres 13!

I suspect the issue is that the older data dir was not owned by the new postgres user that postgres:13 wants to use. This should result in an error, not removing the data dir!! Will try to track this down and make a PR, but may just revert to postgres:12 for now.

[abury]

@erulabs I'm seeing the same after updating to Postgres 13, did you find anything interesting in your research?