Loading Data – Docker

Let us understand how you can take care of loading data into Postgres Database running using Docker Container.

• Make sure database is created along with the user with right permissions. Also the user who want to use COPY command need to have pg_read_server_files role assigned.

  • Create file with sample data

  • Copy file into Docker container

  • Connect to Database

  • Create the table

  • Run COPY Command to copy the data.

Prepare Data

We need to create file with sample data and copy the files into the container.

• Sample File In this case data is added to users.csv under ~/sms_db.

user_first_name,user_last_name,user_email_id,user_role,created_dt
Gordan,Bradock,gbradock0@barnesandnoble.com,A,2020-01-10
Tobe,Lyness,tlyness1@paginegialle.it,U,2020-02-10
Addie,Mesias,amesias2@twitpic.com,U,2020-03-05
Corene,Kohrsen,ckohrsen3@buzzfeed.com,U,2020-04-15
Darill,Halsall,dhalsall4@intel.com,U,2020-10-10

• Copy data

docker cp ~/sms_db/users.csv itv_pg:/tmp

Create Database

Here are the steps to create database.

• Connect to database as super user postgres

docker exec -it itv_pg psql -U postgres

• Create the database with right permissions.

CREATE DATABASE itversity_sms_db;
CREATE USER itversity_sms_user WITH PASSWORD 'sms_password';
GRANT ALL ON DATABASE itversity_sms_db TO itversity_sms_user;
GRANT pg_read_server_files TO itversity_sms_user;

• Exit using q

Connect to Database

Use this command to connect to the newly created database.

psql -U itversity_sms_user 
  -h localhost 
  -p 5433 
  -d itversity_sms_db  
  -W 

Create Table

Here is the script to create the table.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_first_name VARCHAR(30) NOT NULL,
    user_last_name VARCHAR(30) NOT NULL,
    user_email_id VARCHAR(50) NOT NULL,
    user_email_validated BOOLEAN DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE
);

Load Data

Here are the steps to load and validate the data using psql.

• Load data using COPY Command

COPY users(user_first_name, user_last_name, 
    user_email_id, user_role, created_dt
) FROM '/tmp/users.csv'
DELIMITER ','
CSV HEADER;

• Validate by running queries

SELECT * FROM users;