data lost while restarting postgres with docker

Question

I was running a postgres instance with following docker-compose.yml-

postgres:
  restart: always
  image: postgres:latest
  volumes:
    - /data:/var/lib/postgresql
  ports:
    - "5432:5432"
  environment:
    - POSTGRES_USER=admin
    - POSTGRES_PASSWORD=123456
    - POSTGRES_DB=mydb
    - PGDATA=/var/lib/postgresql/data

I had added some tables with few rows. I made a small change in the file, adding container_name: postgres, and restarted with docker-compose up -d

and now when i login to the database, I don't see any tables/data.

psql -h ###### -p 5432 -d mydb -U admin --password

The data directory is added as volumes and is intact.

Is it something to do with initializing postgres?

UPDATE 2016/10/02

Please note that /data is an external hard drive mounted on host docker-machine.

UPDATE 2016/10/02

Also I noticed that when I change the rename the location of docker-compose.yml (from /home/ubuntu/dev/docker-storage/docker-compose.yml to /home/ubuntu/dev/storage/docker-compose.yml), and do a docker-compose up -d, I get an error saying

ERROR: for postgres  Conflict. The name "/postgres" is already in use by container 6de8378a8156ec368748194f8912836a7b5e3212fbb69627093d0f6114b82f0d. 
You have to remove (or rename) that container to be able to reuse that name.

Is that where problem is coming from? I might have removed the original container.

UPDATE 2016/10/06

This seems to be working now. For some weird reason, I had to mount both /var/lib/postgresql and /var/lib/postgresql/data

postgres:
    restart: always
    image: postgres:latest
    container_name: postgres
    volumes:
      - /data/postgresql:/var/lib/postgresql
      - /data/postgresql/data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=admin
      - POSTGRES_PASSWORD=123456
      - POSTGRES_DB=mydb

siddhartha kasaraneni · Accepted Answer · 2020-06-10 10:24:32Z

17

What you did is a hack! The official way to do it, is to have a environment variable named PGDATA, same as the container volume path

here you go:

postgres:
    restart: always
    image: postgres:latest
    container_name: postgres
    volumes:
      - ${PATH_TO_STORAGE}:/var/lib/postgresql/data/:rw <--- check this out
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      PGDATA: /var/lib/postgresql/data/ <--- Check this out

Hope this helps :)

answered Jun 10, 2020 at 10:24

siddhartha kasaraneni

6967 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kampta Over a year ago

Didn't try it myself but seems legit... Accepting the answer! Thanks

Miha Jamsek Over a year ago

Isn't this variable set by default? I never set it, but inspecting container, it has a value and it points to correct folder.

Christopher Rice Over a year ago

For what it's worth as another point of info, I had a PostgreSQL 14 database docker shut down randomly overnight and then refuse to restart with errors about "data already existing." Turns out, all it wanted was an environment variable of PGDATA set to match the actual path on the machine. Why it worked fine and then failed suddenly and then this fixed it, I have no clue... ...but to others on unRAID doing a PostgreSQL DB, it may be a lifesaver. Essentially, just add a new Variable in the docker container settings and set it to the same value you set "Database Storage Path."

Dima Over a year ago

Unfortunately this does not work - data is lost after container is restarted.

RichArt · Accepted Answer · 2016-10-01 20:43:59Z

3

up -d doesn't restart. I think you just started a new instance. You may use restart.

docker-compose restart ...

answered Oct 1, 2016 at 20:43

RichArt

1,69221 silver badges35 bronze badges

2 Comments

kampta Over a year ago

My bad, I think I might have started a new container when I changed the name of directory containing docker-compose.yml. I thought specifying the container_name variable, will resolve the issue (i.e. up -d will start the same container as before all the time)

kampta Over a year ago

I believe I don't know how to use persistent volumes properly. How do I specify a data directory for postgres, such that in case container is destroyed, I can start a new container with existing data directory?

deinspanjer · Accepted Answer · 2016-10-01 22:45:35Z

1

I noticed your host volume directory name is data, but you are mounting it to postgresql rather than postgresql/data.

I am not sure if that could be causing this problem, but it seems like a good thing to clean up.

answered Oct 1, 2016 at 22:45

deinspanjer

5551 gold badge8 silver badges23 bronze badges

1 Comment

kampta Over a year ago

Thanks, I did clean it up, but I don't think that is causing the problem. Modifying the question to reflect the changes

deinspanjer · Accepted Answer · 2016-10-02 14:47:08Z

1

The error about an existing /postgres container means you didn't successfully shut everything down before trying to start the new one with the same name.

It is likely what might have happened is that you started two instances of postgres both pointed at the same data directory. That could possibly cause data corruption.

Specifying a container name is a good way to prevent that from happening because as you saw, Docker won't let you start a second instance with the same name.

answered Oct 2, 2016 at 14:47

deinspanjer

5551 gold badge8 silver badges23 bronze badges

Collectives™ on Stack Overflow

data lost while restarting postgres with docker

4 Answers 4

4 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related