Migrate your databases to PostgreSQL Database as a Service
วิธีการย้าย Database เดิมของคุณ มาใช้งาน PostgreSQL Database as a Service บน NCS
Prerequisite
Instruction
1
เตรียมความพร้อมของข้อมูล
psql -h [source_host] -U [username] -d postgres -c "ALTER SYSTEM SET default_transaction_read_only = on;"
psql -h [source_host] -U [username] -d postgres -c "SELECT pg_reload_conf();"psql -h [source_host] -U [username] -d postgres -c "SHOW default_transaction_read_only;" default_transaction_read_only
--------------------------------
on2
สั่งสร้างไฟล์ Backup จาก Database ต้นทาง (Export)
psql -h [source_host] -U [username] -d postgres -c "\l"pg_dump -h [source_host] -U [username] -Fc -d [db_name] -f nipa_migration.dumpls -lh nipa_migration.dump-rw-rw-r-- 1 nc-user nc-user 2.8M Jul 9 14:37 nipa_migration.dumpกรณีมีหลาย Database ที่ต้องการ Migrate พร้อมกัน
for db in customer_db order_db payment_db; do
pg_dump -h [source_host] -p [source_port] -U [username] -d "$db" -Fc -f "${db}.dump"
donecustomer_db.dump
order_db.dump
payment_db.dump2.1 ตรวจสอบไฟล์ Dump ก่อน Restore (Pre-restore Validation)
pg_restore -l nipa_migration.dump > dump_contents.txt
cat dump_contents.txt8; 2615 16510 SCHEMA - analytics sourceuser
2; 3079 16390 EXTENSION - pgcrypto
3; 3079 16427 EXTENSION - uuid-ossp
225; 1259 16511 TABLE analytics daily_revenue sourceuser
221; 1259 16462 TABLE public bookings sourceuser
217; 1259 16438 TABLE public customers sourceuser
223; 1259 16485 TABLE public payments sourceuser
219; 1259 16451 TABLE public rooms sourceuser
224; 1259 16504 VIEW public booking_summary sourceuser
272; 1255 16502 FUNCTION public set_updated_at() sourceuser
3366; 2620 16503 TRIGGER public customers trg_customers_updated_at sourceusergrep -c "TABLE DATA" dump_contents.txtpsql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -c \
"SELECT * FROM pg_available_extensions WHERE name IN ('pgcrypto', 'uuid-ossp');" name | default_version | installed_version | comment
-----------+-----------------+--------------------+---------------------------------------------------
uuid-ossp | 1.1 | 1.1 | generate universally unique identifiers (UUIDs)
pgcrypto | 1.3 | 1.3 | cryptographic functions2.1.1 เก็บ Row Count จาก Source ไว้เป็น Baseline
psql -h [source_host] -U [username] -d [db_name] -c \
"SELECT 'public.bookings' AS table_name, COUNT(*) FROM public.bookings
UNION ALL SELECT 'public.customers', COUNT(*) FROM public.customers
UNION ALL SELECT 'analytics.daily_revenue', COUNT(*) FROM analytics.daily_revenue;" table_name | count
--------------------------+-------
public.customers | 10000
public.bookings | 50000
analytics.daily_revenue | 31
(3 rows)2.1.2 เก็บ Checksum จาก Source ไว้เป็น Baseline
psql -h [source_host] -U [username] -d [db_name] -c \
"SELECT md5(string_agg(t::text, '' ORDER BY t::text)) FROM (SELECT * FROM public.customers) t;" md5
----------------------------------
5e2c037955c70c88118f65c3a0b3370b
(1 row)3
เตรียม Database ปลายทางบน NCS ก่อน Restore
3.1 กรณีต้องการอัปเดตเฉพาะข้อมูลใหม่ โดยไม่แตะข้อมูลเดิม (Insert-Only Sync)
pg_dump -h [source_host] -U [username] -d [db_name] \
--data-only \
--inserts \
--on-conflict-do-nothing \
--table=public.customers \
--table=public.bookings \
-f nipa_data_update.sqlgrep -E "DROP TABLE|CREATE TABLE|INSERT INTO" nipa_data_update.sql | head -5psql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -f nipa_data_update.sql4
นำข้อมูลเข้าสู่ Database บน NCS
pg_restore -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] --clean --if-exists --no-owner --no-privileges -j 4 nipa_migration.dumppsql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -c "\dt public.*"
psql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -c "\dt analytics.*"
psql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -c "\dv" List of tables
Schema | Name | Type | Owner
--------+-----------+-------+---------
public | bookings | table | db-user
public | customers | table | db-user
public | payments | table | db-user
public | rooms | table | db-user
(4 rows)
List of tables
Schema | Name | Type | Owner
-----------+---------------+-------+---------
analytics | daily_revenue | table | db-user
(1 row)5
ตรวจสอบความถูกต้องและตั้งค่าเพิ่มเติม
psql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -c \
"SELECT 'public.customers' AS table_name, COUNT(*) FROM public.customers
UNION ALL SELECT 'analytics.daily_revenue', COUNT(*) FROM analytics.daily_revenue;"psql -h [nipa_db_endpoint] -U [nipa_db_user] -d [db_name] -c \
"SET timezone = 'UTC'; SELECT md5(string_agg(t::text, '' ORDER BY t::text)) FROM (SELECT * FROM public.customers) t;"ก่อนตั้ง timezone = 'UTC': customers checksum = c0fd14ebc79c23704fbb8c2b8addbd2a (ไม่ตรงกับ Baseline)
หลังตั้ง timezone = 'UTC': customers checksum = 5e2c037955c70c88118f65c3a0b3370b (ตรงกับ Baseline)PreviousMigrate your databases to MySQL Database as a ServiceNextHaving Problem Before Access a Project
Last updated