How do I run a sql file of inserts through docker run?
DockerPsqlDocker RunDocker ExecDocker Problem Overview
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
?
Docker Solutions
Solution 1 - Docker
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
Solution 2 - Docker
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.
Solution 3 - Docker
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
Solution 4 - Docker
#!/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\" ";