[Qwiklabs] GSP355 - Manage PostgreSQL Databases on Cloud SQL: Challenge Lab

[Qwiklabs] GSP355 - Manage PostgreSQL Databases on Cloud SQL: Challenge Lab

[Qwiklabs] GSP355 - Manage PostgreSQL Databases on Cloud SQL: Challenge Lab

2022/08/28
Cover Image for [Qwiklabs] GSP355 - Manage PostgreSQL Databases on Cloud SQL: Challenge Lab

The boss stage of “Manage PostgreSQL Databases on Cloud SQL” quest

Task 1. Migrate a stand-alone PostgreSQL database to a Cloud SQL for PostgreSQL instance

考 GSP918 的內容，但有些沒涵蓋到

Prepare the stand-alone PostgreSQL database for migration

  1. Enable the Google Cloud APIs required for Database Migration Services

Database Migration
ENABLE

  1. Upgrade pglogical database extension on vm

Compute Engine > VM instance
postgresql-vm -> SSH

cmd time!

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;
  1. Create user

!!記得把底下所有 {user} 改成 quest 指定的 Migration user name!!

CREATE USER {user} PASSWORD 'DMS_1s_cool!';
ALTER DATABASE orders OWNER TO {user};
ALTER ROLE {user} WITH REPLICATION;
  1. Grant the user some privileges

!!記得把底下所有 {user} 改成 quest 指定的 Migration user name!!

\c postgres;
GRANT USAGE ON SCHEMA pglogical TO {user};
GRANT ALL ON SCHEMA pglogical TO {user};
GRANT SELECT ON pglogical.tables TO {user};
GRANT SELECT ON pglogical.depend TO {user};
GRANT SELECT ON pglogical.local_node TO {user};
GRANT SELECT ON pglogical.local_sync_status TO {user};
GRANT SELECT ON pglogical.node TO {user};
GRANT SELECT ON pglogical.node_interface TO {user};
GRANT SELECT ON pglogical.queue TO {user};
GRANT SELECT ON pglogical.replication_set TO {user};
GRANT SELECT ON pglogical.replication_set_seq TO {user};
GRANT SELECT ON pglogical.replication_set_table TO {user};
GRANT SELECT ON pglogical.sequence_state TO {user};
GRANT SELECT ON pglogical.subscription TO {user};

\c orders;
GRANT USAGE ON SCHEMA pglogical TO {user};
GRANT ALL ON SCHEMA pglogical TO {user};
GRANT SELECT ON pglogical.tables TO {user};
GRANT SELECT ON pglogical.depend TO {user};
GRANT SELECT ON pglogical.local_node TO {user};
GRANT SELECT ON pglogical.local_sync_status TO {user};
GRANT SELECT ON pglogical.node TO {user};
GRANT SELECT ON pglogical.node_interface TO {user};
GRANT SELECT ON pglogical.queue TO {user};
GRANT SELECT ON pglogical.replication_set TO {user};
GRANT SELECT ON pglogical.replication_set_seq TO {user};
GRANT SELECT ON pglogical.replication_set_table TO {user};
GRANT SELECT ON pglogical.sequence_state TO {user};
GRANT SELECT ON pglogical.subscription TO {user};

GRANT USAGE ON SCHEMA public TO {user};
GRANT ALL ON SCHEMA public TO {user};
GRANT SELECT ON public.distribution_centers TO {user};
GRANT SELECT ON public.inventory_items TO {user};
GRANT SELECT ON public.order_items TO {user};
GRANT SELECT ON public.products TO {user};
GRANT SELECT ON public.users TO {user};
\c orders;
\dt
ALTER TABLE public.distribution_centers OWNER TO {user};
ALTER TABLE public.inventory_items OWNER TO {user};
ALTER TABLE public.order_items OWNER TO {user};
ALTER TABLE public.products OWNER TO {user};
ALTER TABLE public.users OWNER TO {user};
\dt
  1. Add primary key

我這邊只觀察到 inventiory_items 沒有 primary key，所以只打一行

ALTER TABLE inventory_items ADD PRIMARY KEY (id);

Migrate the stand-alone PostgreSQL database to a Cloud SQL for PostgreSQL instance

