4

This question is related to this one: Docker - How can run the psql command in the postgres container?

But the solution provided above is only valid when you are doing things manually while here one would like to do things automatically using docker-compose.

So this docker container works:

FROM postgres
COPY init.sql /docker-entrypoint-initdb.d/

but this docker container fails: 

FROM postgres
RUN psql -U postgres -c "CREATE USER user WITH PASSWORD 'pass';"

because by the time the RUN is executed the postgresql server has not started yet!

Is there a way to overcome this limitation?

George Pligoropoulos
  • 2,919
  • 3
  • 33
  • 65
  • Yes, but you will have to add script started on container start (instead of last `CMD ["postgres"]` command) and this script will run psql command and will end with "exec postgres". – JosMac Apr 27 '18 at 14:08
  • @JosMac I don't get you exactly. This last `CMD["postgres"]` command you are referring to is called automatically? And insert `exec postgres`where?... An example would help – George Pligoropoulos Apr 27 '18 at 14:28
  • @GeorgiosPligoropoulos have you checked if this question here fits your needs? [How to create User/Database in script for Docker Postgres](https://stackoverflow.com/questions/26598738/how-to-create-user-database-in-script-for-docker-postgres) – tgogos Apr 27 '18 at 14:33
  • @tgogos I am afraid this is not helpful because there are instructions on how to execute things manually while clearly what is required here is to have things run within docker. At the end of the day all we should run from command line is `docker-compose up -d`. And all the steps, creating the database etc should be run automatically. Hope this gives a more clear picture – George Pligoropoulos Apr 27 '18 at 15:46
  • You can view this instruction: https://stackoverflow.com/questions/43974888/run-script-after-container-entrypoint-in-docker-compose – yuen26 Jun 28 '19 at 10:25

1 Answers1

5

you can run some scripts in posgres when you mount the folder with your script to docker container at /docker-entrypoint-initdb.d/ se the docker-compose.yml that mounts the folder sql. The postgres images is awesome and will run the scripts each time the postgres starts...

version: '3.6'

services:  
  my_db:
    image: postgres:alpine
    volumes: ["/somepath/sql/:/docker-entrypoint-initdb.d/"]

the file structure of /somepath/

- docker-compose.yml
- sql
  - new_extension.sql

and cat sql/new_extension.sql

/* create extension for cureent DB */
CREATE EXTENSION IF NOT EXISTS citext;
/* create extension over the template1 so all created databases after that also have the extension */
\c template1
CREATE EXTENSION IF NOT EXISTS citext;
Mazel Tov
  • 2,064
  • 14
  • 26
  • I am afraid I already have tried this but I'll tell you what the problem is. I know the command to restore a DB backup to a **specific** database name with `psql ....` but I cannot find how to set the current working database with an SQL statement in postgresql (for example MySQL has the USE keyword). That's why we found ourselves in the situation above. But if you know please share :) – George Pligoropoulos Apr 28 '18 at 08:09
  • i updated my answer, if you switch to template1 then all db created after that will have also the extension.... i dont understand what you want to achieve here, can you post your .sql ? – Mazel Tov Apr 28 '18 at 10:06
  • Related question that gives also context to this question: https://stackoverflow.com/questions/50080850/how-to-specify-the-current-working-database-for-the-initialization-script-of-a-p – George Pligoropoulos Apr 28 '18 at 21:48
  • Great Job. It's a perfect solution. Thank you!! – CrazyGeek May 21 '19 at 10:35