Goal

• Studying SQL for data analysis
• Install PostgreSQL docker for buildiing testing environment

---

Practice

Book for studying

Title	데이터 분석을 위한 SQL 레시피
Author	Nagato Kasaki, Naoto Tamiya
Translator	윤인성
Publisher	한빛미디어

• I’m gonna study SQL syntax and functions for data analysis
• I’m gonna try to load test data on PostgreSQL and execute queries in the book
• First of all, I have to install PostgreSQL for testing and I’ll build the environment by using docker image
• And docker is already installed in my system

1. Install

1.1. Check local docker images

$ docker image ls

1.2. Download docker image

• Search docker image on DockerHub

$ docker search [IMAGE]

• Download PostgreSQL image

  I downloaded 12.1 version

$ docker pull [IMAGE][{:TAG}]
$ docker image ls

2. Run PostgreSQL instance

2.1. Create data directory

• I’m gonna set data directory on my local directory not on docker image

$ mkdir [DATA_DIRECTORY]

2.2. Run instance

• Options
  • Environment variables

Variable	Comment
POSTGRES_PASSWORD	Sets the superuser password for PostgreSQL
POSTGRES_USER	Create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used
POSTGRES_DB	ㅇefine a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used
PGDATA	The default is /var/lib/postgresql/data, but if the data volume you’re using is a filesystem mountpoint (like with GCE persistent disks), Postgres initdb recommends a subdirectory (for example /var/lib/postgresql/data/pgdata ) be created to contain the data

• Run

$ docker run --name [CONTAINER_NAME] -e POSTGRES_PASSWORD=[MY_PASSWORD] -d postgres

• Check running process

$ docker ps

3. Connection Test

3.1. Connect PostgreSQL by DB tool

• I used DBeaver which is open-source database tool

3.2. Create table test

docker start -i -p 5432:5432/tcp postgres-da

---

References

• 데이터 분석을 위한 SQL 레시피 - 한빛미디어
• DockerHub PostgreSQL