PostgreSQL 설치/설정/백업/복구

Mac

1. PostgreSQL 설치

2. 계정,DB 생성

postgresql 터미널 접속

유저 지정해서 DB생성

Ubuntu

1. PostgreSQL 설치

2. 계정,DB 생성

postgresql 터미널 접속

유저 지정해서 DB생성 (owner)

3. 외부 접근 허용

재시작

연결 확인

DB백업 및 복구

참고 사이트

Mac

1. PostgreSQL 설치

설치

$brew install postgresql@14

서비스 관련 명령어

$brew services <start | stop | info> postgresql

2. 계정,DB 생성

postgresql 터미널 접속

psql postgres

DB생성 및 확인

CREATE DATABASE [db_name];

\list

유저 추가 및 확인

데이터 베이스에 접근 가능하며 슈퍼유저,DB생성, 복제 권한을 가지는 유저 생성

CREATE USER [user_name] WITH SUPERUSER CREATEDB REPLICATION PASSWORD '[password]';

유저 확인

\du

유저 지정해서 DB생성

CREATE DATABASE [db_name] OWNER [user_name]

*비밀번호 변경시

ALTER USER [user_name] WITH PASSWORD '[new_password]';

*유저 삭제시

DROP USER [user_name]

---

Ubuntu

1. PostgreSQL 설치

출처 : https://smoh.tistory.com/298

# 다운받을 수 있는 버전 확인
$apt show postgresql

https://smoh.tistory.com/298

→ postgresql 10 이후 버전의 경우 별도 리포지토리 등록 작업 필요하다고 합니다

1. pgdg.list 파일 생성

   #pgdg 파일 생성
   $sudo vi /etc/apt/sources.list.d/pgdg.list
   
   #pgdg 내용추가
   deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

   

2. gpg키 추가 및 apt 업데이트

   $wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   OK
   $sudo apt-get update
   $sudo apt show postgresql # 다운받을 수 있는 버전 확인

3. postgresql 설치

   $sudo apt-get install postgresql postgresql-contrib
   $service postgresql status #설치 확인

   • * postgresql-contrib이란?
     postgresql-contrib 또는 contrib 패키지는 핵심 PostgreSQL 패키지의 일부가 아닌 일부 추가 유틸리티와 기능으로 구성됩니다. 대부분의 경우 PostgreSQL 코어와 함께 contrib 패키지를 설치하는 것이 좋습니다.
     

apt-get 업데이트 및 postgresql 다운

$sudo apt-get update
$sudo apt-get install -y postgresql postgresql-contrib

서비스 관련 명령어

$sudo systemctl <start | stop | status> postgresql

2. 계정,DB 생성

postgresql 터미널 접속

postgres 유저로 변경

$sudo su postgres

터미널 접속

psql [dbname]

DB생성 및 확인

CREATE DATABASE [db_name];

\list

유저 추가 및 확인

데이터 베이스에 접근 가능하며 슈퍼유저,DB생성, 복제 권한을 가지는 유저 생성

CREATE USER [user_name] WITH SUPERUSER CREATEDB REPLICATION PASSWORD '[password]';

유저 확인

\du

유저 지정해서 DB생성 (owner)

CREATE DATABASE [db_name] OWNER [user_name]

*비밀번호 변경시

ALTER USER [user_name] WITH PASSWORD '[new_password]';

*유저 삭제

DROP USER [user_name]

3. 외부 접근 허용

외부 접속은 허용하되, 보안을 위해 IP주소로 제한을 걸어서 특정 사용자만 외부에서 접근할 수 있도록 설정

postgresql.conf 파일에서 외부 모든 아이피의 접속을 허용하고,

pg_hba.conf 파일에서는 허용할 아이피를 작성한다.

pg-hba.conf 파일 수정

$sudo vi /etc/postgresql/14/main/pg_hba.conf

• 내부 서버 대역인 172.30.0.0 ~ 172.30.255.255 추가

• 외부 IP aws 퍼블릭 IPv4 추가( aws 퍼블릭 ip는 인스턴스 재시작시 재할당 되니 주의)
  • 외부에서 VPN 없이 접속해야되면 별도의 설정 필요 (공유기 설정에서 ‘포트 포워딩’ 해야 함)

