PostgreSQL source compile install

간단하게 yum install 할 수도 있지만
디렉토리 구성 등을 기본 default 설정과는 다르게 구성하여 관리하고 싶어 source 파일을 다운받아 컴파일 설치하는 식으로 구성해봤습니다.
때문에 여기서의 디렉토리는 default 설정이 아닌 제 임의로 설정한 구성입니다.

postgresql 버전 확인

https://www.postgresql.org/ftp/source/

설치

directory 생성 (kimdubi 계정)

mkdir -p /home1/kimdubi/psql/engn/    => PostgreSQL engine 영역
mkdir -p /home1/kimdubi/psql/data/testdb  => tablespace 등data 저장공간
mkdir -p /home1/kimdubi/psql/logs/testdb/error_log  => error log 
mkdir -p /home1/kimdubi/psql/arch/testdb  => pg_wal 아카이빙하는 공간

11.7 버전 download & 압축해제

[kimdubi@testserver 15:34:53 ~/psql/engn ]$ pwd
/home1/kimdubi/psql/engn

wget https://ftp.postgresql.org/pub/source/v11.7/postgresql-11.7.tar.gz
tar zxvpf postgresql-11.7.tar.gz

make install

[kimdubi@testserver 20:09:06 ~/psql/engn/postgresql-11.7 ]$ pwd
/home1/kimdubi/psql/engn/postgresql-11.7

$./configure --prefix=/home1/kimdubi/psql/engn/postgresql-11.7/ --enable-depend --enable-nls=utf8--with-python
$make
$make install

DB 설치

[kimdubi@testserver 20:07:47 ~/psql/engn/postgresql-11.7/bin ]$ pwd
/home1/kimdubi/psql/engn/postgresql-11.7/bin
    
### initdb 커맨드 수행 -D datadir
 ./initdb -E utf8 -D /home1/kimdubi/psql/engn/PGSQL/ 
     
     
### 설치 수행 로그
    
The files belonging to this database system will be owned by user "kimdubi".
This user must also own the server process.
    
The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home1/kimdubi/psql/engn/PGSQL ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... ROK
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:
    
./pg_ctl -D /home1/kimdubi/psql/engn/PGSQL/ -l logfile start

설치 확인 & 파라미터 수정

[kimdubi@testserver 10:39:21 ~/psql/engn/PGSQL ]$ pwd
/home1/kimdubi/psql/engn/PGSQL

[kimdubi@testserver 10:39:33 ~/psql/engn/PGSQL ]$ ls
PG_VERSION  pg_commit_ts  pg_ident.conf  pg_notify    pg_snapshots  pg_subtrans pg_wal                postgresql.conf
base        pg_dynshmem   pg_logical     pg_replslot  pg_stat       pg_tblspc    pg_xact
global      pg_hba.conf   pg_multixact   pg_serial    pg_stat_tmp   pg_twophase  postgresql.autoconf


$ vi postgresql.conf

$ vi ~./bash_profile

$ vi pg_hba.conf

=> oracle 설치와 비슷하게 엔진 설치 후 parameter 파일 설정 & DB 기동하는 순서

DB 기동

$ pg_ctl -D /home1/kimdubi/psqlengn/PGSQL/ start

waiting for server to start....2020-04-07 01:50:00 GMT LOG:  listening on IPv4 address "0.0.0.0",port 3000
2020-04-07 01:50:00 GMT LOG:  listening on IPv6 address "::", port 3000
2020-04-07 01:50:00 GMT LOG:  listening on Unix socket "/tmp/.s.PGSQL.3000"
2020-04-07 01:50:00 GMT LOG:  redirecting log output to logging collector process
2020-04-07 01:50:00 GMT HINT:  Future log output will appear in directory "/home1/kimdubi/psqllogs/testdb/error_log".
 done
server started

$ pg_ctl -D /home1/kimdubi/psqlengn/PGSQL/ status

pg_ctl: server is running (PID: 13079)
/home1/kimdubi/psql/engn/postgresql-11.7/bin/postgres "-D" "/home1/kimdubi/psql/engn/PGSQL"

생성 후 작업

tablespace / database 생성

### TABLESPACE 생성
-. OS 디렉토리 생성
mkdir -p /home1/kimdubi/psql/data/testdb/TS_USER
mkdir -p /home1/kimdubi/psql/data/testdb/TS_TEMP
mkdir -p /home1/kimdubi/psql/data/testdb/TS_TEST_D
mkdir -p /home1/kimdubi/psql/indx/testdb/TS_TEST_I

-. DB tablespace 생성
postgres=# create tablespace TS_USER location '/home1/kimdubi/psql/data/testdb/TS_USER';
postgres=# create tablespace TS_TEMP location '/home1/kimdubi/psql/data/testdb/TS_TEMP';
postgres=# create tablespace TS_TEST_D location '/home1/kimdubi/psql/data/testdb/TS_TEST_D';
postgres=# create tablespace TS_TEST_I location '/home1/kimdubi/psql/indx/testdb/TS_TEST_I';

USER 생성 => 복잡하지만 서비스용 schema, user를 별도로 생성했습니다.
보안 및 권한으로 인한 장애를 막기 위함인데요
PostgreSQL은 ORACLE / MySQL 과 user,role,schema 개념이 조금 달라 헷갈릴 수 있습니다. 이 부분은 다음 시간에 별도로 다루겠습니다.

### 서비스 계정

create role testuser login password '!dlatl00' noinherit valid until 'infinity';
create schema testuser authorization testuser;
alter role testuser set default_tablespace=ts_test_d;
grant create on tablespace ts_test_d to testuser;
grant create on tablespace ts_test_i to testuser;
grant create on tablespace ts_temp to testuser;

###
postgres=# create database testdb  tablespace='ts_test_d' with owner kimdubi;
CREATE DATABASE

권한 설정

### super user로 db 접속
testdb=> \c testdb kimdubi

### 다른 계정의 접근 차단 및 public schema 사용 권한 회수
testdb=# revoke all on database testdb from public;
testdb=# revoke all on schema public from public;

### 서비스계정에 권한 추가
testdb=# create schema testuser authorization testuser;

grant connect,temporary on database testdb to testuser;
grant usage on schema testuser to testuser;
grant select,insert,update,delete on all tables in schema testuser to testuser;
grant usage on all sequences in schema testuser to testuser;
alter default privileges in schema testuser grant select,insert,update,delete on tables to testuser;
alter default privileges in schema testuser grant usage on sequences to testuser;

### 새로운 계정 추가되는 경우
create role new_testuser login password '!dlatl00' noinherit valid until 'infinity';
grant connect,temporary on database testdb to new_testuser;
grant usage on schema testuser to new_testuser;
grant select,insert,update,delete on all tables in schema testuser to new_testuser;

DB 접속 권한 확인 => schema usage 권한 확인 => 각 개별 테이블 권한 확인

postgresql 버전 확인#

설치#

생성 후 작업#

postgresql 버전 확인

설치

생성 후 작업