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:
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:
-- 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.