[Google Cloud] Google Study Jam : Create and Manage Cloud SQL for PostgreSQL Instances (1)

본 포스트는 2024년 Google Study Jam을 공부하면서 개인적으로 내용을 정리한 포스트 입니다.

 

Database Migration Service에서는 IP 허용 목록, VPC 피어링, 역방향 SSH 터널 등 다양한 연결 옵션을 사용하여 데이터로 Cloud SQL로 마이그레이션하는 일회성 작업 및 연속 작업 옵션을 제공한다.

 

Configure connectivity  |  Database Migration Service  |  Google Cloud

 

Task1. 마이그레이션을 위해 소스 데이터베이스 준비하기

Google Cloud 콘솔의 탐색 메뉴에서 Compute Engine > VM 인스턴스 > Connect > SSH에서 다음 명령어를 입력해서 pglogical 데이터베이스 확장 프로그램 설치

• 논리적 복제 시스템으로, 전체가 PostgreSQL 확장 프로그램으로 구현되어 있다.
• 완전히 통합되어 있기 때문에 트리거나 외부 프로그램이 필요하지 않다.
• 물리적 복제에 대한 이러한 대안은 선택적 복제를 위해 게시/구독 모델을 사용하여 데이터를 복제하는 매우 효율적인 방법이다.

sudo apt install postgresql-13-pglogical

 

GitHub - 2ndQuadrant/pglogical: Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Post

 

 

pglogical 확장 프로그램 사용 설정을 위해 PostgreSQL 구성 파일에 몇 가지 추가 항목을 다운하여 적용하고 postgresql 서비스를 다시 시작한다.

sudo su - postgres -c "gsutil cp gs://cloud-training/gsp918/pg_hba_append.conf ."
sudo su - postgres -c "gsutil cp gs://cloud-training/gsp918/postgresql_append.conf ."
sudo su - postgres -c "cat pg_hba_append.conf >> /etc/postgresql/13/main/pg_hba.conf"
sudo su - postgres -c "cat postgresql_append.conf >> /etc/postgresql/13/main/postgresql.conf"

sudo systemctl restart postgresql@13-main

 

pg_hba.conf에 모든 호스트에 대한 액세스를 허용하는 규칙을 추가한다.

#GSP918 - allow access to all hosts
host    all all 0.0.0.0/0   md5

 

pg_hba.conf에 pglogical이 모든 주소에서 수신 대기하도록 구성하기 위한 최소 구성을 설정한다.

#GSP918 - added configuration for pglogical database extension

wal_level = logical         # minimal, replica, or logical
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'
max_wal_size = 1GB
min_wal_size = 80MB

listen_addresses = '*'         # what IP address(es) to listen on, '*' is all

 

psql 도구를 실행하고 postgres, orders, gmemegen_db 데이터베이스에 pglogical 데이터베이스 확장 프로그램을 추가한다.

sudo su - postgres

psql

\c postgres;

CREATE EXTENSION pglogical;

\c orders;

CREATE EXTENSION pglogical;

\c gmemegen_db;

CREATE EXTENSION pglogical;

 

서버 PostgreSQL 데이터베이스를 나열한다.

\l

 

 

데이터베이스 마이그레이션 사용자를 만들기 위해 다음 명령어를 입력한다.

CREATE USER migration_admin PASSWORD 'DMS_1s_cool!';
ALTER DATABASE orders OWNER TO migration_admin;
ALTER ROLE migration_admin WITH REPLICATION;

 

 

그 다음 권한 할당을 위해 다음 명령어를 입력한다.

 

1. psql에서 pglogical 스키마 및 pstgres 데이터베이스의 테이블에 대한 권한을 부여한다.

\c postgres;

GRANT USAGE ON SCHEMA pglogical TO migration_admin;
GRANT ALL ON SCHEMA pglogical TO migration_admin;

