PostgreSQL의 psql 사용법

오픈소스 데이터베이스인 PostgreSQL의 psql을 사용하는 방법에 대해 정리한다.

 

PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.

Docker로 PostgreSQL 사용하기

 

PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.

공식 PostgreSQL Docker 이미지에 한글 적용하기

 

GitHub과 DockerHub 사이트는 다음과 같다.

• DockerHub - https://hub.docker.com/_/postgres
• GitHub - https://github.com/docker-library/postgres

 

PostgreSQL은 다음과 같은 구조를 가지고 있다.

각 기능은 다음과 같다.

• Users/Groups - 사용자 정보 관리
• Databases - 실제 레코드가 저장되는 Database 관리
• Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
• Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
• Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
• Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

 

먼저 psql 사용법에 대해 알아보고, 각각의 기능들에 대해 다룬다.

 

 

psql 사용하기 

PostgreSQL 데이터베이스에 접속하여 SQL 쿼리를 실행하기 위해서는 PostgreSQL의 쉘 (Shell)을 사용해야 한다.

파이썬을 사용하여 자동화를 구현하는 경우 주로 다음 목적으로 사용한다.

• 자동화 전에 생성할 Database와 Table이 존재하는지 확인
• 장애가 발생한 경우 이상 여부 확인
• 사용자 추가, 삭제 등 관리 목적
• 수작업으로 쿼리 작업을 수행해야 하는 경우 (발생 빈도에 따라 자동화 권장)

 

psql 실행

Docker 컨테이너 내부로 진입해서 psql을 실행하는 방법은 아래 포스팅의 PostgreSQL 실행 확인 항목을 참고한다.

Docker로 PostgreSQL 사용하기

 

도움말 출력

psql 도움말

다음 명령어를 사용하면 psql 사용법을 확인할 수 있다.

postgres=# \?

 

다음과 같이 도움말을 확인할 수 있다.

 

SQL 도움말

다음 명령어를 사용하면 SQL 사용법을 확인할 수 있다.

postgres=# \h

 

다음과 같이 도움말을 확인할 수 있다.

 

특정 SQL 쿼리에 대한 사용법을 확인하고자 하는 경우 다음과 같이 입력한다.

# select 쿼리 사용법을 확인하고 싶은 경우

postgres=# \h select

 

다음과 같이 SELECT 쿼리 사용법을 확인할 수 있다.

 

Database와 Table의 정보 확인

Database 전체 목록 확인

다음 명령어를 사용하면 Database 목록을 확인할 수 있다.

postgres=# \l

 

다음과 같이 Database 목록을 확인할 수 있다.

 

테이블 목록 확인

다음 명령어를 사용하면 특정 데이터베이스 내의 테이블 목록을 확인할 수 있다.

# 테이블 목록 확인
# \d 대신 \dt도 사용 가능
postgres=# \d

# 테이블의 상세 정보 확인
postgres=# \d+

 

현재 테이블이 정의 되지 않아 다음과 같이 출력된다.

 

만약 테이블이 정의되어 있다면 다음과 같이 출력된다.

 

System Table 확인

PostgreSQL 내부의 System Table 확인은 다음 명령어를 사용한다.

# 데이터베이스 내부의 System Table 확인
postgres=# \dS

 

다음과 같이 출력된다.

 

Schema 확인

\dS 명령어를 수행하면 Schema 컬럼을 확인할 수 있는데, MySQL에는 없는 개념이다.

Database는 RDBMS 내부에서 의미하는 데이터베이스를 의미하며, 물리적으로 구분 된 공간이다.

Schema 컬럼은 특정 Database 내부에서 데이터베이스를 논리적으로 구분한 것이다.

 

그림으로 표현하면 다음과 같다. (출처: https://valuefactory.tistory.com/491)

 

PostgreSQL에서 기본적으로 public 스키마가 존재하며, 임의의 스키마를 생성할 수 있다.

Database 하위에 다수의 Table이 존재할 수 있는데, 목적이나 특성에 따라 구분할 수 있는 폴더 개념이라 이해하면 좋다.

Schema 이름과 Table 이름은 Database 내부에서 고유해야 한다.

 

만약 mydb라는 프로젝트를 수행하는데, A와 B 어플리케이션이 같은 Database를 사용해야 하는 경우를 생각해보자.

• Database는 프로젝트명인 mydb로 생성한다.
• A 어플리케이션은 myschme라는 Schema의 mytable3 Table을 사용한다.
• B 어플리케이션은 alicek106이러는 Schema의 yourtable Table을 사용한다.
• C 어플리케이션은 mytable3과 yourtable을 Join하여 Public Schema의 mytable2 Table에 저장한다.

 

이렇게 논리적으로 구분하면 여러 사용자가 충돌 없이 하나의 데이터베이스를 사용할 수 있다고 한다.

 

다음 명령어를 사용하면 특정 데이터베이스 내의  Schema 정보를 확인할 수 있다.

# 데이터베이스 내부의 Schema 확인
postgres=# \dn

 

다음과 같이 출력된다.

 

View 확인

View는 접근이 허용 된 자료만 제한적으로 사용자에게 보여주기 위한 가상 Table이다.

가상 Table이기 때문에 논리적으로 구현 된 Table이며 주로 임시적인 용도로 사용한다.

조회 용도로 사용하며 삽입, 삭제, 갱신, 연상 등에 제약이 있다.

대부분의 RDBMS에서 지원한다.

 

다음 명령어를 사용하면 특정 데이터베이스 내의  View를 확인할 수 있다.

# 데이터베이스 내부의 View 확인
postgres=# \dv

 

현재 Relation이 정의 되지 않아 다음과 같이 출력된다.

 

만약 View가 정의되어 있다면 다음과 같이 출력된다. (출처: http://www.gurubee.net/lecture/2930)

 

 

다른 Database 사용하기

PostgreSQL 내부의 다른 Database를 사용하려는 경우 다음 명령어를 사용한다.

# 데이터베이스 내부의 다른 Database 사용
postgres=# \c Database이름

 

다음과 같이 출력된다.

 

psql 종료하기

psql을 종료하려는 경우 다음 명령어를 사용한다.

# psql 종료
postgres=# \q

 

다음과 같이 출력된다.