2024. 11. 23. 08:09ㆍ클라우드/GCP
Task 1. Prepare the source database for migration

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 |

sudo su - postgres psql |

\c postgres; CREATE EXTENSION pglogical; \c orders; CREATE EXTENSION pglogical; \c gmemegen_db; CREATE EXTENSION pglogical; |

\l |

Create the database migration user
CREATE USER migration_admin PASSWORD 'DMS_1s_cool!'; ALTER DATABASE orders OWNER TO migration_admin; ALTER ROLE migration_admin WITH REPLICATION; |

\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; |

\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; |

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; |

\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; |

GRANT USAGE ON SCHEMA public TO migration_admin; GRANT ALL ON SCHEMA public TO migration_admin; GRANT SELECT ON public.meme TO migration_admin; |

\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 |

Task 2. Create a Database Migration Service connection profile for a stand-alone PostgreSQL database

Create a new connection profile for the PostgreSQL source instance


Task 3. Create and start a continuous migration job



Create the destination instance





이후 생성될 때까지 대기(꽤 오래 걸림)
Allow access to the postgresql-vm instance from automatically allocated IP range



In the Destination IP range coloum ,copy the IP range (e.g. 10.107.176.0/24) next to peering-route-xxxxx... route.


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

파일 마지막 줄에 다음과 같이 수정

Ctrl-O, Enter, Ctrl-X 입력하여 저장후 nano 종료( : , esc )
sudo systemctl start postgresql@13-main |

Test and start the continuous migration job
In this step, you will test and start the migration job.
Task 4. Confirm the data in Cloud SQL for PostgreSQL

클릭

db 늘어난 것 확인
Connect to the PostgreSQL instance

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

\c orders; |
select * from distribution_centers; |

Update stand-alone source data to test continuous migration
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=us-east4-a --format="value(networkInterfaces[0].accessConfigs[0].natIP)") echo $POSTGRESQL_IP psql -h $POSTGRESQL_IP -p 5432 -d orders -U migration_admin |
\c orders; |
insert into distribution_centers values(-80.1918,25.7617,'Miami FL',11); |
\q |

Connect to the Cloud SQL PostgreSQL database to check that updated data has been migrated
gcloud sql connect postgresql-cloudsql --user=postgres --quiet |

Review data in Cloud SQL for PostgreSQL database
\c orders; |
select * from distribution_centers; |

Task 5. Promote Cloud SQL to be a stand-alone instance for reading and writing data




Note that postgresql-cloudsql is now a stand-alone instance for reading and writing data.
참고 : https://www.cloudskillsboost.google/focuses/22792?parent=catalog
Database Migration Service를 사용하여 PostgreSQL용 Cloud SQL로 마이그레이션하기 | Google Cloud Skills Boost
이 실습에서는 연속 Database Migration Service 작업과 연결을 위한 VPC 피어링을 사용하여, 가상 머신에서 실행 중인 독립형 PostgreSQL 데이터베이스를 PostgreSQL용 Cloud SQL로 마이그레이션합니다.
www.cloudskillsboost.google
'클라우드 > GCP' 카테고리의 다른 글
17. AlloyDB - Database Fundamentals (0) | 2024.11.25 |
---|---|
16. Connect an App to a Cloud SQL for PostgreSQL Instance (0) | 2024.11.24 |
14. Cloud Composer: Copying BigQuery Tables Across Different Locations (0) | 2024.11.22 |
13. Cloud Logging and Monitoring for BigQuery (0) | 2024.11.21 |
그림으로 배우는 구글 클라우드 101 (2) | 2024.11.20 |