[Google Cloud] Google Study Jam : Create and Manage AlloyDB Instances (3)

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

 

Task1. Verify Data in Source Instance for Migration

1. Navigation Menu > Compute Engine > VM instances > pg14-source > Connect > SSH에서 터미널을 열고 다음 명령어들을 입력한다.

sudo -u postgres psql

\dt

select count (*) as countries_row_count from countries;
select count (*) as departments_row_count from departments;
select count (*) as employees_row_count from employees;
select count (*) as jobs_row_count from jobs;
select count (*) as locations_row_count from locations;
select count (*) as regions_row_count from regions;

 

 

Task2. Create a database DMP file using pg_dump

1. pg14-source terminal을 열고 다음을 입력한다.

• 이전에 살펴본 HR 테이블이 포함된 PostgreSQL DB의 DMP 파일을 만든다.
• -Fc 플래그는 사용자 지정 DMP 파일을 만든다. 

sudo -u postgres pg_dump -Fc postgres > pg14_source.DMP

ls -l -h pg14_source.DMP

# Google Cloud 버킷으로 마이그레이션
gsutil cp pg14_source.DMP gs://Project ID/pg14_source.DMP

 

Task3. Import DMP file using pg_restore

1. Navigation menu > Databases > AlloyDB for PostgreSQL > Clusters > lab-cluster > lab-instance에서 Private IP를 카피하기.

 

 

2. Compute Engine > VM Instance > alloydb-client > Connection > SSH로 터미널로 열고 다음 명령을 수행

export ALLOYDB=ALLOYDB_ADDRESS
echo $ALLOYDB  > alloydbip.txt 
psql -h $ALLOYDB -U postgres

\dt
\q

 

 

3. 다음 명령어로 DMP 파일을 가져와서 복원을 진행한다.

gsutil cp  gs://Project ID/pg14_source.DMP pg14_source.DMP
pg_restore -l  pg14_source.DMP | sed -E 's/(.* EXTENSION )/; \1/g' >  pg14_source_toc.toc
pg_restore -h $ALLOYDB -U postgres \
  -d postgres \
  -L pg14_source_toc.toc \
  pg14_source.DMP

4. 복원된 것을 확인한다. 

psql -h $ALLOYDB -U postgres

\dt

select count (*) as countries_row_count from countries;
select count (*) as departments_row_count from departments;
select count (*) as employees_row_count from employees;
select count (*) as jobs_row_count from jobs;
select count (*) as locations_row_count from locations;
select count (*) as regions_row_count from regions;