[PostgreSQL] 유용한 명령어 모음 Part 2

본 포스팅은 PostgreSQL의 유용한 명령어에 대해 가이드하겠습니다.

---

최근 Cloud 환경으로 넘어 오면서 급격하게 OpenSource Software를 사용하는 빈도가 늘어나고 있습니다. Standalone 환경에서 ScaleOut, ScaleIn이 유동적으로 이루어 질수 있도록 기반을 잡고 있고, PostgreSQL이 앞으로 DB 시장에 어떠한 역할을 할지 귀추가 주목됩니다.

 

query 실행시간 확인 방법

- query가 수행하는 시간을 확인하는 방법입니다.

\timing postgreSQL 명령어를 통해 확인할 수 있습니다.

 

postgres=# \timing
Timing is on.
postgres=# select * from t1;
 c1
----
(0 rows)
Time: 0.409 ms
postgres=# \timing
Timing is off.
postgres=# select * from t1;
 c1
----
(0 rows)
postgres=#

위와 같이 \timing으로 query 실행 시간을 켜거나 없앨 수 있습니다.

 

 

postgreSQL을 하나의 pc에서 여러개 기동하기

종종 하나의 machine 에서 여러개의 PostgreSQL 을 사용해야 할 때가 있습니다.

machine 을 여러 사용자가 사용할 수도 있고, 혹은 개인적으로 여러개의 PostgreSQL 을 띄우고 이것저것 작업을 할 수도 있기 때문입니다.

아래 2가지 조건만 만족하면 N개의 PostgreSQL 을 실행 시킬 수 있습니다.
 

1. data 가 달라야 한다.
2. port 가 달라야 한다.

 

data 가 달라야 한다는 말은, "pg_ctl start -D" 옵션에 넣는 경로가 달라야 한다는 말입니다.
당연히, 하나의 datafile에 여러개의 process 가 작업을 하면 안되겠죠!

port 가 달라야 한다는 말은, tcp listen port 가 달라야 한다는 말입니다. 하나의 Server에서 동일한 포트를 2개이상 띄울수는 없으니까요.

자 그럼 직접 생성해 보겠습니다.

기존에 있던 data 말고, 새로운 data 를 생성합니다.

% initdb -D [NEW_DIRECTORY] -U [USER_NAME]

[postgres@NRSON 10]$ initdb -D /home/postgres/PostgreSQL/10/data2 -U postgres
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "ko_KR.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "ko_KR.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory /home/postgres/PostgreSQL/10/data2 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
    pg_ctl -D /home/postgres/PostgreSQL/10/data2 -l logfile start
[postgres@NRSON 10]$

 

port 를 변경합니다.

[postgres@NRSON 10]$ vi /home/postgres/PostgreSQL/10/data2/postgresql.conf

# - Connection Settings -
listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 15432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

앞에 주석 # 을 삭제한 후, 5432 말고 다른 port 를 입력합니다.

 

서버를 시작한다.

[postgres@NRSON 10]$ pg_ctl start -D [NEW_DIRECTORY]

[postgres@NRSON data2]$ pg_ctl start -D /home/postgres/PostgreSQL/10/data2
waiting for server to start....2018-04-05 14:58:41.093 KST [28187] LOG:  listening on IPv6 address "::1", port 15432
2018-04-05 14:58:41.093 KST [28187] LOG:  listening on IPv4 address "127.0.0.1", port 15432
2018-04-05 14:58:41.128 KST [28187] LOG:  listening on Unix socket "/tmp/.s.PGSQL.15432"
2018-04-05 14:58:41.244 KST [28188] LOG:  database system was shut down at 2018-04-05 14:52:38 KST
2018-04-05 14:58:41.279 KST [28187] LOG:  database system is ready to accept connections
 done
server started
[postgres@NRSON data2]$

 

자 그럼 새로운 서버로 접속해 보겠습니다.
    [postgres@NRSON data2]$ psql -p [NEW_PORT]

[postgres@NRSON data2]$ psql -p 15432
psql.bin (10.3)
Type "help" for help.
postgres=# \du
                                   List of roles
 Role name |                         Attributes                                        | Member of
-----------+------------------------------------------------------------+-----------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

