본 포스팅은 PostgreSQL의 다양한 명령어에 대해 가이드하겠습니다.
최근 Cloud 환경으로 넘어 오면서 급격하게 OpenSource Software를 사용하는 빈도가 늘어나고 있습니다. Standalone 환경에서 ScaleOut ScaleIn이 유동적으로 이루어 질수 있도록 기반을 잡고 있고 postgreSQL이 앞으로 DB 시장에 어떠한 역할을 할지 귀추가 주목됩니다.
먼저 psql 입니다.
psql은 postgresql의 SQL을 실행 할 수 있는 Command Line Tools 입니다. 최초 접속 시 기본 사용 중인 postgres Database로 접속하게 됩니다.
별도의 db를 생성하여 접속을 수행 할 경우 psql -d nrsonDB 으로 접속 가능합니다.
첫번째로 table 생성 및 확인방법에 대해 알아보겠습니다.
Table 생성 방법은 create table t1; 명령어로 수행이 가능합니다.
\d를 치면 Database에 추가 되어 있는 Table을 리스트를 확인할 수 있습니다.
postgresql 자체 Command에 대해 다른 포스팅에서 확인했었는데, 추가로 table / index에 대한 명령어를 살펴보도록 하겠습니다.
\d는 현재 접속한 database의 모든 테이블과 sequence를 보여줍니다.
\d 테이블명은 지정한 테이블의 속성과 이에 속한 index를 보여줍니다.
\di는 현재 접속한 database의 모든 index를 보여줍니다.
\di 인덱스명은 지정한 인덱스의 속성을 보여줍니다.
\i 파일명은 지정한 파일로부터 SQL 명령을 읽어 이를 수행합니다.
두번째로 pg_class 명령어입니다.
postgresql 내부 table을 포함한 모든 테이블을 확인하는 방법입니다.
select * from pg_class로 조회하면 Database 내부 테이블을 포함하여 모든 Table이 출력됩니다.
쿼리결과를 보기좋게 세로로 출력할 수 있는 postgreSQL Command 중 \x로 Sort 방법을 변경합니다.
세번째로 postgreSQL DB에 외부에서 접속하는 방법에 대해 알아보겠습니다.
지금까지는 DATABASE Server에 직접 접속하여 명령어를 수행하였으나, 외부 Client PC에서 접속하여 사용하는 것이 일반적입니다.
특히 개발자가 많아 질 경우 필수적인 사항이라고 할 수 있습니다.
이에 외부 Client PC에서 접속하는 방법에 대해 알아보도록 하겠습니다.
외부 사용자가 접속하기 위해서는 몇가지 postgreSQL 환경 파일을 수정해 주어야 합니다.
먼저 data directory 하위의 ph_hba.conf 파일입니다. pg_hba.conf 파일은 허용할 ip 대역을 설정합니다. 기존 127.0.0.1(localhost)를 0.0.0.0 모든대역으로 변경해줍니다.
다음으로 data directory 하위의 postgresql.conf 파일입니다. 해당 설정의 listen_addresses를 '*'로 수정합니다. 버전에 따라 다르지만 postgreSQL 10.3버전의 경우 DEFAULT로 '*'로 설정되어 있습니다.
이후 pg_ctl로 재기동을 수행합니다.
본인의 PC에서 PostgreSQL DB Server로 psql을 붙기 위해서 위와 같은 작업을 수행하였습니다.
실제 접속하기 위해 psql.ext -h db_ip -p db_port -U id -d postgres로 입력하면 정상적으로 프롬프트가 변경되며 접속되는 것을 확인할 수 있습니다.
네번째로 postgreSQL extension 사용법에 대해 알아보겠습니다.
postgreSQL에서는 다양한 extension 기능들을 제공해 주고 있습니다. 이밖에도 자신이 원하는 extension 기능들을 손쉽게 추가해서 사용할 수 있습니다. 이미 유명한 ph_hint_plan 또는 pgpool등이 바로 그것입니다.
다섯번째로 query 실행시간 확인 방법입니다.
\timing postgreSQL 명령어를 통해 확인할 수 있습니다.
매 Query가 수행 될때 마다 Query가 Response를 주는데까지 걸리는 시간을 출력해 줍니다.
\timing 다시한번 입력함으로써 query 실행 시간을 켜거나 없앨 수 있습니다.
여섯번째로 postgreSQL을 하나의 pc에서 여러개 기동하는 방법입니다.
종종 하나의 machine 에서 여러개의 PostgreSQL 을 사용해야 할 때가 있습니다.
machine 을 여러 사용자가 사용할 수도 있고, 혹은 개인적으로 여러개의 PostgreSQL 을 띄우고 이것저것 작업을 할 수도 있기 때문입니다.
아래 2가지 조건만 만족하면 N개의 PostgreSQL 을 실행 시킬 수 있습니다.
먼저 data Directory가 달라야 합니다. 또한 postgreSQL 기동 Port가 달라야 합니다.
data 가 달라야 한다는 말은, "pg_ctl start -D" 옵션에 넣는 경로가 달라야 한다는 말입니다.
당연히, 하나의 datafile에 여러개의 process 가 작업을 해서는 안되기 때문입니다.
port 가 달라야 한다는 말은, tcp listen port 가 달라야 한다는 말입니다. 하나의 Server에서 동일한 포트를 2개이상 띄울수는 없기 때문에 Port가 변경되어야 합니다.
기존에 있던 data 말고, 새로운 data 를 생성합니다.
datafile은 initdb -D [NEW_DIRECTORY] -U [USER_NAME] 로 생성할 수 있습니다.
다음으로 port를 변경합니다.
Port는 위에서 생성한 data Directory 하위의 postgresql.conf 파일을 통해 수정이 가능합니다. port를 default 5432에서 15432로 변경합니다.
Database 서버를 시작한다. 방금전 생성한 Datafile를 -D로 잡고 기동을 수행합니다.
위와 같은 방식을 사용하면, N개의 PostgreSQL 을 실행 시킬 수 있습니다.
일곱번째로 psql 접속 시 자동으로 query 실행하는 방법입니다.
PostgreSQL 에 psql 을 통해 접속할때, 원하는 쿼리(혹은 명령어) query(or command) 를 자동으로 실행시킬 수 있습니다.
~/.psqlrc 파일에 query(or command) 를 추가하면 됩니다. (만약 존재하지 않다면, 새로 생성해 주면 됩니다.)
상단에서 확인했던 timing이라는 postgreSQL Command를 ~/.psqlrc에 기록하고 postgresql을 기동하면 timing을 별도록 치지 않고도 Timing is on 상태로 자동 변경됩니다.
이 텍스트 파일에 실행하고 싶은 query(or command) 를 기록하면 모든 Datafile에 공통적으로 반영해야 하는 정보라든지, 반복되는 귀찮은 명령어등을 실행하는데 활용할 수 있을 것으로 보입니다.
여덟번째로 postgreSQL Replication입니다.
서버 1대로 DB 를 운영 중일때, 만약 서버에 문제가 생기면 서비스를 정작적으로 수행할 수 없게 됩니다. 이럴 경우를 대비해서 예비 서버를 준비해 놓게되고, 문제 발생 시, 예비 서버를 사용해서 서비스를 지속적으로 수행할 수 있도록 해야 합니다.
그럴려면 메인 서버에 갱신되는 DB 의 내용을 예비 서버에도 동일하게 유지시켜 줘야 하는데, 이럴 때 구축하는 것을 replication 이라고 합니다.
replication 기능을 내부적으로 가지고 있는 DB 도 있고, 가지고 있지 않은 DB 도 있습니다. 다행히도 PostgreSQL에도 replication 이라는 기능을 내부적으로 가지고 있습니다.
postgresql replication 을 구성하는 방식에는 Log-Shipping, Streaming 2가지 방식이 있습니다. Log-Shipping 방식은 pg_xlog 디렉토리 안의 WAL 파일 자체를 전달하는 방식이고, Streaming 방식은 로그 내용을 전달하는 방식입니다.
postgresql replication Streaming 방식을 구축하는 과정을 기술해 보도록 하겠습니다.
먼저 master server postgresql.conf 파일을 편집합니다.
자신에게 맞는 편한 editor 를 사용해서 편집하면 됩니다. postgresql.conf 에는 여러가지 환경설정 값을 수정할 수 있습니다.
먼저 listen_addresses = '*'를 설정합니다.
접속을 허용하는 ip 주소를 입력하는 항목입니다.
default 값은 'localhost' 로 되어 있습니다.
'*' 의 의미는 외부의 모든 ip 주소의 접속을 허용하겠다는 의미입니다.
인증,권한 관리는 pg_hba.conf 파일에서 진행하면 되므로 '*' 를 입력합니다.
다음으로 wal_level = replica를 설정합니다.
WAL 에 기록되는 정보의 양을 결정하는 항목입니다.
minimal, replica, logical 중 1가지를 선택할 수 있습니다.
default 값은 minimal 로 되어 있습니다.
minimal 은 충돌 또는 즉시 셧다운으로부터 복구하기 위해 필요한 정보만 기록하는 것입니다.
replica 은 WAL 아카이브에 필요한 로깅과 대기 서버에서 읽기 전용 쿼리에 필요한 정보를 추가합니다.
logical은 논리적 디코딩을 지원하는 데 필요한 정보를 추가합니다.
9.6 이전 버전에서는 archive 와 hot_standby 옵션이 있었습니다.
지금도 설정 가능하만, relica 와 동일하게 취급됩니다.
다음으로 max_wal_senders = 2를 설정합니다.
WAL 파일을 전송할 수 있는 최대 서버수를 결정하는 항목입니다.
default 값은 0 으로 되어 있습니다.
master server 에서 postgresql 을 실행하면 생성되는 process 들 중에서, WAL sender 역할을 하는 process 의 갯수를 의미합니다.
replication 이 실행되면 sender process 가 실행되어 있는걸 확인할 수 있습니다. 이때 sender process 개수를 의미합니다.
그 다음 wal_keep_segments = 32를 설정합니다.
master server 에 보관할 WAL 파일의 수를 결정하는 항목입니다.
default 값은 0 으로 되어 있습니다.
WAL 파일의 갯수가 32개가 되었을 때, 33번째 파일이 생성되는것이 아니라, 1번째 WAL 파일부터 다시 overwrite 하게 됩니다.
max_wal_senders, wal_keep_segments 는 상황에 맞춰 설정하면 됩니다.
다음으로 master server pg_hba.conf 파일을 편집합니다.
nara0617 사용자가 접속할 수 있게 해줍니다. 주석표시로 되어 있는데, 주석표시를 제거해 주면 됩니다.
그 다음 단계로 master server postgresql 을 restart 합니다.
변경된 설정 파일을 적용하기 위해서는, postgresql 을 restart 해야 합니다.
작업을 위한 stand-by server postgresql.conf 파일을 백업해 놓습니다.
다음으로 postgresql data 폴더 안에 있는 모든 파일을 삭제해야 합니다.
연동 시점에 master server postgresql data 를 가져와야 하기 때문입니다.
다음으로 master server postgresql data 를 stand-by server postgresql 에 싱크를 맞춥니다.
현재의 data 상태를 맞춰야 그 다음부터는 log 를 통해 data 를 동기화할 수 있기 때문에 먼저 datafile의 싱크를 맞추어 줘야 합니다.
postgresql/bin/pg_basebackup 프로그램을 사용하면 data 를 넣을 수 있습니다다. Running 중인 postgresql data 를 backup 해 주는 역할입니다.
stream 방식은 백업이 생성되는 동안 트랜잭션 로그도 같이 한다는 의미입니다다. 이렇게 하면 서버에 대한 두 번째 연결이 열리고 백업을 실행하는 동안 트랜잭션 로그 스트리밍이 병렬로 시작됩니다. 따라서 max_wal_senders 매개 변수로 구성된 두 개의 연결을 사용하게 됩니다.
fetch 방식은 트랜잭션 로그 파일은 백업이 끝날 때 수집되며, wal_keep_segments 매개 변수를 높게 설정해야 백업이 끝나기 전에 로그가 제거되지 않습니다.
백업해 놓았던, stand-by server postgresql.conf 파일을 postgresql data 디렉토리에 overwrite 합니다.
postgresql data 디렉토리에 있는 postgresql.conf 파일은 master server postgresql.conf 파일이기 때문입니다.
다음으로 stand-by server postgresql.conf 파일을 편집한다.
stand-by 기능을 활성화 시키기 위해 hot_standby를 on으로 설정 파일을 수정합니다.
다음단계로 postgresql.conf 파일과 같은 경로에 recovery.conf 파일을 새롭게 생성합니다.
본 파일에는 primary Server의 정보인 IP, Port, ID, Password를 담게 됩니다.
primary_conninfo 에 master server postgresql 정보를 입력하고, 이 정보를 이용해서 master server postgresql 에 접속해서 실시간으로 WAL 내용을 전달 받습니다.
master server process를 확인해 보면 wal sender process 가 생성됩니다.
stand-by server process를 확인해 보면 wal receiver process 가 생성됩니다.
마지막 단계로 master server postgresql 에 data 변경을 하고, stand-by server postgresql 에 변경사항이 잘 적용되는지 확인합니다.
다양한 PostgreSQL 명령어를 활용하여 유용하게 사용을 부탁드립니다.
다음시간에 보다 고급진 내용으로 찾아 오겠습니다.
고맙습니다.