GRANT SELECT ON pglogical.tables TO migration_admin;
GRANT SELECT ON pglogical.depend TO migration_admin;
GRANT SELECT ON pglogical.local_node TO migration_admin;
GRANT SELECT ON pglogical.local_sync_status TO migration_admin;
GRANT SELECT ON pglogical.node TO migration_admin;
GRANT SELECT ON pglogical.node_interface TO migration_admin;
GRANT SELECT ON pglogical.queue TO migration_admin;
GRANT SELECT ON pglogical.replication_set TO migration_admin;
GRANT SELECT ON pglogical.replication_set_seq TO migration_admin;
GRANT SELECT ON pglogical.replication_set_table TO migration_admin;
GRANT SELECT ON pglogical.sequence_state TO migration_admin;
GRANT SELECT ON pglogical.subscription TO migration_admin;

 

 

2. psql에서 pglogical 스키마 및 orders 데이터베이스의 테이블에 대한 권한을 부여한다.

\c orders;

GRANT USAGE ON SCHEMA pglogical TO migration_admin;
GRANT ALL ON SCHEMA pglogical TO migration_admin;

GRANT SELECT ON pglogical.tables TO migration_admin;
GRANT SELECT ON pglogical.depend TO migration_admin;
GRANT SELECT ON pglogical.local_node TO migration_admin;
GRANT SELECT ON pglogical.local_sync_status TO migration_admin;
GRANT SELECT ON pglogical.node TO migration_admin;
GRANT SELECT ON pglogical.node_interface TO migration_admin;
GRANT SELECT ON pglogical.queue TO migration_admin;
GRANT SELECT ON pglogical.replication_set TO migration_admin;
GRANT SELECT ON pglogical.replication_set_seq TO migration_admin;
GRANT SELECT ON pglogical.replication_set_table TO migration_admin;
GRANT SELECT ON pglogical.sequence_state TO migration_admin;
GRANT SELECT ON pglogical.subscription TO migration_admin;

 

 

3. psql에서 public 스키마 및 orders 데이터베이스의 테이블에 대한 권한을 부여한다.

GRANT USAGE ON SCHEMA public TO migration_admin;
GRANT ALL ON SCHEMA public TO migration_admin;

GRANT SELECT ON public.distribution_centers TO migration_admin;
GRANT SELECT ON public.inventory_items TO migration_admin;
GRANT SELECT ON public.order_items TO migration_admin;
GRANT SELECT ON public.products TO migration_admin;
GRANT SELECT ON public.users TO migration_admin;

 

 

4. psql에서 pglogical 스키마 및 gmemegen_db 데이터베이스의 테이블에 대한 권한을 부여한다.

\c gmemegen_db;

GRANT USAGE ON SCHEMA pglogical TO migration_admin;
GRANT ALL ON SCHEMA pglogical TO migration_admin;

GRANT SELECT ON pglogical.tables TO migration_admin;
GRANT SELECT ON pglogical.depend TO migration_admin;
GRANT SELECT ON pglogical.local_node TO migration_admin;
GRANT SELECT ON pglogical.local_sync_status TO migration_admin;
GRANT SELECT ON pglogical.node TO migration_admin;
GRANT SELECT ON pglogical.node_interface TO migration_admin;
GRANT SELECT ON pglogical.queue TO migration_admin;
GRANT SELECT ON pglogical.replication_set TO migration_admin;
GRANT SELECT ON pglogical.replication_set_seq TO migration_admin;
GRANT SELECT ON pglogical.replication_set_table TO migration_admin;
GRANT SELECT ON pglogical.sequence_state TO migration_admin;
GRANT SELECT ON pglogical.subscription TO migration_admin;

 

 

5. psql에서 public 스키마 및 gmemegen_db 데이터베이스의 테이블에 대한 권한을 부여합니다.

GRANT USAGE ON SCHEMA public TO migration_admin;
GRANT ALL ON SCHEMA public TO migration_admin;

