In this article, I'll describe some basic troubleshooting associated with using the official Docker Postgres image. Specifically, this article will consider some common pitfalls encountered in using the intialization script feature of this image.
The first thing to be aware of is that the script must have executable permissions set, because Docker copies over the permissions from the local filesystem.
If, looking the docker postgis container logs (docker-compose logs --follow postgis) contain the following error,
postgis | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sh
postgis | /usr/local/bin/docker-entrypoint.sh: /docker-entrypoint-initdb.d/init.sh: /bin/bash: bad interpreter: Permission denied
then the initialization script should be made executable:,
chmod +x migration/bin/init/init.sh
If creating a fresh docker image with a new DB, then the init.sh script will need to run again. There may be some manual clean up to do in order for the Postgres image to run the init script.
For example, it is important to remove any existing postres data volume. Otherwise, the intialization script will not run.
Find the volume by running
docker volume ls -q
Look for the volume by name, in my case it was
breadcrumbs_pg_data
If it exists, delete it by running
docker volume rm breadcrumbs_pg_data
If you get the error:
Error response from daemon: remove breadcrumbs_pg_data: volume is in use - [d473cd3b40293fc1023f1108f2b09118a59df03950178fa24e1f55c636246da8]
Then remove the indicated container. In this case:
docker rm d473cd3b4029
Note that this removes the DB data; so be careful.
Now it should be possible to create a fresh postgres image with use of the initialization script.
Part 2 shares some tips and code examples for setting up your PostgreSQL database with Docker Compose and Flyway.
Docker Compose is useful when you want to run multiple Docker containers behind a single application. For example, a Node.js API running on a container communicates with a PostgreSQL database on another container. Docker Compose simplifies setup for multi-container applications. It also enables you to run the entire application with a single command.
Flyway helps with managing an evolving database. Essentially, it’s a way to keep a record of changes to the Database. This includes changes like new tables, new columns, and dropped tables (to name a few). The version history enables you to
It’s a good idea to consider using a database migration tool for production applications.
The Docker Compose file is where the applications’ docker images are configured.
Here is an example Docker Compose file which runs PostgreSQL and Flyway containers. By default, the Compose file is expected to be located at ./docker-compose.yml
Here is an example docker-compose.yml file from one of my applications.
version: '3.4'
services:
postgres:
image: postgres
container_name: postgres
environment:
- POSTGRES_USER=${DB_USER}
- POSTGRES_PASSWORD=${DB_PW}
- DB_NAME=${DB_NAME}
- ALLOW_IP_RANGE=0.0.0.0/0
ports:
- '5432:5432'
volumes:
- 'pg_data:/var/lib/postgresql'
- ./migration/bin/init/init.sh:/docker-entrypoint-initdb.d/init.sh
restart: always
flyway:
image: flyway/flyway
# postgres url schema: jdbc:postgresql://host:port/database
command: -url=jdbc:postgresql://postgis:${DB_PORT}/${DB_NAME} -user=${DB_USER} -password=${DB_PW} -connectRetries=10 migrate info
volumes:
- ./migration/sql:/flyway/sql
links:
- postgres
depends_on:
- postgres
volumes:
pg_data:
Let’s break this file down line by line to understand each part.
version (optional) — This value is only informative, it is not required since version 1.27.0. It indicates the intended docker-compose version.
services (required) — This property contains the configuration for each of this application’s containers. Each service represents a component that can be scaled or replaced independently of others.
volumes (optional) — Used to persist data generated by Docker containers. The top-level key can be empty, in which case it uses the default configuration.
The docker-compose.yml file above configures two services, Postgres and Flyway. Let’s dig into these two configurations.
Here is an excerpt of the Postgres service so you can avoid too much scrolling.
postgis:
image: postgis/postgis
container_name: postgis
environment:
- POSTGRES_USER=${DB_USER}
- POSTGRES_PASSWORD=${DB_PW}
- DB_NAME=${DB_NAME}
- ALLOW_IP_RANGE=0.0.0.0/0
ports:
- '5432:5432'
volumes:
- 'pg_data:/var/lib/postgresql'
- ./migration/bin/init/init.sh:/docker-entrypoint-initdb.d/init.sh
restart: always
image — Indicates which Docker image to use as the basis for this service. Docker Hub Image Library hosts thousands of images. The value (postgres) targets the official Postgres image.
container_name — Can be used to specify a custom container name. If you do not provide a name, Docker will generate a random name (in the form of a UUID). Think of this as a variable name; it is just a way to add meaning when referring to a container.
environment — A place to add environment variables. Certain images, like the official Postgres image, will look for values in specified environment variables. For example, see the ‘Environment Variables’ section here.
ports — Exposes containers internal ports for external clients to connect to. The exposed port does not need to match the actual container port. The docker-compose.yml file above uses the format, HOST:CONTAINER.
volumes — Used to mount paths on the host or named volumes. Remember a volume is used to persist data. In the docker-compose.yml file above, an initialization script is mounted to the container for use by the Postgres image. For more information about initialization scripts, see part I of this series.
The line pg_data:var/lib/postgresql is a named volume (pg_data) mounted on the container at var/lib/postgresql.
restart — This sets the container’s restart policy. If not specified, the default is to never restart. Specifying always means the container will restart anytime it is stopped.
flyway:
image: flyway/flyway
# postgres url schema: jdbc:postgresql://host:port/database
command: -url=jdbc:postgresql://postgis:${DB_PORT}/${DB_NAME} -user=${DB_USER} -password=${DB_PW} -connectRetries=10 migrate info
volumes:
- ./migration/sql:/flyway/sql
links:
- postgis
depends_on:
- postgis
command — This particular command value requires some extra explanation. When you run a Docker container, it constructs a command line by concatenating the entrypoint and command. The Flyway base image defines an entrypoint. See here. Therefore, the docker-compose.yml file just needs to provide arguments for the Flyway entrypoint command.
links (legacy feature) — Expresses a link to containers in another service. Links also express dependency between services, which tells Docker which services to start first. See Networking in Compose regarding the suggested way to do this when building new containers.
The most common use for Flyway is “versioned migrations”. A “migration” is the name used by Flyway for any change to the database. Migrations can include changes to tables (creating, altering, dropping), indexes, keys, and data types.
Flyway expects these files to follow a specific naming convention. The naming convention is defined in detail here. For example, the migration files for one of my projects are named:
Given the volume definition above, the migration files should be stored on the host machine under ./migration/sql. Notice under volumes this path is mounted on the container:
volumes:
- ./migration/sql:/flyway/sql
Flyway will automatically run any migrations not already applied to the DB. Therefore, to change the database, add the SQL commands to a new migration file on the host machine.
For example, the file V1__initial_setup_create_events.sql contains:
CREATE TABLE IF NOT EXISTS notes(
id SERIAL PRIMARY KEY,
note TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS breadcrumbs(
id SERIAL PRIMARY KEY,
data_type CHAR(6) NOT NULL,
data_id INTEGER REFERENCES notes (id),
geog GEOGRAPHY(POINTZ, 4326)
);
GRANT ALL PRIVILEGES ON TABLE breadcrumbs TO breadcrumbs_user;
GRANT ALL PRIVILEGES ON TABLE notes TO breadcrumbs_user;
For a more complete example of using Flyway and PostgreSQL with Docker Compose, check out my backend application, Breadcrumbs (which is demonstrated with a UI here). It includes Flyway versioned migrations, an initialization script, docker-compose, and a Dockerfile. The Flyway versioned migration files can be found here.
I hope this reference is helpful. Leave a comment if you have any questions, I’ll gladly answer.