postgresql.conf 파일 수정

$sudo vi /etc/postgresql/14/main/postgresql.conf

listen_addresses 를 ‘localhost’ → ‘*’ 로 수정

수정전

수정 후

재시작

$sudo systemctl restart postgresql

포트 열렸는지 확인

$netstat -ntlp | grep 5432

연결 확인

DB백업 및 복구

출처 : https://mozi.tistory.com/557

PostgreSQL 은 장애시 복구를 위해 3가지 백업방법이 있습니다.

3가지 방법은 SQL 덤프 / 파일 시스템 기반 백업 / 아카이브 모드 백업입니다.

백업방식은 pg_dump 명령어로 진행하였습니다.

pg_dump 를 실행하면 DB_NAME 데이터베이스에 생성된 객체와 데이터들을 SQL 구문 텍스트 형식으로 백업합니다.
기본적으로 표준출력이기 때문에 리다이렉션 기능을 사용하여 파일로 저장합니다.

백업

$pg_dump -U 소유주 DB_NAME > postgres_DB_NAME_bak.sql

옵션

일반 클라이언트와 동일하게 원격으로 접속할 수 있는 옵션을 제공하고 있습니다.

따라서 원격 데이터베이스에 접속 및 백업할 수 있는 권한이 있다면 원격서버의 DB 를 백업받을 수 있습니다.

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

pg_dump 백업의 장점

pg_dump 를 이용하면 SQL 형식으로 쿼리가 출력되기 때문에,

복원하려는 PostgreSQL 버전이 다르거나, 운영체제 비트가 달라도 적용이 가능합니다.

pg_dump 는 실행되는 시점의 스냅샷 상태로 작업을 진행합니다.

이 때 배타적 잠금이 발생하지 않는 상태라면, 대부분 작업은 정상적으로 수행됩니다.

pg_dump 주의점

만약 기존에 OID (Object Identifier Types) 값을 사용자 입장에서 사용하는 곳이 있다면, pg_dump 시 -o 옵션을 붙여서 복원할 때도 동일한 값으로 되도록 해야합니다.

만약 -o 옵션이 부여되지 않고 백업받은 경우 복원 시 OID 값이 변경될 수 있습니다.

데이터를 포함한 모든 내용을 쿼리로 출력하기 때문에 리다이렉션되는 파일의 크기가 매우 커질수 있습니다.

최대 파일 크기가 제한된 운영체제도 있어, 이 크기를 초과하는 경우에 문제가 될 수 있으며 이런경우 압축과 같은 기능을 이용하여 해결할 수 있습니다.

# 백업 시
$pg_dump DB_NAME | gzip > postgres_DB_NAME_bak.gz
 
# 복원 시
$gunzip -c postgres_DB_NAME_bak.gz | psql DB_NAME

복원

pg_dump 로 받은 백업파일을 SQL 형태로 되어있기 때문에, 쿼리 import 형식으로 바로 수행이 가능합니다.

위에서 백업받은 덤프 백업파일을 DB_NAME 에 수행해 줍니다.

참고로 SQL 덤프백업에서는 데이터베이스와 사용자 생성 쿼리가 없기 때문에,

복원할 데이터베이스와 사용자는 사전에 만들어 주어야 합니다.

$psql DB_NAME < postgres_DB_NAME_bak.sql

기본적으로는 작업도중 오류가 발생해도 파일의 끝까지 진행됩니다.

이런 기능을 원하지 않는경우에는 ON_ERROR_STOP 옵션을 부여하여 실패 한 이후 쿼리부터는 수행되지 않도록 합니다.

그러나 실패 전까지의 쿼리는 데이터베이스에 적용됩니다.

만약 이를 원치않고 실패 시 모든작업을 취소하고 싶은경우 single-transaction 옵션을 사용하면 됩니다.

$psql --set ON_ERROR_STOP=1 --single-transaction < postgres_DB_NAME_bak.sql

참고 사이트

https://mozi.tistory.com/557

https://smoh.tistory.com/298