GRANT SELECT ON public.meme TO migration_admin;

 

 

이제 소스 데이터베이스가 마이그레이션할 준비가 되었다.

migration_admin 사용자에게 부여할 권한은 Database Migration Service가 postgres, orders, gmemegen_db 데이터베이스를 마이그레이션하는 데 필요한 모든 권한이다.

 

나중에 마이그레이션을 테스트할 때 소스 데이터를 편집할 수 있도록 migration_admin 사용자를 orders 데이터베이스의 테이블 소유자로 설정한다.

\c orders;
\dt

ALTER TABLE public.distribution_centers OWNER TO migration_admin;
ALTER TABLE public.inventory_items OWNER TO migration_admin;
ALTER TABLE public.order_items OWNER TO migration_admin;
ALTER TABLE public.products OWNER TO migration_admin;
ALTER TABLE public.users OWNER TO migration_admin;
\dt

 

Task2. 독립형 PostgreSQL 데이터베이스에 대한 Database Migration Service 연결 프로필 만들기

탐색 메뉴에서 Compute Engine > VM 인스턴스> postgresql-vm의 Internal IP를 복사

 

탐색 메뉴에서 데이터베이스 마이그레이션 > 연결 프로필 > 프로필 만들기

• 데이터 베이스 엔진 : PostgreSQL
• 연결 프로필 이름 : postgres-vm
• host 이름 : 복사한 Internal IP
• port : 5432
• 사용자 이름 : migration_admin
• 비밀번호 : DMS_1s_cool!
• 리전 : (설정된 리전)

 

Task3. 연속 마이그레이션 작업 만들기 및 시작하기

탐색 메뉴에서 데이터베이스 마이그레이션 > 마이그레이션 작업 > 마이그레이션 작업 만들기

• 작업 이름 : vm-to-cloudsql
• 소스 데이터베이스 엔진 : PostgreSQL
• 리전 : (설정된 리전)
• 대상 데이터베이스 엔진 : PostgreSQL용 Cloud SQL
• 마이그레이션 작업 유형 : 연속

 

 

소스 연결 프로필 > postgres-vm 선택 > 저장 및 계속

 

마이그레이션을 위한 대상 인스턴스 만들기

• 대상 인스턴스 ID : postgresql-cloudsql
• 비밀번호 : supersecret!
• Cloud SQL 버전 선택 : Enterprise
• 데이터베이스 버전 : PostgreSQL Cloud SQL 13
• 리전 및 영역 선택 : 단일 영역, (설정된 zone)을 기본 영역으로 선택
• 인스턴스 연결 : 비공개 IP 및 공개 IP 선택
• 자동으로 할당된 IP 범위 사용
• 머신형태 : vCPU 1개, 3.75GB
• 스토리지 유형 : SSD
• 스토리지 용량 : 10GB
• 연결 방법 : VPC 피어링
• VPC : 기본값

 

 

 

탐색 메뉴 > VPC 네트워크 > VPC 네트워크 피어링을 마우스 오른쪽 클릭으로 새탭으로 열고 servicenetworking-googleapis-com 항목을 클릭한 후 EFFECTIVE ROUTES VIEW 클릭

 

EFFECTIVE ROUTES 탭에서 peering-route-xxxx의  Destination IP range를 선택해서 복사하고 VM 인스턴스의 터미널 세션에서 다음과 같이 pg_hba.conf 파일 수정.

 

sudo nano /etc/postgresql/13/main/pg_hba.conf

 

다음을 파일 마지막 줄에 입력

#GSP918 - allow access to all hosts
host    all all 0.0.0.0/0   md5

 

모든 IP 주소 범위를 위 3번에서 복사한 범위로 바꾼다.

#GSP918 - allow access to all hosts
host    all all 10.107.176.0/24   md5

Ctrl-O, Enter, Ctrl-X를 눌러 저장하고 nano 편집기를 저장하고 PostgreSQL 서비스를 다시 시작하는 명령어를 실행.

