#GSP918 - added configuration for pglogical database extension
wal_level = logical # minimal, replica, or logical
max_worker_processes = 10 # one per database needed on provider node
# one per node needed on subscriber node
max_replication_slots = 10 # one per node needed on provider node
max_wal_senders = 10 # one per node needed on provider node
shared_preload_libraries = 'pglogical'
max_wal_size = 1GB
min_wal_size = 80MB
listen_addresses = '*' # what IP address(es) to listen on, '*' is all
\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;
在 psql 中,授予 orders 資料庫的 pglogical 結構定義和資料表權限:
\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;
在 psql 中,授予 orders 資料庫的 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;
\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;
在 psql 中,授予 gmemegen_db 資料庫的 public 結構定義和資料表權限:
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
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+-------
public | distribution_centers | table | migration_admin
public | inventory_items | table | migration_admin
public | order_items | table | migration_admin
public | products | table | migration_admin
public | users | table | migration_admin
(5 rows)
結束 psql 和 postgres 使用者工作階段:
\q
exit
點選「Check my progress」,確認目標已達成。
為 PostgreSQL 來源執行個體做好遷移準備。
工作 2:為獨立 PostgreSQL 資料庫,建立資料庫移轉服務連線設定檔
在這個工作中,您將建立 PostgreSQL 來源執行個體的連線設定檔。
取得 PostgreSQL 來源執行個體的連線資訊
在這個步驟中,您將找出要遷移至 Cloud SQL 的來源資料庫執行個體內部 IP 位址。
在 Google Cloud 控制台的「導覽選單」() 中,依序點選「Compute Engine」>「VM 執行個體」。
longitude | latitude | name | id
-----------+----------+---------------------------------------------+----
-89.9711 | 35.1174 | Memphis TN | 1
-87.6847 | 41.8369 | Chicago IL | 2
-95.3698 | 29.7604 | Houston TX | 3
-118.25 | 34.05 | Los Angeles CA | 4
-90.0667 | 29.95 | New Orleans LA | 5
-73.7834 | 40.634 | Port Authority of New York/New Jersey NY/NJ | 6
-75.1667 | 39.95 | Philadelphia PA | 7
-88.0431 | 30.6944 | Mobile AL | 8
-79.9333 | 32.7833 | Charleston SC | 9
-81.1167 | 32.0167 | Savannah GA | 10
Labs create a Google Cloud project and resources for a fixed time
Labs have a time limit and no pause feature. If you restart it, you'll have to start from the beginning.
On the top left of your screen, click Start lab to begin
Use private browsing
Copy the provided Username and Password for the lab
Click Open console in private mode
Sign in to the Console
Sign in using your lab credentials. Using other credentials might cause errors or incur charges.
Accept the terms, and skip the recovery resource page
Don't click End lab unless you've finished the lab or want to restart it, as it will clear your work and remove the project
此内容目前不可用
一旦可用,我们会通过电子邮件告知您
太好了!
一旦可用,我们会通过电子邮件告知您
One lab at a time
Confirm to end all existing labs and start this one
Setup your console before you begin
Use an Incognito or private browser window to run this lab. This
prevents any conflicts between your personal account and the Student
account, which may cause extra charges incurred to your personal account.