\du의 경우 현재 postgresDatabase의 DB User에 대한 정보를 알려줍니다.

위와 같은 방식을 사용하면, N개의 PostgreSQL 을 실행 시킬 수 있습니다.

 

psql 접속 시 자동으로 query 실행하는 방법

PostgreSQL 에 psql 을 통해 접속할때, 원하는 쿼리(혹은 명령어) query(or command) 를 자동으로 실행시킬 수 있습니다.

~/.psqlrc 파일에 query(or command) 를 추가하면 됩니다. (만약 존재하지 않다면, 새로 생성해 주면 된다.)

 

상단에서 확인했던 timing이라는 postgreSQL Command를 반영해 보도록 하겠습니다.

~/.psqlrc

[postgres@NRSON ~]$ cat .psqlrc
\timing
[postgres@NRSON ~]$

 

위와 같이 작성 후 psql을 수행해 보겠습니다.

[postgres@NRSON ~]$ psql
Password:
Timing is on.
psql.bin (10.3)
Type "help" for help.
postgres=# select * from t1;
 c1
----
(0 rows)
Time: 0.774 ms
postgres=#

 

위와 같이 별도의 입력 없이도 \timing command가 수행되는 것을 볼수 있습니다.

이 텍스트 파일에 실행하고 싶은 query(or command) 를 기록하면 귀차니즘을 줄일 수 있을지 않을까 싶네요.

 

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 방식을 구축하는 과정을 기술해 보도록 하겠습니다.

 

a. 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 개수를 의미한다.
nara0617        10225     1  0 11:23 pts/1    00:00:00 /home/nara0617/postgresql/bin/postgres
nara0617        10227 10225  0 11:23 ?        00:00:00 postgres: checkpointer process
nara0617        10228 10225  0 11:23 ?        00:00:00 postgres: writer process
nara0617        10229 10225  0 11:23 ?        00:00:00 postgres: wal writer process
nara0617        10230 10225  0 11:23 ?        00:00:00 postgres: autovacuum launcher process
nara0617        10231 10225  0 11:23 ?        00:00:00 postgres: stats collector process
nara0617        10243 10225  0 11:23 ?        00:00:00 postgres: wal sender process nara0617 10.0.2.2(58506) streaming 0/F60001E8

 

wal_keep_segments = 32
master server 에 보관할 WAL 파일의 수를 결정하는 항목이다.
default 값은 0 으로 되어 있다.
WAL 파일의 갯수가 32개가 되었을 때, 33번째 파일이 생성되는것이 아니라, 1번째 WAL 파일부터 다시 overwrite 하게 된다.
※ max_wal_senders, wal_keep_segments 는 상황에 맞춰 설정하면 된다.

 

b. master server pg_hba.conf 파일을 편집합니다.

nara0617 사용자가 접속할 수 있게 해줍니다. 주석표시로 되어 있는데, 주석표시를 제거해 주면 됩니다.

host    replication     nara0617        0.0.0.0/0            trust

 

c. master server postgresql 을 restart 합니다.
변경된 설정 파일을 적용하기 위해서는, postgresql 을 restart 해야 합니다.

 

d. stand-by server postgresql.conf 파일을 백업해 놓는다.
다음으로 postgresql data 폴더 안에 있는 모든 파일을 삭제해야 합니다.
연동 시점에 master server postgresql data 를 가져와야 하기 때문입니다.
(비우지 않으면 다음 단계에서 해당 디렉토리가 비어있지 않다는 에러 메세지 발생)

 

e. master server postgresql data 를 stand-by server postgresql 에 싱크를 맞춥니다.
현재의 data 상태를 맞춰야 그 다음부터는 log 를 통해 data 를 동기화할 수 있기 때문에 먼저 datafile의 싱크를 맞추어 줘야 합니다.
postgresql/bin/pg_basebackup 프로그램을 사용하면 data 를 넣을 수 있습니다다.
(running 중인 postgresql data 를 backup 해 주는 역할)

 

