postgres 마이그레이션

얼마 전 회사에서 A 서버의 Docker 위에서 돌아가는 postgres를 B 서버로 마이그레이션하는 작업을 맡았다.

 

일단 덤프 파일을 만들기 위해 A 서버에서 다음과 같은 명령어를 실행해준다.

docker exec -i <컨테이너 ID 또는 이름> pg_dumpall -U <username> > <output_file>.sql

 

`pg_dumpall` 명령어는 postgres에 존재하는 모든 데이터베이스에 대한 덤프 파일을 만들 수 있는데, 만약 일부 데이터베이스의 덤프 파일이 필요하면 `pg_dump` 명령어를 사용하면 된다.

 

다음으로 A 서버의 덤프 파일을 B 서버로 옮겨줘야 하는데, 나는 Termius라는 멀티 SSH 플랫폼을 사용하여 쉽게 옮겼다.

 

이제 A 서버에서 사용하던 postgres docker-compose.yml 파일의 내용을 가져와서, B 서버에서 실행해주면 된다.

여기서 한 가지 문제가 발생했는데, 내용은 다음과 같다.

 

<기존 docker-compose.yml 파일>

services:
  postgres:
    image: bitnami/postgresql:16
    container_name: postgres_container
    environment:
      - POSTGRESQL_USERNAME=username
      - POSTGRESQL_PASSWORD=********
      - POSTGRESQL_DATABASE=database
      - POSTGRES_PASSWORD=********
      - TZ=Asia/Seoul
    volumes:
      - ./data:/bitnami/postgresql
    ports:
      - "5432:5432"
    restart: always

volumes:
  postgres_data:
    driver: local

B 서버의 postgres에 덤프 파일을 씌우려면 슈퍼 유저 권한이 필요한데, 위 파일로는 슈퍼 유저(postgres)의 비밀번호를 설정할 수 없었다.

 

<새로운 docker-compose.yml 파일>

services:
  postgres:
    image: bitnami/postgresql:16
    container_name: postgres_container
    environment:
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=********
      - POSTGRESQL_DATABASE=database
      - TZ=Asia/Seoul
    volumes:
      - ./data:/bitnami/postgresql
    ports:
      - "5432:5432"
    restart: always

volumes:
  postgres_data:
    driver: local

 

그 후에는 위에서 만든 덤프 파일을 postgres 컨테이너 안으로 옮겨주고, 새로운 postgres에 씌워주면 된다.

# 덤프 파일을 컨테이너 안으로 복사
docker cp <mydump.sql> <컨테이너 ID>:<경로>

# 컨테이너 내부로 접속
docker exec -it <컨테이너 ID> sh

# 덤프 파일 실행
psql -U postgres -d postgres -f <mydump.sql>

 

마지막으로 슈퍼 유저로 postgres에 접속한 뒤, 원하는 유저를 생성하고 적절한 권한을 부여하면 끝이다.

-- 새로운 유저 생성
CREATE USER <username> WITH PASSWORD 'your_password';

-- dbo 스키마 권한 부여
GRANT USAGE ON SCHEMA dbo TO <username>;

-- dbo 스키마 내의 모든 객체(테이블, 뷰, 시퀀스 등)에 대한 권한을 부여
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA dbo TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA dbo TO <username>;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA dbo TO <username>;

# dbo 스키마에 새로운 테이블, 시퀀스, 또는 함수가 생성될 때 유저에게 자동으로 권한을 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA dbo GRANT ALL PRIVILEGES ON TABLES TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbo GRANT ALL PRIVILEGES ON SEQUENCES TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA dbo GRANT EXECUTE ON FUNCTIONS TO <username>;