Populating a PostgreSQL Database in Docker

When using Docker, Docker Compose, etc. I’ve found that trying to populate a database by mounting a volume containing the necessary .sql files doesn’t always work. If you’re using PostgreSQL and developing locally, here is a quick command to seed (re-seed) your database.

In your terminal:

1

cat ./path/to/data.sql | docker exec -i <docker service> psql -U <postgres username>

In this example SQL file, we’re going to create a DB called ‘orders’. We need to remove any existing instances of our database before populating.

At the top of your SQL file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- Make sure db exists
SELECT * from pg_database where datname = 'orders';

-- Prevent new connections

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'orders';
ALTER DATABASE orders CONNECTION LIMIT 1;

-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'orders';
DROP DATABASE IF EXISTS orders;

...

-- Create tables, etc.