Wednesday, May 8, 2024
Cloud SQL GCP Juara GCP Postgresql

Migrate to Cloud SQL for PostgreSQL using Database Migration Service

admin
SQL

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

Daftar Isi

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

Baca Juga :  Cara Setup Cloud Monitoring Uptime Check di GCP

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
Baca Juga :  Cara Membuat Virtual Private Cloud (VPC) Custom di Google Cloud Platform 2021

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.

(Visited 102 times, 1 visits today)

Related Posts:

Similar Posts