Why Your Docker-Compose Fails to Create a Custom PostgreSQL Database

Discover how to resolve the issue of your custom database not being created in Docker-Compose for PostgreSQL, with step-by-step guidance.
---
This video is based on the question https://stackoverflow.com/q/72517169/ asked by the user 'AlexanderKondrat' ( https://stackoverflow.com/u/16174212/ ) and on the answer https://stackoverflow.com/a/72517244/ provided by the user 'Hans Kilian' ( https://stackoverflow.com/u/3924803/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Why database is not create when docker-compose up?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Why Your Docker-Compose Fails to Create a Custom PostgreSQL Database

When working with Docker to run PostgreSQL, you may encounter a common problem: your custom database fails to create, leaving you with just a default database named postgres. This situation can be frustrating, especially when you have high hopes for your database setup. In this guide, we will explore this issue, why it occurs, and how to effectively solve it.

The Problem Explained

Imagine you’re trying to set up PostgreSQL using Docker and docker-compose, but after running docker-compose up -d --build, you find that only the default postgres database is created. Your custom configurations and scripts seem to have no effect. This is a common concern that can trip up many developers, especially those new to Docker and container orchestration.

What You Did

Here’s a brief overview of your setup:

Dockerfile to build the PostgreSQL image.

docker-compose.yml to define your PostgreSQL service settings, including volume mappings and environment variables for the database configuration.

An initialization script (create_extension.sql) intended to run when the database is created.

An .env file that holds sensitive information like database names and passwords.

You expected that your provided environment settings and scripts would create your custom database and needed extensions, but instead, they were ignored.

Understanding the Solution

The key to solving this issue lies in understanding how PostgreSQL initialization works within Docker containers. The PostgreSQL image only uses the provided environment variables and initialization scripts when it detects that there is no existing database on startup. Thus, if a volume previously exists (like postgres_data in your setup), PostgreSQL will skip the initialization steps, leading to the behavior you're experiencing.

Steps to Resolve the Issue

Here’s a clear walkthrough of how to get your custom database created successfully:

Delete the Existing Volume: Since the existing volume holds a database, you need to remove it.

You can do this with the command:

[[See Video to Reveal this Text or Code Snippet]]

Replace <your_project_name> with the actual name of your Docker project.

Rebuild and Restart the Container: After deleting the volume, run the docker-compose command again.

[[See Video to Reveal this Text or Code Snippet]]

This command rebuilds the container and ensures it starts fresh without an existing database.

Check Your Custom Database: After running the above command, check to see if your custom database (mydb in this case) has been created along with the specified extensions defined in your SQL scripts.

Verification

Finally, verify your setup by connecting to your PostgreSQL instance:

[[See Video to Reveal this Text or Code Snippet]]

Look for your custom database and confirm that the extensions are correctly applied.

Conclusion

If your Docker setup for PostgreSQL isn’t behaving as expected, remember that the presence of an existing database can interfere with the initialization process. By deleting the existing volume and restarting your setup, you can allow your custom configurations to take effect. This solution not only applies to PostgreSQL but can be a handy approach for other services using Docker with similar initialization logic.

Happy coding, and may your databases be ever in your favor!