Skip to content

Latest commit

 

History

History
235 lines (185 loc) · 7.38 KB

GSP318.md

File metadata and controls

235 lines (185 loc) · 7.38 KB

GSP318 - Migrate to Cloud SQL for PostgreSQL using Database Migration Service

Setup

  • Enable service networking API

Task 1. Prepare the source database for migration

Upgrade the database with the pglogical extension

  1. Connect instance with SSH

  2. Install extension

    sudo apt install postgresql-13-pglogical

  3. Download tambahan

    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

  4. Masuk ke psql

    sudo su - postgres
psql

  5. Buat extension

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

Create the database migration user

  1. buat 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

  1. Kasih permission

    \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

  2. Exit psql

    \q
exit

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

Navigation menu (Navigation menu icon), click Compute Engine > VM instances

  1. Navigation menu > Compute Engine > VM instances > postgresql-vm , copy ip internal = 10.138.0.2

Create a new connection profile for the PostgreSQL source instance

  1. Navigation menu > Database Migration > Connection profiles
  2. + Create Profile
    • Database Engine: PostgreSQL
    • Connection Profile Name: postgres-vm
    • Hostname or IP address: <internal-ip>
    • Port: 5432
    • Username: migration_admin
    • Password: DMS_1s_cool!i
    • Region: us-west1

Task 3. Create and start a continuous migration job

Create a new continuous migration job

  1. Navigation menu > Database Migration > Migration jobs
  2. + Create Migration Job
    • Migration job name: vm-to-cloudsql
    • Source database engine: PostgreSQL
    • Destination region: us-west1
    • Destination database engine: Cloud SQL for PostgreSQL
    • Migration job type: Continuous

Define the source instance

  • postgres-vm

Create the destination instance

  1. Destination instance
    • Destination instance ID: postgresql-cloudsql
    • Root password: supersecret!
    • Database version: Cloud SQL for PostgreSQL 13
    • Zone: us-west1-c
    • Instance connectivity: Private IP and Public IP
    • Use an automatically allocated IP range
    • klik Allocate and connect
    • Machine type: Standard , 1 vCPU, 3.75 GB
    • Storage type: SSD
    • Storage capacity: 10 GB
    • klik Create & Continue

Define the connectivity method

  1. Tentukan connectivity method
    • Connectivity method: VPC peering
    • VPC network: default
    • Configure and continue

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

  1. Navigation menu > VPC network > VPC network peering

  2. klik servicenetworking-googleapis-com

  3. copy ip range = 10.76.144.0/24

  4. Edit pg_hba.conf

    sudo nano /etc/postgresql/13/main/pg_hba.conf
    #GSP918 - allow access to all hosts
host    all all 10.107.176.0/24   md5

  5. Restart postgresql

    sudo systemctl restart postgresql@13-main

Test and start the continuous migration job

  1. Database Migration Service
  2. Test Job
  3. Create and start job > Create and start

Task 4. Confirm the data in Cloud SQL for PostgreSQL

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