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
COPYcommand 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
COPYCommand 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
COPYCommand
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;
In [ ]: