회사에서 진행하고 있는 프로젝트에서 사용할 테스트용 데이터베이스 서버 구축을 위해 PostgreSQL 설치 과정을 작성하려고 한다. 설치는 Ubuntu 18.04.4 버전에서 진행하였다.
PostgreSQL 11 설치
# Repository 추가
echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >> /etc/apt/sources.list.d/pgdg.list
# Signing Key 등록
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
apt-get install postgresql-11
# 설치 후 접속
sudo -i -u postgres psql정상적으로 접속 후, 쉘이 postgres=# 으로 변경된다면 설치가 완료된 것이다. 앞으로 사용하는 명령어는 PostgreSQL에 접속한 상태에서 사용한다.
사용자 생성
PostgreSQL 에서 현재 사용자 정보를 확인하려면 PG_USER 테이블을 확인하면 된다. 사용자 생성은 아래 명령어를 사용한다.
CREATE USER [사용자명] PASSWORD '[패스워드]' SUPERUSER;
뒤에 SUPERUSER를 입력하면 SUPERUSER 권한을 부여하고 기본값은 NOSUPERUSER이다.
그럼 사용자를 생성해보자. 그 전에 기본 사용자인 postgres의 패스워드를 변경하자.
ALTER USER postgres with encrypted password 'password';
CREATE USER test PASSWORD 'password' SUPERUSER;데이터베이스 생성
PostgreSQL의 데이터베이스는 여러 개의 스키마(SCHEMA)들로 구성되어 있다. 스키마는 TABLE, VIEW, SEQUENCE, DOMAIN, FUNCTION 등의 OBJECT들로 구성되어 있는 것을 말한다. 이러한 데이터베이스와 USER/GROUPS, TABLESPACES들이 모여 하나의 CLUSTER를 이룬다.
데이터베이스 생성은 아래 명령어를 사용한다.
CREATE DATABASE 데이터베이스명 OWNER 사용자명;
데이터베이스를 생성하기 위해서는 SUPERUSER이거나 CREATEDB 권한이 있는 사용자이어야 한다. 데이터베이스 목록을 조회하려면 \l 입력하거나 PG_DATABASE 테이블을 조회한다.
외부 접속 허용
PostgreSQL을 처음에 설치하면 기본적으로 외부에서 접속이 불가능하다. 따라서 설정 파일을 수정해줘야 하는데, 일단 \q 명령어를 입력하여 PostgreSQL에서 접속 해제한다.
PostgreSQL의 기본 포트는 5432이다. netstat -ntlp 명령어를 입력하면 127.0.0.1:5432가 출력될 것이다. 이는 내부에서만 접속할 수 있다는 의미이다.
2개의 설정 파일을 수정해야한다. 일단 첫 번째 파일이다.
vi /etc/postgresql/11/main/postgresql.conf
위 캡처 화면에서 listen_addresses = '*' 부분을 주목하자. 기본적으로 localhost로 되어 있지만 *로 변경하여 어디서든 접속할 수 있게 설정한다.
그 다음 두 번째 파일이다.
vi /etc/postgresql/11/main/pg_hba.conf
맨 아래에 있는 줄을 입력하고 저장하고 PostgreSQL을 재시작한다. 그 후에는 DBeaver와 같은 데이터베이스 관리 도구를 통해 접속이 가능하다.
데이터베이스 백업 및 복원
원래라면 데이터베이스 생성 후 필요한 테이블을 생성하고, 기본키 등 제약조건을 추가하는 등의 작업을 해야하지만, 이는 테스트용 데이터베이스 서버 구축을 위한 것으로, 실제 사용 중인 데이터베이스를 백업하여 이를 복원해서 사용할 것이다.
이 때 pg_dump라는 프로그램을 사용하는데, 아래 명령어를 입력하면 외부 데이터베이스 서버에 있는 데이터베이스를 백업한다.
pg_dump -s -h [데이터베이스 서버 주소] -U [사용자명] -d [데이터베이스명] > [파일명]
여기서 입력하는 사용자명과 데이터베이스명은 백업할 데이터베이스 서버에서 사용하는 정보이다. 명령어를 입력했을 때 해당 사용자에 패스워드가 설정되어 있다면 패스워드 입력 후 db.sql 파일이 생성될 것이다.
그 후 복원하려는 테스트용 데이터베이스 서버에서 아래 명령어를 실행하여 복원한다.
psql -U [사용자명] -d [데이터베이스명] -f [파일명]
복원하려는 서버와 실제 백업했던 서버의 데이터베이스명 및 사용자명이 다르다면 백업 파일 내에 존재하는 DDL문을 바꿔치기하면 된다.
예를 들어, 실제 서버에서 사용하는 사용자명이 user이고 테스트용 서버에서 생성한 사용자명이 test라면 덤프 파일을 열어 user라는 단어를 모두 test로 바꾸고 복원하면 된다. 하지만 이 때 user처럼 SQL 명령어로 사용될법한 단어라면... 다른 방법을 찾아야한다.
