Execute SQL Scripts¶
Let us understand how to execute SQL Scripts using psql. We will create new database and then run the scripts which are cloned from GitHub.
Clone the GitHub repository on to the host.
Copy the folder which contain scripts to itv_pg container under root folder /.
Make sure retail_db database and retail_user user are created.
Run the appropriate scripts to create tables as well as to insert data.
Validate that tables are created and data is inserted by running simple queries.
Clone Repository
# Make sure you are in the home directory in the host (not in the container)
cd # to be in home directory
git clone https://www.github.com/dgadiraju/retail_db.git
Copy Script and Validate
docker container cp retail_db itv_pg:/
docker exec -it itv_pg ls -ltr /retail_db
docker exec -it itv_pg psql -U postgres
Create Database and User for retail_db
docker exec -it itv_pg psql -U postgres
CREATE DATABASE retail_db;
CREATE USER retail_user WITH ENCRYPTED PASSWORD 'retail_password';
GRANT ALL ON DATABASE retail_db TO retail_user;
Create tables and copy data
We will be running script to create tables and copy data.
docker cp ~/retail_db itv_pg:/
docker exec -it itv_pg psql -U retail_user -d retail_db -W
\i /retail_db/create_db_tables_pg.sql
\i /retail_db/load_db_tables_pg.sql
Validate - Run Queries
Make sure you are in right database and run these queries.
docker exec -it itv_pg psql -U retail_user -d retail_db -W
\d
\d orders
SELECT * FROM orders LIMIT 10;
SELECT count(1) FROM orders;