I am using docker-compose to deploy a multicontainer python Flask web application. I'm having difficulty understanding how to create tables in the postgresql database during the build so I don't have to add them manually with psql.
My docker-compose.yml file is:
web: restart: always build: ./web expose: - "8000" links: - postgres:postgres volumes: - /usr/src/flask-app/static env_file: .env command: /usr/local/bin/gunicorn -w 2 -b :8000 app:app nginx: restart: always build: ./nginx/ ports: - "80:80" volumes: - /www/static volumes_from: - web links: - web:web data: restart: always image: postgres:latest volumes: - /var/lib/postgresql command: "true" postgres: restart: always image: postgres:latest volumes_from: - data ports: - "5432:5432"
I dont want to have to enter psql in order to type in:
CREATE DATABASE my_database; CREATE USER this_user WITH PASSWORD 'password'; GRANT ALL PRIVILEGES ON DATABASE "my_database" to this_user; \i create_tables.sql
I would appreciate guidance on how to create the tables.
Fill the port value as 5432 that runs the Docker PostgreSQL Container and provide the name of the database as postgres. Then, fill the username and password fields with the credentials you created while running the PGAdmin container. After providing all required details, click on the “Save” button.
Docker is great for running databases in a development environment! You can even use it for databases of small, non-critical projects which run on a single server. Just make sure to have regular backups (as you should in any case), and you'll be fine.
It didn't work for me with the COPY
approach in Dockerfile
. But I managed to run my init.sql
file by adding the following to docker-compose.yml
:
volumes: - ./init.sql:/docker-entrypoint-initdb.d/init.sql
init.sql
was in the same directory as my docker-compose.yml
. I picked the solution from this gist. Check this article for more information.
I dont want to have to enter psql in order to type in
You can simply use container's built-in init mechanism:
COPY init.sql /docker-entrypoint-initdb.d/10-init.sql
This makes sure that your sql is executed after DB server is properly booted up.
Take a look at their entrypoint script. It does some preparations to start psql correctly and looks into /docker-entrypoint-initdb.d/
directory for files ending in .sh
, .sql
and .sql.gz
.
10-
in filename is because files are processed in ASCII order. You can name your other init files like 20-create-tables.sql
and 30-seed-tables.sql.gz
for example and be sure that they are processed in order you need.
Also note that invoking command does not specify the database. Keep that in mind if you are, say, migrating to docker-compose and your existing .sql
files don't specify DB either.
Your files will be processed at container's first start instead of build
stage though. Since Docker Compose stops images and then resumes them, there's almost no difference, but if it's crucial for you to init the DB at build
stage I suggest still using built-in init method by calling /docker-entrypoint.sh
from your dockerfile and then cleaning up at /docker-entrypoint-initdb.d/
directory.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With