[Cloud Skills Boost] Manage PostgreSQL Databases on Cloud SQL ⑤

< Lab >

---

Manage PostgreSQL Databases on Cloud SQL: Challenge Lab

Cloud SQL에서 PostgreSQL 데이터베이스 관리: 챌린지 랩

 

• 독립형 PostgreSQL 데이터베이스를 PostgreSQL용 Cloud SQL 인스턴스로 마이그레이션

1. Database Migration API 및 Service Networking API가 활성화되어 있는지 확인
2. pglogical 확장으로 데이터베이스 업그레이드 :
   Compute Engine - VM 인스턴스 - postgressql-vm의 SSH를 클릭 후 pglogical 데이터베이스 확장 설치 및
   PostgreSQL 구성 파일에 몇 가지 추가 사항을 다운로드하여 적용하고 서비스를 다시 시작
   (pg_hba.conf는 모든 호스트에 대한 액세스를 허용하는 규칙
   postgresql.conf는 pglogical이 모든 주소에서 수신 대기하도록 구성하는 최소 구성 설정)
   

   sudo apt install postgresql-13-pglogical
   
   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

    
3.  데이터베이스 마이그레이션 사용자 생성 :
   psql에서 아래 명령을 입력 하여 복제 역할을 가진 새 사용자를 생성
   

   sudo su - postgres
   psql
   
   \c orders; 
   CREATE EXTENSION pglogical;
   
   CREATE USER import_user PASSWORD 'DMS_1s_cool!';
   ALTER DATABASE orders OWNER TO import_user;
   ALTER ROLE import_user WITH REPLICATION;

   
   
4. 마이그레이션 사용자에게 권한 할당 :
   psql에서 orders 데이터베이스의 public 스키마와 테이블에 권한을 부여
   

   \c orders;
   GRANT USAGE ON SCHEMA public TO import_user;
   GRANT ALL ON SCHEMA public TO import_user;
   GRANT SELECT ON public.distribution_centers TO import_user;
   GRANT SELECT ON public.inventory_items TO import_user;
   GRANT SELECT ON public.order_items TO import_user;
   GRANT SELECT ON public.products TO import_user;
   GRANT SELECT ON public.users TO import_user;

   
   마이그레이션을 테스트할 때 소스 데이터를 편집하도록 migration_admin 사용자를 orders 데이터베이스의 소유자로 변경
   

   \c orders;
   \dt
   ALTER TABLE public.distribution_centers OWNER TO import_user;
   ALTER TABLE public.inventory_items OWNER TO import_user;
   ALTER TABLE public.order_items OWNER TO import_user;
   ALTER TABLE public.products OWNER TO import_user;
   ALTER TABLE public.users OWNER TO import_user;
   \dt

   
   

   

5. 마이그레이션할 모든 테이블의 기본 키 세트 확인 :
   전체 조회
   

   SELECT A.* 
   	 , B.*
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
        , INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
   WHERE 1=1 --A.TABLE_CATALOG   = 'order'
      AND A.CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND A.TABLE_CATALOG   = B.TABLE_CATALOG
      AND A.TABLE_SCHEMA    = B.TABLE_SCHEMA
      AND A.TABLE_NAME      = B.TABLE_NAME
      AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
   ;

   부분 조회
   

   SELECT A.* 
   	 , B.*
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
        , INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
   WHERE 1=1 --A.TABLE_CATALOG   = 'order'
      AND A.TABLE_NAME      = 'order_items'
      AND A.CONSTRAINT_TYPE = 'PRIMARY KEY'
      AND A.TABLE_CATALOG   = B.TABLE_CATALOG
      AND A.TABLE_SCHEMA    = B.TABLE_SCHEMA
      AND A.TABLE_NAME      = B.TABLE_NAME
      AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
   ;

   
   

• Cloud SQL을 데이터 읽기 및 쓰기를 위한 독립 실행형 인스턴스로 승격

 

• PostgreSQL IAM 데이터베이스 인증을 위한 Cloud SQL 구현

 

• 특정 시점 복구 구성 및 테스트