117

I have a question. I am pretty new to docker, so what I am trying to do is create a docker-compose file that on compose command will also create the database. Problem is that does not create DB as I ask it nicely. So my docker-compose looks like:

version: '3'

services:
  db:
    image: postgres:latest
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: 'postgres'
    volumes:
      - database_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  database_data:
    driver: local

And when I start docker-compose up in log I can see

db_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1  |
db_1  | 2020-01-13 11:14:36.259 UTC [1] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1  | 2020-01-13 11:14:36.259 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1  | 2020-01-13 11:14:36.260 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1  | 2020-01-13 11:14:36.264 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1  | 2020-01-13 11:14:36.277 UTC [29] LOG:  database system was shut down at 2020-01-13 11:10:57 UTC
db_1  | 2020-01-13 11:14:36.280 UTC [1] LOG:  database system is ready to accept connections

In my init SQL there is only 1 line:

CREATE DATABASE "MyDataBase";

As I list DB is Postgres container my DB is nowhere to be found. What could be the source root of this problem?

Improve this question

7 Answers 7

Reset to default
207

If, when you start your Docker Compose, you're getting:

PostgreSQL Database directory appears to contain a database; Skipping initialization

you need to proactively remove the volumes which were set up to store the database.

The command docker-compose down doesn't do this automatically.

You can request removal of volumes like this:

docker-compose down --volumes

Be warned that this will delete any data you had in any database before. You can't get this data back if you remove the volume which contained it!

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

4 Comments

oh. This definitely is a time saver - been spending time debugging the POSTGRES_USER env var not being picked up, thanks!
will this destroy the data? what if I want to add a new database and keep the existing ones?
!! BE CAREFUL !! As this will DESTROY the data as it did just for me.
This command ran but did not work for me (without error or warning), as my user did not have file permissions for file deletion I imagine. I had to sudo rm -r my_volume. The owner of it was user postgres.
42

According to the documentation of postgres docker image you did everything correct.

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

But, there is a catch which I think you missed based on log that you posted above.

Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup.

So, I would give it a try to empty database_data directory and run again docker-compose up.

Improve this answer

3 Comments

I don't have any volumes for persisting data after container is shut down and I'm getting the same log message. What I had to do was delete the shutdown container and then re-run my docker compose file.
Where is the database_data located?
May I know if there an automatic way of deleting volumes ? For examples when I shut down the container ?
20

Had a similar problem and what worked for me was deleting containers and volumes

List all containers by id:

docker container ls -qa

run this to each container:

docker container rm [id]

And same with volumes:

docker volume ls
docker volume rm [VolumeName]

and the when I docker-compose up -d it's all working well

reference for removing from docker

Improve this answer

1 Comment

Empty data directory means postgres re-runs the initialize step
10

I had to do a: docker-compose down --volumes to remove the volumes first.

And then had to do use: docker-compose up --build to make sure PostGres uses the sql files.

Also make sure that the db directory is on the same level as the docker file.

Improve this answer

Comments

7

In my case, I didn't declare a volume to /var/lib/postgresql/data, but it always has been created a volume in /var/lib/docker/volumes/, and the data persisted after stop and start the container.

So, I forced the recreation of anonymous volumes.

docker-compose up --renew-anon-volumes

With that, you won't need to empty the database_data directory and run again docker-compose up every single time.

Improve this answer

1 Comment

This doesn't seem to work anymore. Either do docker compose down --volumes or create a custom init bash script that wipes the postgres data on each db service start.
3

From the Initialization Scripts section at https://hub.docker.com/_/postgres:

Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

This was the cause of the PostgreSQL Database directory appears to contain a database; Skipping initialization error in the log output. An error in the initialization script will cause the container to restart with an intialized data directory, and so your init script will not run.

Improve this answer

2 Comments

An error in my start up script lead me here. I was not seeing any error messages in the log but I think it was because the container was restarting and carrying on as if it was initialized.
This was the case for me: my initialization script was failing and so the container was restarting. It turned out that the error was due to the fact that in Windows, the files use the CRLF newline as opposed to the expected LF. It was fundamental for me to change that in order for the script to be run without failing.
0

In my case the problem was with the permissions of the init-user-db.sh file, make sure it has execute permissions.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.