Postgresql 기본 설치, 데이터 스토리지 변경, 삭제 (Ubuntu 16.x)

기본설치

설치

$sudo apt-get install postgresql

Database 및 계정 생성

$sudo -u postgres psql

postgres=# create database test_db;
CREATE DATABASE

postgres=# create user test_user with encrypted password '1234';
CREATE ROLE

postgres=# GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;
GRANT

원격 접근 설정

$sudo vim /etc/postgresql/9.5/main/postgresql.conf

//전체 IP 대역 접근 허용
//'*' 전체 IP 허용이니 개별 IP로 구체적 지정하는게 보안상 더욱 좋다
#listen_addresses = 'localhost' => listen_addresses = '*' 

DB,계정별 접근 설정

$sudo vim /etc/postgresql/9.5/main/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
//데이터베이스 , 계정의 접근권한 IP 설정
//0.0.0.0/0 전체 IP 허용이니 개별 IP로 구체적 지정하는게 보안상 더욱 좋다
host   test_db         test_user        0.0.0.0/0               md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

DB 서버 서비스 재시작

sudo systemctl restart postgresql

 

데이터 스토리지 위치 변경

현재 위치 확인

sudo -u postgres psql

postgres=# show data_directory;

Output
       data_directory       
------------------------------
/var/lib/postgresql/9.5/main
(1 row)

DB 서버 중단

//중단 요청
sudo systemctl stop postgresql

//중단 상태 확인
sudo systemctl status postgresql

DB 서버 복사(권한,구조를 포함)

// /mnt/stroage 가 변경할 디렉토리 패스 
sudo rsync -av /var/lib/postgresql /mnt/stroage   

기존 DB 백업

sudo mv /var/lib/postgresql/9.5/main /var/lib/postgresql/9.5/main.bak

데이터 패스 변경

sudo vim /etc/postgresql/9.5/main/postgresql.conf

. . .
data_directory = '/mnt/stroage/postgresql/9.5/main'     # use data in another directory
                                                        # (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       # host-based authentication file
                                                        # (change requires restart)
. . .

DB서버시작 및 변경 확인

$sudo systemctl start postgresql

$sudo -u postgres psql

postgres=# show data_directory;

output
          data_directory
----------------------------------
 /mnt/stroage/postgresql/9.5/main
(1 row)

 

Postgresql 삭제

DB서버 중지

sudo systemctl stop postgresql

sudo systemctl status postgresql

output
------
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Fri 2019-10-04 13:28:57 KST; 15s ago
  Process: 31856 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 31856 (code=exited, status=0/SUCCESS)

Oct 04 13:22:16 eeg-test-mysql systemd[1]: Starting PostgreSQL RDBMS...
Oct 04 13:22:16 eeg-test-mysql systemd[1]: Started PostgreSQL RDBMS.
Oct 04 13:28:57 eeg-test-mysql systemd[1]: Stopped PostgreSQL RDBMS.

Postgresql 설치확인

dpkg -l|grep postgres

output
------
ii  postgresql                         9.5+173ubuntu0.2                           all          object-relational SQL database (supported version)
ii  postgresql-9.5                     9.5.19-0ubuntu0.16.04.1                    amd64        object-relational SQL database, version 9.5 server
ii  postgresql-client-9.5              9.5.19-0ubuntu0.16.04.1                    amd64        front-end programs for PostgreSQL 9.5
ii  postgresql-client-common           173ubuntu0.2                               all          manager for multiple PostgreSQL client versions
ii  postgresql-common                  173ubuntu0.2                               all          PostgreSQL database-cluster manager
ii  postgresql-contrib-9.5             9.5.19-0ubuntu0.16.04.1                    amd64        additional facilities for PostgreSQL

Postgresql 설치 삭제

apt-get --purge remove postgresql postgresql-9.5 postgresql-client-9.5 postgresql-client-common postgresql-common postgresql-contrib-9.5