내가 사용하고 있는 Confluence, Jira, Bitbucket의 MySQL을 PostgreSQL로 변경하기로 마음먹고 학습을 진행했다. 그간에 얻은 PostgreSQL의 설치, 설정, 튜닝, 데이터베이스와 사용자 생성, 백업 및 복구 등을 전반적으로 정리해 본다. 이 페이지에서는 설치, 설정, 튜닝에 대해서 다룬다. 나머지 내용은 PostgreSQL의 데이터베이스와 사용자 생성, 백업 및 복구로 이어진다. PostgreSQL을 처음 접하는 분들에게 도움이 되었으면 한다.
PostgreSQL 설치
PostgreSQL의 라이센스는 자유를 추구하는 데비안과 잘 맞는다. PostgreSQL은 데비안의 주요 패키지 중의 하나이기 때문에 다음과 같이 한 줄의 명령어로 설치한다.
# apt install postgresql postgresql-client
설치 후 PostgreSQL 관련 파일의 위치는 다음과 같다.
- 설정 파일: /etc/postgresql/[version]/[cluster]/
- 실행 파일: /usr/lib/postgresql/[version]
- 데이타 파일: /var/lib/postgresql/[version]/[cluster]
- 로그 파일: /var/log/postgresql 디렉토리 안의 postgresql-[version]-[cluster].log
내가 사용하는 데비안 9(stretch)에서 PostgreSQL 버전은 9.6이다. 앞으로 나올 파일의 경로는 9.6을 기준으로 서술한다. PostgreSQL은 클러스터로 데이터베이스를 관리한다. 간단하게 ‘클러스터 > 데이터베이스 > 테이블’의 순서로 그려볼 수 있다. 여러 개의 클러스터가 존재할 수 있고 한 클러스터 내에 여러 개의 데이터베이스를 가질 수 있다. 클러스터는 아마존 클라우드와 같이 대용량을 위한 것이다. 개인이라면 하나의 클러스터로도 충분할 것이다.
데비안은 pg_ctl을 pg_ctlcluster로 대체한다. pg_ctlcluster는 Perl 스크립트 파일이고 내부에서 pg_ctl을 사용한다. pg_ctlcluster는 PostgreSQL의 다양한 버전과 여러 클러스터를 효율적으로 관리하기 위한 것이다.
데비안에서 PostgreSQL의 init.d 시작 스크립트는 /etc/init.d/postgresql이다. 이 시작 스크립트 안에서 pg_ctlcluster를 호출한다. PostgreSQL의 시작과 종료는 systemctl을 사용하거나 다음과 같이 init.d 시작 스크립트를 실행할 수 있다.
# /etc/init.d/postgresql stop [ ok ] Stopping postgresql (via systemctl): postgresql.service. # /etc/init.d/postgresql start [ ok ] Starting postgresql (via systemctl): postgresql.service. # /etc/init.d/postgresql restart [ ok ] Restarting postgresql (via systemctl): postgresql.service. /etc/init.d/postgresql reload [ ok ] Reloading postgresql configuration (via systemctl): postgresql.service. # /etc/init.d/postgresql status ...
사용자 접근 설정
디폴트 데이타베이스와 사용자는 postgres이다. 데이타베이스 관리는 postgres 사용자로 하여야 한다. root 계정에서 다음의 명령어로 postgres 사용자로 변경한다.
# su -s /bin/bash postgres $ id uid=115(postgres) gid=124(postgres) groups=124(postgres),112(ssl-cert)
id 명령어로 현재의 사용자가 postgres인지 확인할 수 있다. postgres로 사용자 변경후 psql 클라이언트 프로그램을 실행한다.
$ psql
root에서 postgres로 사용자를 전환하는 su 명령어를 사용하지 않고 psql을 직접 실행하려면 다음과 같이 하여야 한다.
# psql -h localhost -U postgres -d postgres postgres 사용자의 암호:
postgres 사용자의 암호를 묻는 메시지가 나온다. 데비안에서는 디폴트로 postgres 사용자의 암호가 없다. <Ctrl + C> 키를 눌러 우선은 빠져나온다.
데비안의 PostgreSQL은 로컬 시스템에서 peer 인증(authentication)을 사용한다. peer 인증은 PostgreSQL 사용자와 동일한 이름을 가지는 로컬 계정으로 로컬 시스템에 로그인하면 암호 없이 데이터베이스를 사용할 수 있다. 이러한 설정은 /etc/postgresql/9.6/main/pg_hba.conf에서 확인할 수 있다.
# Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 ...
위 설정을 보면 로컬 시스템(127.0.0.1/32)에 로그인하지 않고(host) 접근하려면 암호를 사용해야 한다. METHOD가 md5인 경우 암호를 사용한다는 의미이다. 로컬 시스템에서 사용자가 아니라 응용프로그램이 PostgreSQL 데이터베이스에 접속할 때 적용될 것이다. 또는 위에서 보았듯 root 계정에서 PostgreSQL 사용자로 전환하는 su를 사용하지 않고 psql, pg_dump 등을 사용할 때 암호를 사용해야 한다.
PostgreSQL의 관리자 권한을 가지는 postgres 사용자에 암호를 설정하려면 psql 클라이언트에서 다음의 SQL문을 실행한다.
postgres=# ALTER USER postgres PASSWORD '원하는 암호 입력'; ALTER ROLE postgres=# \q
SQL 실행 후 ALTER ROLE 확인 메시지가 나오면 성공한 것이다. \q 명령어를 실행하여 psql을 종료한다.
root 계정으로 다음과 같이 psql을 다시 실행하고 설정한 암호를 입력하여 접속에 성공하는지 확인해 본다.
# psql -h localhost -U postgres -d postgres postgres 사용자의 암호: <설정한 암호 입력> psql (9.6.17) SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off) 도움말을 보려면 "help"를 입력하십시오. postgres=#
매번 암호를 입력하는 것은 귀찮은 작업이다. 특히 백업에 사용하는 pg_dump를 스크립트에서 사용할 때 암호가 노출된다. pg_dump를 한 번 실행해 보자.
# pg_dump -Fc -h localhost -U postgres -d postgres -f /root/postgres_`date +%F`.dump 암호:
스크립트에서 암호를 입력하지 않고 실행하려면 다음과 같이 해야 한다.
#!/bin/bash export PGPASSWORD="yourpassword" pg_dump -Fc -h localhost -U postgres -d postgres -f /root/postgres_`date +%F`.dump
이를 해결하기 위해 다음의 내용을 가지는 /root/.pgpass 파일을 생성한다.
localhost:5432:*:postgres:<관리자인 postgres의 암호를 입력> localhost:5432:*:*:<개발이나 응용프로그램에서 사용할 암호 입력>
위의 내용은 hostname:port:database:username:password를 의미한다. 별표 문자 *는 와일드카드로 ‘모든’을 뜻한다. 마지막의 password를 제외하고 별표 문자를 사용할 수 있다. 두 번째 줄에서 개발이나 다른 응용프로그램에서 사용할 데이터베이스의 암호를 따로 설정해 주었다. 일반적으로 개발이나 응용프로그램은 설정 파일에 암호를 저장하는 경우가 빈번하기 때문에 사용하는 데이터베이스의 암호가 노출되기 쉽다. 관리자 암호와는 다른 암호를 사용하여 관리자의 암호를 보호하기 위해 두 번째 줄을 추가한 것이다. root 외 다른 사용자가 .pgpass 파일 안의 내용을 보지 못하도록 다음의 명령어를 실행한다.
# chmod 600 /root/.pgpass
다시 root 계정으로 pg_dump와 psql을 실행하면 암호를 묻지 않는 것을 확인할 수 있다.
# pg_dump -Fc -h localhost -U postgres -d postgres -f /root/postgres_`date +%F`.dump # psql -h localhost -U postgres -d postgres psql (9.6.17) SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off) 도움말을 보려면 "help"를 입력하십시오. postgres=#
원격 접속을 위한 설정
시스템 외부에서 pgAdmin, DataGrip 등의 프로그램을 사용하여 원격으로 접속하려면 몇 가지 설정을 해야 한다. PostgreSQL은 기본값으로 포트 5432를 사용한다. 데비안에서 포트 5432는 디폴트로 로컬 주소(127.0.0.1)로 바인딩한다. 다음의 명령어로 확인할 수 있다.
# netstat -nlt | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
따라서 시스템 외부에서 접근할 수 없다. 시스템 외부에서 접근할 수 있도록 허용하려면 /etc/postgresql/9.6/main/postgresql.conf 파일에 다음의 내용을 설정한다.
listen_addresses = '*'
로컬 네트워크에 있는 컴퓨터가 접근할 수 있도록 허용하려면 /etc/postgresql/9.6/main/pg_hba.conf에 다음의 내용을 추가한다. 로컬 네트워크 대역의 주소(192.168.1.0/24)는 여러분에게 맞는 값을 넣어준다.
host all all 192.168.1.0/24 md5
모든 설정을 마쳤으면 PostgreSQL을 다시 시작한다.
# /etc/init.d/postgresql restart
PostgreSQL의 기본적인 튜닝
PostgreSQL은 오래된 시스템을 지원하기 위한 설정값을 디폴트로 가진다. 전문적인 튜닝은 아니지만 약간의 설정을 변경하는 것으로도 많은 성능 향상을 가져온다. 다음의 사이트에서 설정값에 대한 힌트를 얻을 수 있다.
운영체제의 종류, 메모리 크기 등을 선택해서 나온 설정값을 종합적으로 고려하여 /etc/postgresql/9.6/main/postgresql.conf 파일을 수정하고 PostgreSQL을 다시 시작한다. 내가 설정한 내용은 다음과 같다.
shared_buffers = 1GB work_mem = 64MB maintenance_work_mem = 256MB effective_io_concurrency = 2 wal_buffers = 16MB max_wal_size = 2GB min_wal_size = 512MB checkpoint_completion_target = 0.7 random_page_cost = 4 effective_cache_size = 3GB default_statistics_target = 100