Postgres: docker-entrypoint-initdb.d

[hunsche]

How do I create a database in the docker-entrypoint-initdb.d directory?
Need to create an image where it already comes with some database created.

[marema31]

Hello,
You just have to put a file with .sql extension that contains:
CREATE DATABASE "mydb";

if you want to also insert data be sure to add a
\c "mydb" line before the inserts because the file is run as postgres role in the postgres database by default.

[kachkaev]

I'm also interested in having a lightweight container that instantly spins up with some pre-built data. Just doing COPY db/ /docker-entrypoint-initdb.d/ works, but feels suboptimal, because the data is replicated twice and the restoring the dump takes time.

Was trying 1000's variations of the following, but with no luck so far:

FROM postgres:9.5-alpine AS data-donor
COPY etc/db/ /docker-entrypoint-initdb.d/
RUN docker-entrypoint.sh
ENTRYPOINT ["/bin/sh"]

FROM postgres:9.5-alpine
COPY --from=data-donor /var/lib/postgresql/data /var/lib/postgresql/data/

According to the logs of the donor intermediate container, the data does load, but when I RUN ls -la /var/lib/postgresql/data in any of the containers, the folder appears empty. I suspect this is because it belongs to user postgres, but I can't find a workaround. Just running the first two lines does produce an image with the data I want, but the DB gets restored every time, which is slow.

Having an example on https://hub.docker.com/_/postgres/ would save people hundreds of hours! I guess the situations when the data is bound with the lightweight autonomous containers become more often as people move their microservices into kubernetes clusters and docker swarms.

[tianon]

The "/var/lib/postgresql/data" directory is a volume, so you cannot
pre-populate it. You can set PGDATA to override the location to somewhere
that isn't a volume, or you could build your own image that does not
include the VOLUME declaration.

[kachkaev]

Awesome, thanks for the hint @tianon!

This worked:

FROM postgres:9.5-alpine AS data-donor
RUN apk add --no-cache unzip
COPY etc/db/ /docker-entrypoint-initdb.d/
ENV PGDATA=/pgdata
RUN docker-entrypoint.sh --help

FROM postgres:9.5-alpine
ENV PGDATA=/pgdata
COPY --from=data-donor /pgdata /pgdata
RUN chown -R postgres:postgres /pgdata

I'm adding --help to RUN docker-entrypoint.sh --help in the first image just to trick the script – it otherwise does not start at all or launches a foreground process, which never exits.

Problem solved! Your hint about the VOLUME was the key!

UPD: The container starts, but there are still some issues: https://github.com/docker-library/postgres/issues/319

[hunsche]

Thanks, use "COPY db/ /docker-entrypoint-initdb.d/" work.

[kachkaev]

@hunsche that's also an option, just bear in mind that your DB will be restored from a dump on each container start. Doing the build in two stages will give you faster launch times.