Postgres only initializes the database if no database is found, when the container starts. Since you have a volume mapping on the database directory, chances are that a database already exists.
If you delete the db_data volume and start the container, postgres will see that there isn't a database and then it'll initialize one for you using the scripts in docker-entrypoint-initdb.d.
Docker entrypoint-initdb.d postgres
psql -h localhost fails in init script
docker-entrypoint.sh ignoring my init scripts
postgresql - Scripts in the /docker-entrypoint-initdb.d folder are ignored - Stack Overflow
Videos
person ancient cough retire squash paint books ruthless jeans provide
This post was mass deleted and anonymized with Redact
Postgres only initializes the database if no database is found, when the container starts. Since you have a volume mapping on the database directory, chances are that a database already exists.
If you delete the db_data volume and start the container, postgres will see that there isn't a database and then it'll initialize one for you using the scripts in docker-entrypoint-initdb.d.
The accepted answer was correct (when it was written)
There was a subsequent discussion in github with the maintainer of the postgres docker image about supporting a mechanism similar to the mysql /always-init.d/ etc.
The link to that discussion: https://github.com/docker-library/postgres/pull/496
The solution with docker is to provide a custom entry-point, there is a bit of a bug on version shared in issue 496 so I'm posting a more updated version here in hopes others will find it useful:
#!/usr/bin/env bash
## copied from: https://github.com/docker-library/postgres/pull/496#issue-358838955
set -Eeo pipefail
echo " custom-entry-point"
# Example using the functions of the postgres entrypoint to customize startup to always run files in /always-initdb.d/
source "$(which docker-entrypoint.sh)"
docker_setup_env
docker_create_db_directories
# assumption: we are already running as the owner of PGDATA
# This is needed if the container is started as `root`
#if [ "$1" = 'postgres' ] && [ "$(id -u)" = '0' ]; then
if [ "$(id -u)" = '0' ]; then
exec gosu postgres "$BASH_SOURCE" "$@"
fi
if [ -z "$DATABASE_ALREADY_EXISTS" ]; then
echo " db is missing"
docker_verify_minimum_env
docker_init_database_dir
pg_setup_hba_conf
# only required for '--auth[-local]=md5' on POSTGRES_INITDB_ARGS
export PGPASSWORD="${PGPASSWORD:-$POSTGRES_PASSWORD}"
docker_temp_server_start "$@" -c max_locks_per_transaction=256
docker_setup_db
docker_process_init_files /docker-entrypoint-initdb.d/*
docker_temp_server_stop
else
echo " db already exists"
docker_temp_server_start "$@"
docker_process_init_files /always-initdb.d/*
docker_temp_server_stop
fi
echo " .. starting!"
exec postgres "$@"
I have a standard postgres container running, with the pg_data volume mapped to a directory on the host machine.
I want to be able to run an init script everytime I build or re-build the container, to run migrations and other such things. However, any script or '.sql' file placed in /docker-entrypoint-initdb.d/ only gets executed if the pg_data volume is empty.
What is the easiest solution to this – at the moment I could make a pg_dump pf the pg_data directory, then remove it’s content, and restore from the pg_dump, but it seems pointlessly convoluted and open to errors with potential data loss.
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?
Had the same problem with postgres 11.
Some points that helped me:
- run:
docker-compose rmdocker-compose builddocker-compose up
- The obvious: don't run compose in detached mode. You want to see the logs.
After adding the step docker-compose rm to the mix it worked, finally.
This is how I use postgres on my projects and preload the database.
file: docker-compose.yml
db:
container_name: db_service
build:
context: .
dockerfile: ./Dockerfile.postgres
ports:
- "5432:5432"
volumes:
- /var/lib/postgresql/data/
This Dockerfile load the file named pg_dump.backup(binary dump) or psql_dump.sql(plain text dump) if exist on root folder of the project.
file: Dockerfile.postgres
FROM postgres:9.6-alpine
ENV POSTGRES_DB DatabaseName
COPY pg_dump.backup .
COPY pg_dump.sql .
RUN [[ -e "pg_dump.backup" ]] && pg_restore pg_dump.backup > pg_dump.sql
# Preload database on init
RUN [[ -e "pg_dump.sql" ]] && cp pg_dump.sql /docker-entrypoint-initdb.d/
In case of need retry the loading of the dump, you can remove the current database with the command:
docker-compose rm db
Then you can run docker-compose up to retry load the database.
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 ?