22. 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.

 

빨간 박스 부분 다음과 같이 수정

Ctrl-O

Enter

Ctrl-X

 

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

 

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?locale=en&parent=catalog

Migrate to Cloud SQL for PostgreSQL using Database Migration Service | Google Cloud Skills Boost