PostgreSQL은 고성능과 확장성을 자랑하며, 오픈 소스로 널리 사용되는 관계형 데이터베이스 시스템입니다. 이번 글에서는 PostgreSQL 데이터베이스의 생성부터 백업까지 전반적인 과정을 자세히 설명합니다. 특히, DBeaver를 활용한 데이터베이스 생성, 관리 및 쿼리 실행 방법을 다루고, pg_dump를 사용한 효율적인 백업 절차를 소개합니다. 또한 Docker와 Kubernetes와 같은 컨테이너 환경에서 데이터베이스 백업을 어떻게 진행해야 하는지에 대해 실용적인 접근법을 제시합니다. 이를 통해 다양한 환경에서 PostgreSQL 데이터베이스를 효과적으로 관리하고 보호하는 방법을 이해할 수 있을 것입니다.
* DBeaver 접속
* 먼저 메뉴에서 “Database > New Database Connection”을 선택하고 DB종류중 PostgreSQL을 선택합니다.
> Host : db 서버 도메인 혹은 IP
> Port : 접속 포트 (default : 5432)
> Database : 데이터베이트 이름 (default : postgres)
> Username / Password : 접속 계정 정보 (설치시 postgres 계정에 대해 설정한 비밀번호)
> Host : db 서버 도메인 혹은 IP
> Port : 접속 포트 (default : 5432)
> Database : 데이터베이트 이름 (default : postgres)
> Username / Password : 접속 계정 정보 (설치시 postgres 계정에 대해 설정한 비밀번호)
* Test Connection : 입력한 정보고 정상 접속이 되는지 확인
* 메뉴에서 SQL Editor > New SQL Script 선택하여 SQL 쿼리를 실행할 수 있는 script창 활성화
* SQL 편집기에 쿼리 작성후 “Control + Enter” 키 입력
* DB 생성 & DB 관리자 USER 생성
* 아래 예제는 test database를 만들고, 만들어진 test database를 관리하는 슈퍼관리자로 test_admin 유저를 생성하는 쿼리 입니다.
-- Create database
CREATE DATABASE test;
-- Create user
CREATE USER test_admin WITH PASSWORD 'secure_password';
-- Make a user the manager of a table
ALTER DATABASE test OWNER TO test_admin;
* 만약 사용자를 PostgreSQL 전체를 관리하는 슈퍼유저로 만들고 싶다면 아래 쿼리를 이용하면 됩니다. 하지만 DB를 관리하기 위해 사용자를 만들고 개발 및 관리의 편의성을 위해 슈퍼유저권한으로 상승 시키는 것은 추천하지 않는 방법 입니다.
ALTER USER test_admin WITH SUPERUSER;
* 생성된 데이터베이스와 사용자 정보를 이용하여 다시 접속해 보면 접속이 되는 것을 확인 할 수 있습니다.
* DB 백업
* 백업은 pg_dump를 이용하면 됩니다. 만약 Local에 PostgreSQL을 설치 했다면 Local shell에서 아래 명령을 바로 사용하면 됩니다.
# pg_dump -U {user name} {db name} > {backup file name}
postgres-user:~$ pg_dump -U test_admin test > backup.sql
* Container에서 실행되는 DB 백업
* PostgreSQL이 컨테이너에서 실행 중인 경우, 먼저 컨테이너 내부의 셸에 접속해야 합니다. 백업을 수행하면 백업 파일이 생성되지만, 일반적으로 컨테이너 이미지는 보안상의 이유로 접속 사용자에게 제한된 권한만 부여합니다. 이로 인해 파일을 생성할 수 없는 상황이 발생할 수 있습니다. 이를 해결하기 위해 /tmp 디렉토리로 이동하는 것이 일반적입니다. 이 디렉토리는 일반 사용자도 파일을 생성할 수 있도록 허용되어 있기 때문입니다.
# Check the container id
user:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS
c3f279d17e0a postgresq "/bin/bash" 5 minutes ago Up 5 minute
# Enter the Container : docker exec -it <container_name_or_id> /bin/bash
user:~$ docker exec -it c3f279d17e0a /bin/bash
# Change directory
c3f279d17e0a:~$ cd /tmp
# DB backup : pg_dump -U {user name} {db name} > {backup file name}
c3f279d17e0a:/tmp$ pg_dump -U test_admin test > backup.sql
# Exit from the Docker container to the local host
c3f279d17e0a:/tmp$ exit
user:~$
* 생성된 백업 파일(backup.sql)을 로컬로 가져오기 위해 Docker 명령어를 사용하여 로컬 호스트로 복사합니다.
# Copy the backup.sql file from the container to the local directory
user:~$ docker cp c3f279d17e0a:/tmp/backup.sql .
* DB 복원
* 복원은 psql 을 이용하면 됩니다. 만약 Local에 PostgreSQL을 설치 했다면 Localhost shell에서 아래 명령을 바로 사용하면 됩니다.
# psql -U {user name} {db name} < {backup file name}
postgres-user:~$ psql -U test_admin test < backup.sql
* Container에서 실행되는 DB 복원
# Check the container id
user:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS
c3f279d17e0a postgresq "/bin/bash" 5 minutes ago Up 5 minute
# Copy the backup.sql file from the local directory to the container
user:~$ docker cp backup.sql c3f279d17e0a:/tmp
# Enter the Container : docker exec -it <container_name_or_id> /bin/bash
user:~$ docker exec -it c3f279d17e0a /bin/bash
# Change directory
c3f279d17e0a:~$ cd /tmp
# DB restore : psql -U {user name} {db name} < {backup file name}
c3f279d17e0a:/tmp$ psql -U test_admin test < backup.sql
# Exit from the Docker container to the local host
c3f279d17e0a:/tmp$ exit
user:~$
