61

Given a file with a SQL insert:

INSERT INTO countries (id, country_code, name)
VALUES
    (1, 'AF', 'Afghanistan'),
    (2, 'AL', 'Albania');

I would like to run the file by using the docker run command on a container that is running postgres.

I've tried this:

docker run -e domain="192.168.99.100" pg /bin/bash -c "psql -d whiteboard_api -a -f inserts_into_countries_table.sql"
psql: could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

you can see my image is pg:

capistrano:whiteboard_v2 jzollars$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
6b500bec9210        bbb                 "/usr/bin/supervisord"   4 weeks ago         Up 4 weeks          0.0.0.0:80->80/tcp       distracted_raman
c1e88f2695f5        wh                  "/usr/bin/supervisord"   4 weeks ago         Up 4 weeks          0.0.0.0:3000->3000/tcp   high_einstein
7e383e99bdc3        pg                  "/usr/lib/postgresql/"   4 weeks ago         Up 4 weeks          0.0.0.0:5432->5432/tcp   pg_test

How can I load this file and run it in a docker container using docker run?

Rob Bednark
  • 25,981
  • 23
  • 80
  • 125
JZ.
  • 21,147
  • 32
  • 115
  • 192

5 Answers5

96

to execute commands against a running container use docker exec.

to copy a file (ex: dump.sql) into a container, use docker cp

So your approach might look something like this:

docker cp ./dump.sql pg_test:/docker-entrypoint-initdb.d/dump.sql
docker exec -u postgres pg_test psql postgres postgres -f docker-entrypoint-initdb.d/dump.sql

here it is in generic form:

docker cp ./localfile.sql containername:/container/path/file.sql
docker exec -u postgresuser containername psql dbname postgresuser -f /container/path/file.sql

And note that if you need to seed your database every time it is run, the folder /docker-entrypoint-initdb.d/ does have special significance, if you're using the offical postgres image

Felipe Pereira
  • 1,368
  • 16
  • 26
code_monk
  • 9,451
  • 2
  • 42
  • 41
  • 1
    Thanks this was a great answer, especially like the "generic form" – JZ. Jan 12 '16 at 20:20
  • is there away to do this without copying the file into the container? – Max Carroll Sep 27 '19 at 14:30
  • I did some looking round and I couldn't find anyway to do it without copying the file into the docker container, if anyone finds a way please post the answer – Max Carroll Sep 27 '19 at 14:42
  • 4
    @MaxCarroll, you can mount your file into the container, rather than explicitely copying it, using the _volume_ flag: `docker run -v ./dump.sql:/docker-entrypoint-initdb.d/dump.sql pg_test` – code_monk Sep 27 '19 at 20:55
85

You can run a sql command file against a running postgres container via the one liner

cat ./query.sql | docker exec -i <container-name> psql -U <user> -d <database>

without having to copy the file from your host machine to the container.

James Conkling
  • 3,235
  • 2
  • 25
  • 37
  • 3
    This worked for me. I was hesitant to use the other answers since they all use multiple lines or would store the file on the docker instance. I think this is the best answer for my use case since it just executes the script and nothing else. – pizzae May 26 '20 at 08:22
  • 1
    Based on this answer I expected this to work as well, and it did. Thanks. `echo SELECT * FROM table; | docker exec -i postgresdb psql -U user -d db` – bearcat Jul 16 '20 at 01:23
  • Seems to work only when psql is run on the db in the docker container. Won't run on a "remote" db. Mentioned this because I only want to use the psql command of the docker container to run an SQL on another db. – ka3ak Jul 29 '20 at 11:16
  • 2
    Ok. Figured out how to do it: echo "select count(*) from ..." | docker exec -i postgres-12.3 sh -c "export PGPASSWORD= && psql -U -h -p 5432 -d " Thanks for the answer. It helped. – ka3ak Jul 29 '20 at 11:23
7

With the official Docker image of PostgreSQL, code_monk's answer does not work for me. I arrive to this solution:

docker exec -it <container-name> psql -U <DB_USER> -d <DB_NAME> -f /file.sql
GuanacoBE
  • 442
  • 1
  • 7
  • 12
  • is the /file.sql inside the container or is it on the host, i tried this but it says file not found im wondering if its looking for the file in the container instead of the host? – Max Carroll Sep 27 '19 at 14:28
  • 5
    The file must be in the container. You can copy it with `docker cp` – GuanacoBE Sep 27 '19 at 14:40
1
#!/bin/bash
set -e

FUN_SQL2="
CREATE OR REPLACE FUNCTION insert_event(text)
  RETURNS integer AS
\\\\$BODY\\\\$
DECLARE ret INTEGER;
begin
select $1 into ret;
RETURN ret;

end;
\\\\$BODY\\\\$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
"

# add function to DB
docker exec -it db_container sh -c "psql -U postgres -d db_name -c \"$FUN_SQL2\" ";
i01573
  • 75
  • 2
  • 11
Mariusz Szot
  • 19
  • 2
1

Just in case, if you also want to ssh, here is one liner with script/sql

#!/bin/bash

ssh -tt -i .ssh/local.pem username@remotehost docker exec -ti docker-container /usr/bin/psql postgresql://user:password@dbhost:port/dbname -f sqlscript_on_docker

ssh -tt -i .ssh/local.pem username@remotehost docker exec -ti docker-container /usr/bin/psql postgresql://user:password@dbhost:port/dbname -c \"select \* from table\”

kc12
  • 11
  • 1