Docker is a great tool for local development. Today we will look at how to bootstrap an instance of PostgreSQL with sample data to make development and testing a breeze. Files can be found here.
First create a new directory for our files:
mkdir postgres-bootstrap && cd postgres-bootstrap
Next we will start off by creating our schema.sql file. Let’s make a table for customers and one for orders:
CREATE USER bootstrap;
CREATE DATABASE bootstrap;
GRANT ALL PRIVILEGES ON DATABASE bootstrap TO bootstrap;
\c bootstrap;
CREATE TABLE customers (
created timestamp without time zone DEFAULT now(),
id SERIAL PRIMARY KEY,
email text,
first_name text,
last_name text
);
ALTER TABLE customers OWNER TO "bootstrap";
CREATE TABLE orders (
created timestamp without time zone DEFAULT now(),
id SERIAL PRIMARY KEY,
customer_id integer,
amount decimal
);
ALTER TABLE orders OWNER TO "bootstrap";
We want to fill the database with some mock data. Create two csv files customers.csv and orders.csv.
customers.csv:
id,email,first_name,last_name
1,bobby@gmail.com,Bob,Smith
2,rob@gmail.com,Robert,Johnson
3,tom@hotmail.com,Thomas,Zimmerman
orders.csv:
id,customer_id,amount
1,1,12.76
2,3,24.67
3,1,55.98
4,2,88.43
5,1,23.54
Now create a file named script.sh to copy the data from the csv files into the database.
#!/bin/bash
DATABASE_NAME="bootstrap"
ROLE_NAME="bootstrap"
for x in $(ls /tmp/data/customers.csv);
do psql -d "$DATABASE_NAME" -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -c "COPY customers(id,email,first_name,last_name) FROM '$x' DELIMITERS ',' CSV HEADER;"; done
echo "*** CUSTOMERS DATA IMPORT COMPLETE ***"
for x in $(ls /tmp/data/orders.csv);
do psql -d "$DATABASE_NAME" -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -c "COPY orders(id,customer_id,amount) FROM '$x' DELIMITERS ',' CSV HEADER;"; done
echo "*** ORDERS DATA IMPORT COMPLETE ***"
The next thing we need is our Dockerfile. Let’s start with the official postgres:9.6 image as our base. On startup the official image will run any .sh and .sql files we put into the /docker-entrypoint-initdb.d/ directory. We will also create the /tmp/data/ directory and add the csv files.
FROM postgres:9.6
RUN mkdir -p /tmp/data/
ADD *.csv /tmp/data/
ADD schema.sql /docker-entrypoint-initdb.d/
ADD script.sh /docker-entrypoint-initdb.d/
When the container starts schema.sql will run, then script.sh will run.
Build and start the container:
docker build -t bootstrap-postgres . && docker run -p 5432:5432 bootstrap-postgres
Run some queries!
bootstrap=> SELECT * FROM customers;
created | id | email | first_name | last_name
----------------------------+----+-----------------+------------+-----------
2018-06-16 23:09:11.822871 | 1 | bobby@gmail.com | Bob | Smith
2018-06-16 23:09:11.822871 | 2 | rob@gmail.com | Robert | Johnson
2018-06-16 23:09:11.822871 | 3 | tom@hotmail.com | Thomas | Zimmerman
bootstrap=> SELECT * FROM orders;
created | id | customer_id | amount
----------------------------+----+-------------+--------
2018-06-16 23:09:11.866757 | 1 | 1 | 12.76
2018-06-16 23:09:11.866757 | 2 | 3 | 24.67
2018-06-16 23:09:11.866757 | 3 | 1 | 55.98
2018-06-16 23:09:11.866757 | 4 | 2 | 88.43
2018-06-16 23:09:11.866757 | 5 | 1 | 23.54
Success! Our test data has been added on startup.