接下來的部分，不知道為什麼 Loading 都很久，但是切到其他 tab 再回來卻顯示完成了，懷疑他們程式有 bug。
總之如果轉圈圈轉太久，試著切到其他 tab 再回來

  1. Create new DMS

Database Migration > Connection profile (如果一直被導向啟用 API，試著重新登入)
CREATE PROFILE

  1. Create new continuous DMS job

S1

S2

S3
Follow the quest instruction!!! (特別注意 Destination Instance ID 與 Database Version)


S4 (Need to wait S3 to be completed)

S5
omitted

  1. Test & Start the job

START

稍等一下等到 Status 變成 Running CDC in progress 就可以回去按 check progress 了

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

進 Database Migration 給他按 Promote ，稍等一下就完成了，EZ

Task 3. Implement Cloud SQL for PostgreSQL IAM database authentication

GSP920 Task 1, 3

  1. Allow public ip of postgres-vm to sql instance

SQL > (instance) > Connections

  1. Create CloudIAM user

SQL > (instance) > Users
Add user account
選擇 Cloud IAM，Principle 填自己的 account name (Username)

  1. Grant SELECT permission to the Cloud IAM user

SQL > (instance) > Overview，滑下去找 OPEN CLOUD SELL 以連線
密碼是 supersecret!

\c orders

密碼一樣是 supersecret!

底下{Database Table} 替換成 Database Table to be secured with IAM Authentication、 {IAM UserName} 換成自己的 account name (Username)！！最後面引號(")跟分號(;)都要記得!!

GRANT SELECT ON TABLE {Database Table} TO "{IAM Username}";
  1. Test Run as IAM user

(You can omit this part, go check the progress directly 😄)

開啟新的 Cloud Shell
底下 、{IAM UserName} 換成自己的 account name!!

gcloud sql connect {} --user={IAM Username}
SELECT COUNT(*) FROM orders

Task 4. Configure and test point-in-time recovery

  1. Enable backups

In cloud shell,

底下 {SQL Instance Name} 替換成 SQL instance name

export CLOUD_SQL_INSTANCE={SQL Instance Name}
gcloud sql instances describe $CLOUD_SQL_INSTANCE
gcloud sql instances patch $CLOUD_SQL_INSTANCE --backup-start-time=00:00
  1. Enable point-in-time recovery

底下的 --retained-transaction-log-days=X，X 要替換為指定的 Point-in-time recovery retention days

gcloud sql instances patch $CLOUD_SQL_INSTANCE \
 --enable-point-in-time-recovery \
 --retained-transaction-log-days=X

等他跑跑跑

  1. Make a note of the timestamp for the point-in-time you wish to restore to.

紀錄一下現在時間

date --rfc-3339=seconds
  1. Make some changes to the database (Add a row of data to the orders.distribution_centers table)

用跟之前同樣的方式連進資料庫 (SQL > Overview: OPEN CLOUD SHELL, psw=supersecret!)

\c orders;

INSERT INTO distribution_centers VALUES(-80.1918,25.7617,'Miami FL',11);
SELECT COUNT(*) FROM distribution_centers;
  1. Use point-in-time recovery to create a clone that replicates the instance state at your chosen timestamp.

底下 {TIMESTAMP_IN_STEP_3} 替換為剛剛 Step 3 印出來的時間，同樣記得引號(')

export NEW_INSTANCE_NAME=postgres-orders-pitr
gcloud sql instances clone $CLOUD_SQL_INSTANCE $NEW_INSTANCE_NAME \
 --point-in-time '{TIMESTAMP_IN_STEP_3}'

等好久好久，跑完就可以去評分了

  1. Task 1. Migrate a stand-alone PostgreSQL database to a Cloud SQL for PostgreSQL instance
    1. Prepare the stand-alone PostgreSQL database for migration
    2. Migrate the stand-alone PostgreSQL database to a Cloud SQL for PostgreSQL instance
  2. Task 2. Promote a Cloud SQL to be a stand-alone instance for reading and writing data
  3. Task 3. Implement Cloud SQL for PostgreSQL IAM database authentication
  4. Task 4. Configure and test point-in-time recovery