postgreSQL RDS dump 뜨기(pg_dumpall)

AWS에서 서비스하는 postgreSQL RDS의 경우 super user 권한을 가진 user가 없어서 pg_dumpall 를 수행할 수 없다고 공식 가이드 문서에서 확인할 수 있다.

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

하지만, 약간의 변형을 가하면, pg_dumpall 명령어를 사용할 수 있다.

기본 구성

• postgreSQL (source DB) : dump를 뜨려는 원본 RDS
• postgreSQL (target DB) : dump를 복원하려는 타겟 RDS
• dump용 EC2 : postgreSQL client 버전 12 이상이 설치되어 있는 서버. source RDS와 target RDS에 접속가능해야 한다.

DUMP 수행

postgreSQL RDS는 rdsadmin 이 소유하고 있는 database가 있는데 이건 RDS 생성시 만들었던 master 계정이 권한이 없기 때문에 덤프를 뜨는게 불가능하다. postgreSQL 12버전 이상을 사용하면 특정 database를 제외하고 덤프를 뜰 수 있다.(–exclude-database 옵션)

덤프를 뜨기전, master 계정이 database에 권한을 가지고 있는지, tablespace 소유 여부를 확인한다.

# postgreSQL 접속
$ psql --host=(source RDS Endpoint) --port=(source RDS port) --username=(master 계정) --password

# db list 조회
> \l 

# 사용자 권한 조회
> \du

# table space 조회
> \db

# master 계정 소유로 table space 변경
> ALTER TABLESPACE (tablespace 명) OWNER TO (master 계정);

# master 계정에게 db 권한 부여
> GRANT (db명) TO (master 계정);

master계정에게 권한이 부여되었다면, 아래 명령어로 rdsadmin이 소유한 database는 제외하고 dump를 뜬다. –no-role-passwords 옵션을 사용하여 super_user 권한이 없는 계정으로 dump를 뜬다. 복원 후 사용자 비밀번호는 다시 지정한다.

$ pg_dumpall --no-role-passwords -f (파일명) --exclude-database=rdsadmin --exclude-database=template0 -v -h (source RDS endpoint) -U (master 계정) -p (source RDS port)

데이터 용량이 많아서 압축 덤프를 사용하는 경우의 명령어는 아래와 같다.(gzip 사용)

$ pg_dumpall --no-role-passwords --exclude-database=rdsadmin --exclude-database=template0 -v -h (source RDS endpoint) -U (master 계정) -p (source RDS port) | gzip > 파일명.gz

덤프 중 실제로 잘 덤프를 뜨고 있는지 여부에 대한 확인은 RDS의 모니터링 지표 중 connection 지표를 확인한다.

RESTORE 수행

덤프 작업이 완료 된 후 restore를 수행한다.

$ psql -h (target RDS endpoint) -U (master 계정) -p (target RDS port) < (백업 파일)

압축 덤프를 사용했던 경우에는 아래 명령어로 복원한다.

gunzip -c 파일명.gz | psql -h (target RDS endpoint) -U (master 계정) -p (target RDS port) 

복원 후 psql로 target RDS에 접속하여 사용자 비밀번호를 다시 입력한다.

# target RDS 연결
$ psql --host=(target RDS Endpoint) --port=(target RDS port) --username=(master 계정) --password

# 사용자 정보 변경
> ALTER ROLE (사용자명) WITH LOGIN;
> ALTER USER (사용자명) WITH PASSWORD '(비밀번호)';

40 GB 데이터 베이스 기준으로 dump 뜨는데 30분, restore하는데 20분이 수행되었다.

pg_dump의 경우 table space 및 사용자의 권한은 복사되지 않고, 데이터 베이스 별로 덤프를 떠서 복원해야 하기 때문에 RDS 전체 데이터를 백업해야 하는 경우에는 pg_dumpall 이 더 빠르고 정확하고 편하다.