Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Docker-compose v3 not persisting postgres database

I'm having difficulty persisting postgres data after a docker-compose v3 container is brought down and restarted. This seems to be a common problem, but after a lot of searching I have not been able to find a solution that works.

My question is similar to here: How to persist data in a dockerized postgres database using volumes, but the solution does not work - so please don't close. I'm going to walk through all the steps below to replicate the problem.

Here is my docker-compose file:

version: "3"  
services:  
  db:
    image: postgres:latest
    environment:
      POSTGRES_DB: zennify
      POSTGRES_USER: patientplatypus
      POSTGRES_PASSWORD: SUPERSECRETPASSWORD
    volumes:
      - pgdata:/var/lib/postgresql/data:rw
    ports:
      - 5432:5432
  app:
    build: .
    command: ["go", "run", "main.go"]
    ports:
      - 8081:8081
    depends_on:
      - db
    links:
      - db
volumes: 
  pgdata:

Here is the terminal output after I bring it up and write to my database:

patientplatypus:~/Documents/zennify.me/backend:08:54:03$docker-compose up
Starting backend_db_1 ... done
Starting backend_app_1 ... done
Attaching to backend_db_1, backend_app_1
db_1   | 2018-08-19 13:54:53.661 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1   | 2018-08-19 13:54:53.661 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1   | 2018-08-19 13:54:53.664 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1   | 2018-08-19 13:54:53.692 UTC [24] LOG:  database system was shut down at 2018-08-19 13:54:03 UTC
db_1   | 2018-08-19 13:54:53.712 UTC [1] LOG:  database system is ready to accept connections
app_1  | db init successful
app_1  | create_userinfo_table started
app_1  | create_userinfo_table finished
app_1  | inside RegisterUser in Golang
app_1  | here is the users email:
app_1  | %s [email protected]
app_1  | here is the users password:
app_1  | %s ANOTHERSECRETPASSWORD
app_1  | value of randSeq,  7NLHzuVRuTSxYZyNP6MxPqdvS0qy1L6k
app_1  | search_userinfo_table started
app_1  | value of OKtoAdd, %t true
app_1  | last inserted id = 1 //I inserted in database!
app_1  | value of initUserRet,  added

I can also connect to postgres in another terminal tab and verify that the database was written to correctly using psql -h 0.0.0.0 -p 5432 -U patientplatypus zennify. Here is the output of the userinfo table:

zennify=# TABLE userinfo
;
         email         |                           password                           |            regstring             | regbool | uid 
-----------------------+--------------------------------------------------------------+----------------------------------+---------+-----
 [email protected] | $2a$14$u.mNBrITUJaVjly15BOV9.Q9XmELYRjYQbhEUi8i4vLWtOr9QnXJ6 | r33ik3Jtf0m9U3zBRelFoWyYzpQp7KzR | f       |   1
(1 row)

So writing to the database once works!

HOWEVER

Let's now do the following:

$docker-compose stop
backend_app_1 exited with code 2
db_1   | 2018-08-19 13:55:51.585 UTC [1] LOG:  received smart shutdown request
db_1   | 2018-08-19 13:55:51.589 UTC [1] LOG:  worker process: logical replication launcher (PID 30) exited with exit code 1
db_1   | 2018-08-19 13:55:51.589 UTC [25] LOG:  shutting down
db_1   | 2018-08-19 13:55:51.609 UTC [1] LOG:  database system is shut down
backend_db_1 exited with code 0

From reading the other threads on this topic using docker-compose stop as opposed to docker-compose down should persist the local database. However, if I again use docker-compose up and then, without writing a new value to the database simply query the table in postgres it is empty:

zennify=# TABLE userinfo;
 email | password | regstring | regbool | uid 
-------+----------+-----------+---------+-----
(0 rows)

I had thought that I may have been overwriting the table in my code on the initialization step, but I only have (golang snippet):

_, err2 := db.Exec("CREATE TABLE IF NOT EXISTS userinfo(email varchar(40) NOT NULL, password varchar(240) NOT NULL, regString  varchar(32) NOT NULL, regBool bool NOT NULL, uid serial NOT NULL);")

Which should, of course, only create the table if it has not been previously created.

Does anyone have any suggestions on what is going wrong? As far as I can tell this has to be a problem with docker-compose and not my code. Thanks for the help!

EDIT:

I've looked into using version 2 of docker and following the format shown in this post (Docker compose not persisting data) by using the following compose:

version: "2"  
services:  
  app:
    build: .
    command: ["go", "run", "main.go"]
    ports:
      - "8081:8081"
    depends_on:
      - db
    links:
      - db
  db:
    image: postgres:latest
    environment:
      POSTGRES_DB: zennify
      POSTGRES_USER: patientplatypus
      POSTGRES_PASSWORD: SUPERSECRETPASSWORD
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data/
volumes:
  pgdata: {}

Unfortunately again I get the same problem - the data can be written but does not persist between shutdowns.

EDIT EDIT:

Just a quick note that using docker-compose up to instantiate the service and then relying on docker-compose stop and docker-compose start has no material affect on persistence of the data. Still does not persist across restarts.

EDIT EDIT EDIT:

Couple more things I've been finding out. If you want to properly exec into the docker container to see the value of the database you can do the following:

docker exec -it backend_db_1 psql -U patientplatypus -W zennify

where backend_db_1 is the name of the docker container database patientplatypus is my username and zennify is the name of the database in the database container.

I've also tried, with no luck, to add a network bridge to the docker-compose file as follows:

version: "3"  
services:  
  db:
    build: ./db
    image: postgres:latest
    environment:
      POSTGRES_USER: patientplatypus
      POSTGRES_PASSWORD: SUPERSECRET
      POSTGRES_DB: zennify
    ports:
      - 5432:5432
    volumes:
      - ./db/pgdata:/var/lib/postgresql/data
    networks:
      - mynet
  app:
    build: ./
    command: bash -c 'while !</dev/tcp/db/5432; do sleep 5; done; go run main.go'
    ports:
      - 8081:8081
    depends_on:
      - db
    links:
      - db
    networks:
      - mynet
networks:
  mynet:
    driver: "bridge"

My current working theory is that, for whatever reason, my golang container is writing the postgres values it has to local storage rather than the shared volume and I don't know why. Here is my latest idea of what the golang open command should look like:

data.InitDB("postgres://patientplatypus:SUPERSECRET@db:5432/zennify/?sslmode=disable")
...
func InitDB(dataSourceName string) {
    db, _ := sql.Open(dataSourceName)
    ...
}

Again this works, but it does not persist the data.

like image 342
Peter Weyand Avatar asked Aug 19 '18 14:08

Peter Weyand


1 Answers

I had exactly the same problem with a postgres db and a Django app running with docker-compose.

It turns out that the Dockerfile of my app was using an entrypoint in which the following command was executed: python manage.py flush which clears all data in the database. As this gets executed every time the app container starts, it clears all data. It had nothing to do with docker-compose.

like image 148
bartaelterman Avatar answered Oct 31 '22 18:10

bartaelterman