CODEWITHSUNDEEP
bashpostgresqldocker
Sadan A.
Sadan A.

Reputation: 1107

Unable to run queries from a file using psql command line with docker exec

I have a bash file should bring the postgres docker container online and then run a .sql file to create the databases. But it's throwing the error.

psql: error: provision-db.sql: No such file or directory

I have checked the path and the file exists at the same level of this bash script. Following is the content of my bash file.

#!/usr/bin/env bash

docker-compose up -d db

# Ensure the Postgres server is online and usable
until docker exec -i boohoo.postgres pg_isready --host="${POSTGRES_HOST}" --username="${POSTGRES_USER}"
do
    echo "."
    sleep 1
done

docker exec -i boohoo.postgres psql -h "${POSTGRES_HOST}" -U "${POSTGRES_USER}" -a -q -f provision-db.sql

And this is the provision-db.sql file.

DROP DATABASE "boo-hoo";
CREATE DATABASE "boo-hoo";
GRANT ALL PRIVILEGES ON DATABASE "boo-hoo" TO postgres;

This is the part of docker-compose.yml

version: '3.3'

services:
  db:
    container_name: boohoo.postgres
    hostname: postgres.boohoo
    image: postgres
    ports:
      - "15432:5432"
    environment:
      POSTGRES_USER: "postgres"
      POSTGRES_PASSWORD: "postgres"

Upvotes: 5

Views: 1885

Answers (2)

ckaserer
ckaserer

Reputation: 5702

The short version

This works

cat provision-db.sql | docker exec -i boohoo.postgres bash -c 'psql -U ${POSTGRES_USER} -w -a -q -f -'

The long version

multiple things here

1) why does following command not find the provision-db.sql?

docker exec -i boohoo.postgres psql -h "${POSTGRES_HOST}" -U "${POSTGRES_USER}" -a -q -f provision-db.sql

because the provision-db.sql is on your host and not in your container. Therefore, when you execute the psql command inside the container it can not find the file

2) Why didn't my first solution work?

cat provision-db.sql | docker exec -i boohoo.postgres psql -h "${POSTGRES_HOST}" -U "${POSTGRES_USER}" -a -q -f - should do the trick asuming provision-db.sql

That is due to the fact, that the variables ${POSTGRES_USER} and ${POSTGRES_PASSWORD} get evaluated on your host machine and I guess they are not set there. In addition, I forgot to specify the -w flag to avoid the password prompt

3) Why does that work?

cat provision-db.sql | docker exec -i boohoo.postgres bash -c 'psql -U ${POSTGRES_USER} -w -a -q -f -'

Well, let's go through it step by step.

First, we print the content of provision-db.sql, which resides on the host machine to stdout and pipe it to the next command via |.

docker-exec executes a command in the container specified (boohoo.postgres). By specifying the -i flag we allow the stdin from your host to go to stdin in the container <- that's important.

In the container, we execute bash -c which is just a wrapper to avoid evaluating the bash variables on the host. We want the variables from the container and by putting it into single quotes we can do that.

docker-exec boohoo.postgres bash -c "echo $POSTGRES_USER"

evaluates the host env variable named POSTGRES_USER.

docker-exec boohoo.postgres bash -c "echo $POSTGRES_USER"

evaluates the container env variable named POSTGRES_USER.

Next we just have to get our postgres command in order.

psql -U ${POSTGRES_USER} -w -a -q -f -

-U specifies the user
-w does not ask for password
-q do it quietly
-f - process whatever you get from stdin

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246523

-f is an option for psql and not for docker exec, and psql is running inside the container, so it can only access the file if it is inside the container as well.

Upvotes: 0

Related Questions