It didn't work for me with the COPY approach in Dockerfile. But I managed to run my init.sql file by adding the following to docker-compose.yml:
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
init.sql was in the same directory as my docker-compose.yml.
I picked the solution from this gist. Check this article for more information.
Using docker-compose to create tables in postgresql database - Stack Overflow
How to run a script PostgreSQL with Docker Compose in the initialization ?
Specify an initialization script for Postgres, using docker run
docker-compose and create db in Postgres on init - Stack Overflow
Videos
It didn't work for me with the COPY approach in Dockerfile. But I managed to run my init.sql file by adding the following to docker-compose.yml:
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
init.sql was in the same directory as my docker-compose.yml.
I picked the solution from this gist. Check this article for more information.
I dont want to have to enter psql in order to type in
You can simply use container's built-in init mechanism:
COPY init.sql /docker-entrypoint-initdb.d/10-init.sql
This makes sure that your sql is executed after DB server is properly booted up.
Take a look at their entrypoint script. It does some preparations to start psql correctly and looks into /docker-entrypoint-initdb.d/ directory for files ending in .sh, .sql and .sql.gz.
10- in filename is because files are processed in ASCII order. You can name your other init files like 20-create-tables.sql and 30-seed-tables.sql.gz for example and be sure that they are processed in order you need.
Also note that invoking command does not specify the database. Keep that in mind if you are, say, migrating to docker-compose and your existing .sql files don't specify DB either.
Your files will be processed at container's first start instead of build stage though. Since Docker Compose stops images and then resumes them, there's almost no difference, but if it's crucial for you to init the DB at build stage I suggest still using built-in init method by calling /docker-entrypoint.sh from your dockerfile and then cleaning up at /docker-entrypoint-initdb.d/ directory.
I have this:
services:
db:
image: postgres:16.3-alpine3.20
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD:
POSTGRES_DB: pento
volumes:
- ./init-db.sql:/docker-entrypoint-initdb.d/init-db.sql
- pg_data:/var/lib/postgresql/data
ports:
- "5432:5432"
volumes:
pg_data:And I would like to CREATE other DATABASE when container is starting using a sql script.
For example:
/init-db.sql CREATE DATABASE pento_dev;
How can I do that ? Is it possible ?
I there a way to specify an initialization script for Postgres, using docker run?
If I use Docker Compose, this line will copy the script and run it:
volumes: - ./init.sql:/docker-entrypoint-initdb.d/init.sql
However, this syntax doesn't work in the CLI:
$ docker volume create postgres-data $ docker run --detach \ --rm \ --name postgres \ --publish 5432:5432 \ --env-file .env \ --volume postgres-data:/var/lib/postgresql/data \ --volume ./init.sql:/docker-entrypoint-initdb.d/init.sql \ postgres:9.6.22 docker: Error response from daemon: create ./init.sql: "./init.sql" includes invalid characters for a local volume name, only "[a-zA-Z0-9][a-zA-Z0-9_.-]" are allowed. If you intended to pass a host directory, use absolute path.
Is this possible?
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!
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.