stand-by server 에 접속
shell 에서 아래 명령어를 수행한다.
[postgres@NRSON ~]$ pg_basebackup -h MASTER_IP -D /home/postgresql/data -v -P -X stream
-h 옵션에는 master server ip address 를 입력한다.
-D 옵션에는 stand-by server postgresql data 를 저장할 디렉토리를 입력한다.

해당 디렉토리는 비어 있는 디렉토리여야 한다.
master server postgresql data 를 통채로 가져올 것이기 때문이다.
-v 옵션은 부가적인 정보를 표기해 준다.
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
transaction log start point: 0/F7000028 on timeline 1
pg_basebackup: starting background WAL receiver
transaction log end point: 0/F7000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
-P 옵션은 전송 진행 상황을 표기해 준다.
2569818/2569818 kB (100%), 1/1 tablespace
-X 옵션은 WAL 도 같이 backup 하라는 옵션이다. 

 

stream 방식은 백업이 생성되는 동안 트랜잭션 로그도 같이 한다는 의미입니다다. 이렇게 하면 서버에 대한 두 번째 연결이 열리고 백업을 실행하는 동안 트랜잭션 로그 스트리밍이 병렬로 시작됩니다. 따라서 max_wal_senders 매개 변수로 구성된 두 개의 연결을 사용하게 됩니다.
fetch 방식은 트랜잭션 로그 파일은 백업이 끝날 때 수집되며, wal_keep_segments 매개 변수를 높게 설정해야 백업이 끝나기 전에 로그가 제거되지 않습니다.

 

6. 백업해 놓았던, stand-by server postgresql.conf 파일을 postgresql data 디렉토리에 overwrite 합니다.
postgresql data 디렉토리에 있는 postgresql.conf 파일은 master server postgresql.conf 파일이기 때문입니다.

 

7. stand-by server postgresql.conf 파일을 편집한다.
stand-by 기능을 활성화 시키기 위해 아래와 같이 설정 파일을 수정합니다.

hot_standby = on

 

 

8. postgresql.conf 파일과 같은 경로에 recovery.conf 파일을 새롭게 생성합니다.
아래 내용을 입력합니다.

 

standby_mode = on
primary_conninfo = 'host=MASTER_IP port=5432 user=nara0617 password=password'

 

위는 stand-by 기능을 활성화 시킵니다.
primary_conninfo 에 master server postgresql 정보를 입력하고, 이 정보를 이용해서 master server postgresql 에 접속해서 실시간으로 WAL 내용을 전달 받습니다.

 

9. master server 에 보면 wal sender process 가 생성된것을 확인할 수 있다.

 

nara0617  10225     1  0 11:23 pts/1    00:00:00 /home/nara0617/postgresql/bin/postgres
nara0617  10227 10225  0 11:23 ?        00:00:00 postgres: checkpointer process
nara0617  10228 10225  0 11:23 ?        00:00:00 postgres: writer process
nara0617  10229 10225  0 11:23 ?        00:00:00 postgres: wal writer process
nara0617  10230 10225  0 11:23 ?        00:00:00 postgres: autovacuum launcher process
nara0617  10231 10225  0 11:23 ?        00:00:00 postgres: stats collector process
nara0617  10243 10225  0 11:23 ?        00:00:00 postgres: wal sender process nara0617 10.0.2.2(58506) streaming 0/F60001E8

stand-by server 에 보면 wal receiver process 가 생성된것을 확인할 수 있다. 

nara0617  21547     1  0 11:23 pts/2    00:00:00 /home/nara0617/postgresql/bin/postgres
nara0617  21548 21547  0 11:23 ?        00:00:00 postgres: startup process   recovering 0000000100000000000000F6
nara0617  21549 21547  0 11:23 ?        00:00:00 postgres: checkpointer process
nara0617  21550 21547  0 11:23 ?        00:00:00 postgres: writer process
nara0617  21551 21547  0 11:23 ?        00:00:00 postgres: stats collector process
nara0617  21552 21547  0 11:23 ?        00:00:00 postgres: wal receiver process   streaming 0/F60001E8

 

10. master server postgresql 에 data 변경을 하고, stand-by server postgresql 에 변경사항이 잘 적용되는지 확인한다.

오늘은 여기까지 입니다.

다음시간에 보다 고급진 내용으로 찾아 올께요.

감사합니다.