Migrate to Cloud SQL for PostgreSQL using Database Migration Service

admin

1 year ago

“Migrate to Cloud SQL for PostgreSQL using Database Migration Service”

Daftar Isi

Toggle

Pengantar

Migrasi database melalui Layanan Migrasi Database memerlukan beberapa persiapan database sumber, termasuk membuat pengguna khusus dengan hak replikasi, menambahkan ekstensi database pglogical ke database sumber, dan juga memberikan hak skemata dan tabel di database yang akan dimigrasikan. sebagai database postgres, kepada pengguna tersebut.

Praktikum

Task 1. Prepare the source database for migration

Upgrade the database with the pglogical extension

Buka menu Navigation menu (nav-menu.png), click Compute Engine > VM instances.
klik ssh pada VM postgresql-vm
install pglogical

sudo apt install postgresql-13-pglogical

install dependensi

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

masuk ke psql

sudo su - postgres
psql

buat database

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

cek list db

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

Assign permissions to the migration user

Tambahkan permission untuk pglogical schema dan tables

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

Tambahkan permission untuk pglogical schema dan tables di order DB

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

Tambahkan permission untuk public

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;

Tambahkan permission untuk gmemegen_db database

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

Tambahkan permission Public untuk gmemegen_db database.

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

Eksekusi perintah berikut

\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

\q
exit

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

Get the connectivity information for the PostgreSQL source instance

Klik menu Navigation , click Compute Engine > VM instances.
Copy internal IP pada Vm postgresql-vm

Create a new connection profile for the PostgreSQL source instance

Buka menu Navigation menu > click Database Migration > Connection profiles.
Klik + Create Profile
Pilih Database engine > PostgreSQL.
Untuk Connection profile name -> postgres-vm
Untuk hostname/IP addr -> masukan IP internal yg sudah di copy sebelum nya
Port -> 5432
Username -> migration_admin
Password -> DMS_1s_cool!
Select region
klik Create

Task 3. Create and start a continuous migration job

Create a new continuous migration job

Buka Navigation menu -> click Database Migration > Migration jobs.
Klik + Create Migration Job
Masukan info berikut :

Migration job name : vm-to-cloudsql
Source database engine : PostgreSQL
Destination region : US-East
Destination database engine : Cloud SQL for PostgreSQL
Migration job type : Continuous

Klik Save & Continue

Define the source instance

Pilih Source connection profile -> postgres-vm
klik save & continue

Create the destination instance

Destination Instance ID : postgresql-cloudsql
Root password : supersecret!
Database version : Cloud SQL for PostgreSQL 13
Instance connectivity : Private IP and Public IP
Use an automatically allocated IP range

Klik Allocate & Connect

Machine type : Pilih Standard dan check 1 vCPU, 3.75 GB
Storage type : SSD
Storage capacity : 10 GB

Klik Create & Continue

Define the connectivity method

Connectivity method : VPC peering
VPC : default

Klik Configure & Continue

Allow access to the posgresql-vm instance from automatically allocated IP range

Buka Navigation menu -> right-click VPC network > VPC network peering
Klik servicenetworking-googleapis-com
Pada tab Imported routes -> masukan Ip range ( misal : 10.107.176.0/24)
Pada terminal edit file

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

Pada baris terahir tambahkan script berikut ganti dengan IP yang di peroleh sebelumnya

#GSP918 - allow access to all hosts
host    all all 10.107.176.0/24   md5

Restart service postgre

sudo systemctl start postgresql@13-main

Test and start the continuous migration job

pada menu Database Migration Service -> klik test job
Klik Create & Start Job

Task 4. Confirm the data in Cloud SQL for PostgreSQL

Klik Navigation menu -> click Databases > SQL
Buka postgresql-cloudsql-master
Buka instance postgresql-cloudsql (PostgreSQL read replica).
Pada menu Replica Instance click Databases

Connect to the PostgreSQL instance

Pada Replica Instance click Overview
Scroll ke bawah Connect to this instance section and click Open Cloud Shell

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

Masukan password

supersecret!

Review the data in the Cloud SQL for PostgreSQL instance

\c orders;
supersecret!
select * from distribution_centers;

exit -> \q

Update stand-alone source data to test continuous migration

Buka cloud Shell

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=(zone) --format="value(networkInterfaces[0].accessConfigs[0].natIP)")
echo $POSTGRESQL_IP
psql -h $POSTGRESQL_IP -p 5432 -d orders -U migration_admin

Masukan password

DMS_1s_cool!

Pada psql

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

Review data in Cloud SQL for PostgreSQL database

\c orders;
supersecret!
select * from distribution_centers;

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

Buka Navigation menu , click Database Migration > Migration jobs
Klik vm-to-cloudsql -> klik promote
Klik Navigation menu , click Databases > SQL

Penutup

Sahabat Blog Learning & Doing demikianlah penjelasan mengenai Migrate to Cloud SQL for PostgreSQL using Database Migration Service. Semoga Bermanfaat . Sampai ketemu lagi di postingan berikut nya.