Postgres 설치 및 사용 방법

설치 환경

운영체제 : Linux centos7.9 3.10.0-1160.36.2.el7.x86_64

DB 버전 : 11.9

 

특이 사항

• postgres 사용자 생성 후 설치(postgres 패키지 설치 시, 자동으로 시스템 계정의 postgres가 생성되나 추후 failover까지 고려하여 파일을 보내는 경우 등을 고려하면 생성하는 것이 편함, 필수사항 아님)
• 기본적으로 운영체제(Linux Centos 7.9가 설치 완료된 환경에서 시작

 

설치 방법

OS의 계정을 생성

#useradd postgres

#passwd postgres

postgres

 

만약 OS 계정 생성 전, 설치를 하여 postgres 유저가 있다면 삭제 후 생성

가급적 시스템 유저를 삭제하기 보단, 패키지를 삭제하고 OS postgres 유저를 생성 후 

패키지 재설치를 추천(시스템 유저 postgres가 소유하고 있던 디렉터리 전부 찾아서 소유권 변경해야 함)

#userdel -r postgres 혹은 

#yum -y remove postgresql11-server postgresql11-contrib

 

패키지 설치

• yum이 가능한 환경

repository RPM 설치

#yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

 

설치 후 확인

#ls -l /etc/yum.repos.d

 

postgres 설치

#yum install -y postgresql11-server postgresql11-contrib

 

yum repository를 수정하지 않고 설치 시, 설치가 되지 않거나

#yum -y install postgresql 실행 시 9.x 가 설치된다면 repository를 수정해야함(-U : 패키지를 업그레이드)

#rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

 

• 인터넷이 안되는 환경

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/postgresqldbserver11.group.html 에서 아래 4개를 받아서 리눅스 환경에 옮기기

postgresql11-11.9-1PGDG.rhel7.x86_64.rpm

postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm

postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm

postgresql11-server-11.9-1PGDG.rhel7.x86_64.rpm

 

중간에 의존성 오류 발생 시 발생한 의존성 패키지만 설치 후 다시 설치

 

설치 순서

postgresql11-libs -> postgresql11 -> postgresql11-server -> postgresql11-contrib 순으로 설치

# rpm -ivh postgresql11-libs-11.5-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql11-11.5-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql11-server-11.5-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql11-contrib-11.5-1PGDG.rhel7.x86_64.rpm

 

설치 패키지 확인

rpm -qa | grep postgresql

 

설치 패키지 위치 확인

rpm –qs postgresql11

 

기본 데이터베이스 생성 및 서비스 등록

 

Initdb 명령으로 기본 데이터베이스 설치 및 자동실행, postgres 시작

#/usr/pgsql-11/bin/postgresql-11-setup initdb

#systemctl enable postgresql-11

#systemctl start postgresql-11

 

postgres 시스템 유저가 있던 상황에서 삭제 후 OS의 postgres유저를 만들 경우

/var/run/postgresql의 디렉터리에 .s.PGSQL.5432.lock 파일을 생성할 수 없어 기동이 불가

해당 디렉터리 권한 변경하여 해결

#chown postgres:postgres /var/run/postgresql

 

/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql11/data start로는 가급적 실행하지 않길 권고함

systemd(시스템 데몬, 시스템 관리자)가 이것에 대해 알 수 없기 때문에 systemctl로 서버를 시작 및 중지할 수 없음, 따라서 항상 systemctl 사용을 권장하며, pg_ctl reload로 새로고침은 괜찮음

 

 

확인

#systemctl status postgresql-11 혹은 #ps -ef | grep postgres

 

계정, 그룹 생성 및 사용자 프로파일 설정

postgresql 접속

#su - postgres -c 'psql' 혹은  #su - postgres이후 $psql

 

 

postgres는 접속 시, OS 유저와 DB 유저, database의 이름이 동일하다면 옵션 없이 바로 접속 가능

원래는 $psql -U postgres -d postgres 하여 postgres유저가 postgres디비에 접속하는 형태로 접속해야 함

 

 

DB계정 패스워드 변경

postgres=# alter user postgres password 'psql'; 

postgres=# \password <role name>

 

 

OS에서 관리자 Password 변경(-c : shell 명령어를 실행)

#su - postgres -c 'psql'

 

 

롤 생성/삭제

postgres=# create role <지정할 role name> with superuser createdb createrole login password '<지정할 password>';

postgres=# drop role <role name>

 

 

OS에서 생성/삭제

#sudo -u postgres createuser –interactive

#sudo –u postgres dropuser <role name>

 

 

테이블 스페이스 생성(생성 전, OS에서 해당 디렉터리 만들어야 함)

#mkdir /var/lib/pgsql/11/TABLESP

postgres=# create tablespace TABLESP owner dbsec location '/var/lib/pgsql/11/TABLESP';

 

 

데이터베이스 생성

postgres=# create database <지정할 데이터베이스 이름> with owner='<role name>';

 

 

권한 주기

Alter role <롤 이름> superuser createdb createrole;

 

 

특정 데이터베이스 모든 권한 주는 경우

Grant all on database <database name> to <role name>

 

 

테이블 모든 권한 주는 경우

GRANT ALL ON <table name> TO <role name>;

 

 

다른 role을 사용해서 postgresql 접속

#psql -U <role name> -d <database name> 

단, 이렇게 하기 위해서는 pg_hba.conf 파일 수정

 

<Method 설정 내용>

trust : 무조건 연결 허용, password나 임의의 다른 인증 없이 연결 가능

md5 : 인증을 위해 double-MD5-hashed 패스워드 제공

ident : 클라이언트의 ident 서버에 접촉하여 운영체제 사용자 이름을 획득하고 데이터베이스 사용자 이름과 일치하는지 검증, TCP/IP에서만 사용 가능

peer : 운영체제 이름과 사용자의 이름이 일치하는지 확인, 로컬연결에서만 사용 가능

 

따라서, md5나 trust 로 설정하여 지정(vi /var/lib/pgsql/11/data/pg_hba.conf)

IPv4 local connections: 만 변경해도 동작

 

 

외부 접속 허용 설정(vi /var/lib/pgsql/11/data/postgresql.conf) 

listen_addresss='*' 로 변경

 

 

리스타트

#systemctl restart postgresql-11

 

• 테스트 쿼리

기본 설정이 완료되었다면, http://www.postgresqltutorial.com/postgresql-sample-database/ 에서 dvdrental tar 파일을 다운받아 restore 수행, 수행 전 전용 데이터베이스 생성(여기서는 dvdrental 이름의 데이터베이스 생성 후 작업)

 

/var/lib/pgsql/11 에 dvdrental.tar 를 옮겨두기

#su - postgres

$pg_restore -U postgres -d dvdrental /var/lib/pgsql/11/dvdrental.tar

 

 

이후 확인

postgres=# \connect dvdrental

postgres=# \dt