15. Migrate to Cloud SQL for PostgreSQL using Database Migration Service

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.

1.In the Database Migration Service tab you open earlier, review the details of the migration job.

2.Click Test Job.

3.After a successful test, click Create & Start 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