Published on

Docker Postgres Container Initial Scripts

Authors

When using a docker database container you often want to run one or more db scripts once the container starts for the first time.

If you are using the official Postgres container this is luckily relatively straightforward.

Project Structure

Pretend you have a project structure that looks as below (you can have whatever other folders you want)

infrastructure/
                -> migrations/
                                -> 1-initial.sql
                docker-compose.yml
makefile

And the compose file looks as follows:

version: '3'
services:
  db:
    image: postgres
    #    environment:
    #      POSTGRES_PASSWORD: "your-super-secure-password"
    volumes:
      - db-data:/var/lib/postgresql/data
      - ./migrations/:/docker-entrypoint-initdb.d/
    ports:
      - '5432:5432'
volumes:
  db-data:
    driver: local

Running Compose Against the Docker-compose in the Infrastructure Folder

We then run this compose file from the root of the project:

docker-compose -f ./infrastructure/docker-compose.yml up -d

Important points to note

  • The migrations volume you specify in the docker-compose.yml is of the form: - ./host_system_path_to_migrations_folder/:/docker-entrypoint-initdb.d/
    • host_system_path_to_migrations_folder this is relative to the docker-compose/docker context. This context is the folder where the compose file is run from in our case it is ./infrastructure
    • For this reason, we do not use ./infrastructure/migrations but instead use ./migrations as the context of the compose command is ./infrastructure/
    • If you do specify the path wrong that folder path will be created from the compose context
  • These DB scripts are only run on a fresh container create i.e. the volume cannot exist yet.

Confirm It Deployed Correctly

To confirm it deployed and migrated correctly lets first get the container's id:

docker ps

Confirm it ran by looking at the logs docker logs your-containers-id.

You should see each .sql script mentioned in the logs.

If you see it say something like /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/ it means that you did not mount your scripts folder properly

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


WARNING: enabling "trust" authentication for local connections
Success. You can now start the database server using:
You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.
    pg_ctl -D /var/lib/postgresql/data -l logfile start

****************************************************
WARNING: No password has been set for the database.
         This will allow anyone with access to the
         Postgres port to access your database. In
         Docker's default configuration, this is
         effectively any other container on the same
         system.

         Use "-e POSTGRES_PASSWORD=password" to set
         it in "docker run".
****************************************************
waiting for server to start....2019-07-23 13:29:04.146 UTC [45] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-07-23 13:29:04.156 UTC [46] LOG:  database system was shut down at 2019-07-23 13:29:03 UTC
2019-07-23 13:29:04.160 UTC [45] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/1-initial.sql
CREATE TABLE
CREATE TABLE


2019-07-23 13:29:04.309 UTC [45] LOG:  received fast shutdown request
waiting for server to shut down....2019-07-23 13:29:04.311 UTC [45] LOG:  aborting any active transactions
2019-07-23 13:29:04.313 UTC [45] LOG:  background worker "logical replication launcher" (PID 52) exited with exit code 1
2019-07-23 13:29:04.313 UTC [47] LOG:  shutting down
2019-07-23 13:29:04.338 UTC [45] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2019-07-23 13:29:04.426 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-07-23 13:29:04.426 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2019-07-23 13:29:04.430 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-07-23 13:29:04.441 UTC [63] LOG:  database system was shut down at 2019-07-23 13:29:04 UTC
2019-07-23 13:29:04.445 UTC [1] LOG:  database system is ready to accept connections

Additional Resources