1. Introduction

The post describes how to create a docker container and fill a table with test data for further development.

2. Create a test instance via docker

First of all you need to install Docker. After successful installation, start docker and create a postgres instance:

2.1 Docker run

Docker run command to create and start any docker image:

docker run [OPTIONS] IMAGE [COMMAND] [ARG...]

Docker command to run the latest postgres database image: Optional: for permanent data storage locally you can create a folder and mount it.

mkdir ~/test-pg # create a new folder in your home directory to store the postgres data.
docker run --name test-pg -p 5432:5432 -v ~/test-pg:/var/lib/postgresql/data -e POSTGRES_PASSWORD=postgres -d postgres:latest

[OPTIONS]:
--name: Assign a name to the container - test-pg.
--publish / -p: Publish a container’s port(s) to the host. The syntax is [host_ip]:[host_port]:[container_port], while host_ip is optional. In the example we keep the default port for postgres 5432:5432.
--volume / -v: Bind mount a volume. (Optional, but keeps the data permanent).
--env / -e: Set environment variables. We only set the environment variable POSTGRES_PASSWORD, which sets the password for the PostgreSQL.
--detach / -d: Run container in background and print container ID.

IMAGE:
the image docker should run. We would like to run a postgres data base, so we choose postgres:latest. More information to the docker postgres image can be found here.

2.2 Docker exec

docker exec [OPTIONS] CONTAINER COMMAND [ARG...]

docker exec -i -t test-pg psql -U postgres

[OPTIONS]:
–interactive / -i: Keep STDIN open even if not attached
–tty / -t: Allocate a pseudo-TTY

CONTAINER:
the name we assigned to the container - test-pg

COMMAND:
psql -U postgres

3. Generate test data

Creata a table films which will be filled with test data.

CREATE TABLE films (
    code        serial,
    title_hash  varchar(40),
    date_prod   date,
    len         int CHECK(len BETWEEN 60 AND 180),
    genre       VARCHAR(6) NOT NULL CHECK (genre IN ('comedy', 'action')),
    CONSTRAINT code_title_hash PRIMARY KEY(code,title_hash)
);

Insert a time series with random data into the table films

INSERT INTO films(title_hash, date_prod, len, genre)
SELECT md5(RANDOM()::TEXT), 
       generate_series('2022-01-01'::date, '2100-05-01'::date, '1h'::interval), 
       (floor(random()*(180-60+1))+60), 
       CASE WHEN RANDOM() < 0.5 THEN 'comedy' ELSE 'action' END;