sudo systemctl start postgresql@13-main

 

Test Job 이후 CREATE & START JOB 누르기

 

 

탐색 메뉴 > 데이터베이스 마이그레이션 > 마이그레이션 작업 > vm-to-cloudsql을 클릭하고 세부정보 페이지를 표시

 

마이그레이션 작업 상태를 검토

• Running으로 변경되면 다음 작업을 진행

 

Task4. PostgreSQL 용 Cloud SQL에서 데이터 확인하기

탐색 메뉴 > 데이터베이스 > SQL > postgresql-cloudsql-master라는 인스턴스 ID를 확장한다.

 

postgresql-cloudsql(PostgreSQL 읽기 복제본) 인스턴스를 클릭한다.

 

복제 인스턴스 메뉴에서 데이터베이스를 클릭

 

postgres, orders, gmemegen_db라는 데이터베이스가 Cloud SQL로 마이그레이션되었음을 알 수 있다.

복제본 인스턴스 메뉴에서 개요를 클릭하고 이 인스턴스에 연결 섹션까지 아래로 스크롤하고 Cloud Shell을 열기를 클릭하면 자동 입력된다.

gcloud sql connect postgresql-cloudsql --user=postgres --quiet

 

명령어를 실행한다.

 

메시지가 표시되면 API에 대해 승인을 클릭하고 비밀번호를 입력한다.

 

PostgreSQL 대화형 콘솔에서 명령어를 통해 데이터 검토

\c orders;

supersecret!

select * from distribution_centers;

 

연속 마이그레이션을 테스트하기 위해 독립형 소스 데이터 업데이트를 하려면 다음과 같은 절차를 따르면된다.

 

1. Cloud Shell에서 명령어 입력하여 소스 PostgreSQL 인스턴스에 연결

export VM_NAME=postgresql-vm
export PROJECT_ID=$(gcloud config list --format 'value(core.project)')
export POSTGRESQL_IP=$(gcloud compute instances describe ${VM_NAME} \
  --zone=(zone) --format="value(networkInterfaces[0].accessConfigs[0].natIP)")
echo $POSTGRESQL_IP
psql -h $POSTGRESQL_IP -p 5432 -d orders -U migration_admin

 

2. 비밀번호 입력

DMS_1s_cool!

 

3. psql에서 다음 명령어 입력

\c orders;
insert into distribution_centers values(-80.1918,25.7617,'Miami FL',11);

 

업데이트된 데이터가 마이그레이션되었는지 확인하기 위해 Cloud SQL PostgreSQL 데이터베이스와 연결한다.

 

1. Cloud Shell에 명령어를 입력해 대상 Cloud SQL PostgreSQL 인스턴스에 연결한다.

gcloud sql connect postgresql-cloudsql --user=postgres --quiet

 

2. Cloud SQL 인스턴스의 비밀번호를 입력한다.

supersecret!

 

 

PostgreSQL용 Cloud SQL 데이터베이스의 데이터 검토하기 위해 다음과 같은 절차를 따른다.

 

1. Cloud Shell의 PostgreSQL 대화형 콘솔에서 활성 데이터베이스를 선택한다.

\c orders;

 

2. Cloud SQL 인스턴스의 비밀번호를 입력한다.

supersecret!

 

3. distribution_centers 테이블을 쿼리합니다.

select * from distribution_centers;

 

Task5. Cloud SQL을 데이터 읽기 및 쓰기를 위한 독립형 인스턴스로 승격하기

탐색 메뉴 > 데이터베이스 마이그레이션>마이그레이션 작업 > vm-to-cloudsql을 클릭하고 PROMOTE을 클릭

 

탐색 메뉴 > 데이터베이스 > SQL > postgresql-cloudsql은 데이터 읽기 및 쓰기를 위한 독립형 인스턴스가 됨.