[PostgreSQL] 기본 구조 및 psql 명령어

nooyji·2021년 8월 12일

db 엔진이 올라간 후, 인스턴스를 생성하면서 database가 생성되어 대부분 1 instance = 1 database 를 나타내는 오라클과 달리 postgresql에서의 인스턴스는 1 instance = 1 server를 의미하는 서버 단위의 개념이다.
oracle에서의 클러스터는 RAC 환경의 공유노드 개념으로 하나의 스토리지를 바라보는 여러대의 노드, 오라클 클러스터웨어인 grid instucture를 통한 active-active의 환경을 조성하고 클러스터웨어를 통해 이루어진 공유노드를 클러스터라고 한다.
postgresql에서의 클러스터는 psql DB 집합체를 의미한다. 처음 postgresql 설치 시 기본적으로 postgres database, template0, template1 3개의 database가 생성되는데, 이 db 집합체를 postgresql 클러스터라고 한다.

postgresql의 database는 여러개의 schema로 구성되어 있으며 schema는 object들의 논리적인 집합을 의미한다.
postgresql schema는 tables, views, sequences, synonyms, domains, functions 등의 object로 구성되며 이러한 database와 users/groups, tablespaces가 모여 cluster를 구성

default data directory 경로 $cd /var/lib/pgsql/ll/data
data directory 내 base 라는 디렉토리가 있으며, 이 밑으로 database가 각 디렉토리로 생성된다. 해당 디렉토리에 테이블이나 인덱스와 같은 오브젝트들이 파일 형식으로 저장된다.
postgresql은 tablespace를 지원하며, 테이블스페이스 생성을 위해 특정 디렉토리를 지정하고 테이블스페이스를 생성하면, 해당 경로가 pg_tblspc 디렉토리 밑에 심볼릭 링크로 걸리게 되고, 이 링크를 통해 디렉토리의 파일을 조회할 수 있다.
psql로 접속하여 pg_database를 통해 각 데이터베이스의 oid 값을 조회해 db 구조를 파악할 수 있다.
select datname, oid from pg_database;

DB 내의 물리적인 부분으로 DB object 내 실제 데이터를 저장하는 공간이다. 단지 데이터베이스 저장소 위치를 지정하며, 논리적인 데이터베이스 구조나 스키마를 지정하지 않는다. (동일 스키마 내의 다른 오브젝트는 서로 다른 테이블스페이스에 위치할 수 있다.)
postgresql에서 tablespace의 의미 또한 타 DB의 tablespace 역할과 동일하며 DBA가 database object가 저장된 파일 시스템 장소를 정의할 수 있도록 한다.

psql 접속
psql -U username -d database
psql 비밀번호 접속
PGPASSWORD=password psql -U username -d database
데이터베이스 목록
\l
데이터베이스 내 릴레이션 정보 확인
\d
데이터베이스 내 테이블 조회
\dt
스키마 조회
\dn
USER 조회 (각 user가 가진 role, 롤, 권한을 확인할 수 있다.)
\du
TABLESPACE 조회
\db
다른 데이터베이스 접속
\c {database_name}
psql 종료
\q
query 수정 및 실행
\e 명령어를 실행하면 psql.edit 메모장 파일이 열려 query를 수정하고 실행할 수 있다.
쿼리 입력 후, esc+:wq 로 빠져나오면 bash 창에 쿼리 결과가 표시
Variable (변수) 선언
\set {name} {value} 로 변수 사용 가능
\echo 명령어로 {name} 변수를 호출하면 변수에 저장된 값이 출력
Special Variable 선언
psql 에는 환경설정 셋팅을 변경할 수 있는 특별한 변수가 존재
변수 선언과 마찬가지로 \set {special_variable} {option} 을 통해 세팅 변경을 위한 변수 설정을 할 수 있다.