코드노예

이번에는 PostgreSQL 데이터베이스를 백업하고 복구하는 방법에 대해서 살펴보겠습니다. database 이름은 testdb 라고 가정하였으며, 백업해서 생성할 파일은 db_backup_file.sql 입니다.

DB 백업하기

DB 백업을 위해서는 pg_dump 명령어를 사용합니다.

백업시에 많이 사용되는 기본 옵션들과 같이 적어 보았습니다.

pg_dump -h <server_host> -p <port> -U <username> -d <db_name> -f <file_name>

만약 DB 서버에서 root 권한으로 백업을 한다면, 아래와 같이 db_name과 file_name만 적어줘도 됩니다.

pg_dump testdb -f db_backup_file.sql

f 옵션으로 파일 이름을 지정하는 대신 redirect를 이용할 수도 있습니다.

pg_dump testdb > db_backup_file.sql

DB 복구하기

위에서 백업한 파일은 sql 구문형태로 저장이 됩니다. 물론 복구시의 최적화를 위해, 제약사항들을 나중에 추가하는 방식으로 기록됩니다. 따라서 데이터 복구를 위해서는 sql 파일을 실행해 주면 됩니다.

psql -h <server_host> -p <port> -U <username> -d <db_name> -f <file_name>

혹은

psql -h <server_host> -p <port> -U <username> -d <db_name> < <file_name>

DB서버에서 root 권한으로 복구를 한다면, db_name과 file_name 만 있으면 됩니다.

psql testdb -f db_backup_file.sql

혹은

psql testdb < db_backup_file.sql

대용량 DB 다루기

1. 대용량 DB를 백업하거나 복구할 때는, gzip / gunzip 을 이용할 수 있습니다.

pg_dump testdb | gzip > db_backup_file.sql.gz

복구시에는 반대로 압축을 풀면서 스트림을 전달해 주면 됩니다.

gunzip -c db_backup_file.sql.gz | psql testdb

아니면 cat 명령어를 이용하여 다음과 같이 사용해도 결과는 같습니다.

cat db_backup_file.sql.gz | gunzip | psql testdb

2. 압축을 선호하지 않는다면, split 명령어를 이용하여 적당한 크기로 분할할 수 있습니다.

pg_dump testdb | split -b 2G - db_backup_file.sql

합치는 것은 cat 명령어를 이용하면 됩니다. 

cat db_backup_file.sql* | psql testdb

3. 커스텀 dump 포맷을 사용하는 경우

=> 이 경우 백업 결과, 압축 포맷으로 저장되기 때문에, pg_restore를 이용해서 복구를 해야 합니다.

pg_dump -Fc testdb > db_backup_file.sql

pg_restore를 이용하여 복구

pg_restore -d testdb db_backup_file.sql

유용한 팁

아래 명령어를 이용하면, src DB 서버에서 백업을 하면서, 동시에 target DB 서버에 restore를 할 수 있습니다.

pg_dump -h <src_db_host> <db_name> | psql -h <target_db_host> <db_name>

PostgreSQL에서는 Role을 정의해서 DB 이용에 대한 권한을 부여합니다. 다른 DB의 user와 비슷한 개념이지만 좀 더 포괄적으로 사용할 수 있습니다.

여기서는 testuser 라는 role을 생성할 예정이고, 해당 role에게 testdb에 접속해서 public schema 에 있는 테이블을 사용하는 권한을 부여하는 방법에 대해서 알아보도록 하겠습니다.

ROLE 생성하기

Role 생성은 CREATE ROLE statement를 사용합니다.

CREATE ROLE testuser;

cf. role 이름은 '소문자'만 허용이 되는 것 같습니다. 생성시에 이름을 대문자로 입력해도 '소문자'로 생성이 되네요..

ROLE 확인하기

생성된 Role은 다음의 statement로 확인이 가능합니다.

SELECT rolname FROM pg_roles;

혹은 psql 에서는 아래 command를 이용할 수도 있습니다.

\du

ROLE 에 속성 추가/변경 하기

위에서 생성된 role은 아무런 권한이 부여되지 않았기 때문에, 아무것도 할 수 없습니다. 로그인 조차 할 수 없기 때문에, 필요한 속성을 추가해 줘야 합니다. ALTER ROLE statement 를 이용하여 지정한 password를 이용해서 로그인을 할 수 있는 권한을 부여합니다.

ALTER ROLE testuser [WITH] LOGIN PASSWORD 'password';

WITH는 LOGIN, PASSWORD 같은 옵션 앞에 붙인다고 되어 있는데, 생략해도 되는 것 같습니다. ( PostgreSQL 16 기준)

추가할 수 있는 속성에 대해서는 PostgreSQL manual 페이지를 참조해 주세요.

https://www.postgresql.org/docs/current/sql-alterrole.html

참고로 PostgreSQL에서, CREATE USER는 CREATE ROLE 의 alias 로 쓰이고 있어서, 아래 구문은 위와 같습니다.

ALTER USER testuser [WITH] LOGIN PASSWORD 'password';

참고로 LOGIN 속성을 제거 하고 싶다면, NOLOGIN 속성을 부여하면 됩니다.

Password 변경은 ALTER ROLE 혹은 ALTER USER statement로 PASSWORD 를 다시 지정해 주면 됩니다.

ALTER ROLE testuser PASSWORD 'new_password';

DB 연결 권한 추가하기

Role은 전체 PostgresSQL 시스템에서 유저를 관리하는 개념이었고, 개별 database에 접근하는 권한은 각 database 별도로 따로 지정해 줘야 합니다. 먼저 DB 연결 권한을 부여합니다.

GRANT CONNECT ON DATABASE testdb TO testuser;

DB 사용 권한 추가하기

다음으로 DB 사용 권한을 부여해 줍니다. DB 사용 권한은 schema 별로 지정해 줘야 합니다. 여기서는 public schema에 대한 권한을 부여해 주겠습니다. DB 사용 권한 부여시에는 해당 DB에 접속한 상태에서 구문을 입력합니다.

GRANT USAGE ON SCHEMA public TO testuser;

이번에는 select 구문 사용 권한을 추가해 봅니다.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO testuser;

select 이외에도 insert, update, delete 등의 권한을 추가해 줄 수 있습니다.

그 밖에 자세한 권한에 대한 정보는 아래 링크를 참고하시면 됩니다.

https://www.postgresql.org/docs/current/sql-grant.html

위의 구문으로 DB 테이블에 대한 권한을 부여하면, 권한을 줄 때 당시에 있던 테이블에 대해서만 권한이 적용되며, 나중에 추가한 테이블에 대한 권한은 자동으로 추가되지 않습니다. 모든 새로 생성된 테이블에 대한 권한을 자동으로 추가해 주려면 아래와 같은 구문을 실행해 줍니다.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO testuser;

DB 사용 권한 제거하기

위에서 생성한 DB 권한은 Revoke statement로 제거할 수 있습니다.

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM testuser;

cf.  테이블 별 사용 권한 확인

\dp <tablename>

ROLE 제거하기

위에서 생성했던 Role은 DROP ROLE statement로 제거할 수 있습니다. 하지만 그 전에 부여했던 모든 권한을 DB별로 모두 제거해 줘야 합니다.

권한 제거는 DROP OWNED statement를 이용합니다. 아래와 같이 사용하면 됩니다.

DROP OWNED BY testuser CASCADE;

그리고 나서 Role을 제거합니다.

DROP ROLE testuser;

아래 URL 내용 참고.

https://stackoverflow.com/questions/3023583/how-to-quickly-drop-a-user-with-existing-privileges