Greenplum邏輯備份增強-備份期間不鎖metadata(pg_class)，不堵塞DDL
標籤
PostgreSQL , 邏輯備份 , 一致性 , 堵塞DDL , 鎖後設資料
背景
邏輯備份為了保障庫級別的全域性一致性，使用了MVCC的機制來保障。
需要鎖後設資料(catalog AccessShareLock)(同時對備份物件加AccessShareLock鎖)，不允許DROP已有的表，ALTER已有表的表結構，TRUNCATE已有表等操作（只允許AccessShareLock不衝突的操作）。
但是可以在備份啟動，並載入完所有的accessshare lock後，新增表，以及對新增的表做任何DDL DML操作。
postgres=# select relation::regclass,* from pg_locks order by 1;
relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------------------------------------+------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
pg_foreign_data_wrapper_oid_index | relation | 13285 | 112 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_foreign_server_oid_index | relation | 13285 | 113 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_foreign_data_wrapper_name_index | relation | 13285 | 548 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_foreign_server_name_index | relation | 13285 | 549 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_default_acl | relation | 13285 | 826 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_default_acl_role_nsp_obj_index | relation | 13285 | 827 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_default_acl_oid_index | relation | 13285 | 828 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_tablespace | relation | 0 | 1213 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_type | relation | 13285 | 1247 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_attribute | relation | 13285 | 1249 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_proc | relation | 13285 | 1255 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_class | relation | 13285 | 1259 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_authid | relation | 0 | 1260 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_database | relation | 0 | 1262 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_foreign_server | relation | 13285 | 1417 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_inherits_parent_index | relation | 13285 | 2187 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_foreign_data_wrapper | relation | 13285 | 2328 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_constraint_conparentid_index | relation | 13285 | 2579 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_am | relation | 13285 | 2601 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_attrdef | relation | 13285 | 2604 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_cast | relation | 13285 | 2605 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_constraint | relation | 13285 | 2606 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_conversion | relation | 13285 | 2607 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_depend | relation | 13285 | 2608 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_description | relation | 13285 | 2609 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_inherits | relation | 13285 | 2611 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_language | relation | 13285 | 2612 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_namespace | relation | 13285 | 2615 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_opclass | relation | 13285 | 2616 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_operator | relation | 13285 | 2617 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_rewrite | relation | 13285 | 2618 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_am_name_index | relation | 13285 | 2651 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_am_oid_index | relation | 13285 | 2652 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_attrdef_adrelid_adnum_index | relation | 13285 | 2656 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_attrdef_oid_index | relation | 13285 | 2657 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_attribute_relid_attnam_index | relation | 13285 | 2658 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_attribute_relid_attnum_index | relation | 13285 | 2659 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_cast_oid_index | relation | 13285 | 2660 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_cast_source_target_index | relation | 13285 | 2661 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_class_oid_index | relation | 13285 | 2662 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_class_relname_nsp_index | relation | 13285 | 2663 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_constraint_conname_nsp_index | relation | 13285 | 2664 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_constraint_conrelid_index | relation | 13285 | 2665 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_constraint_contypid_index | relation | 13285 | 2666 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_constraint_oid_index | relation | 13285 | 2667 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_conversion_default_index | relation | 13285 | 2668 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_conversion_name_nsp_index | relation | 13285 | 2669 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_conversion_oid_index | relation | 13285 | 2670 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_database_datname_index | relation | 0 | 2671 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_database_oid_index | relation | 0 | 2672 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_depend_depender_index | relation | 13285 | 2673 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_depend_reference_index | relation | 13285 | 2674 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_description_o_c_o_index | relation | 13285 | 2675 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_authid_rolname_index | relation | 0 | 2676 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_authid_oid_index | relation | 0 | 2677 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_inherits_relid_seqno_index | relation | 13285 | 2680 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_language_name_index | relation | 13285 | 2681 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_language_oid_index | relation | 13285 | 2682 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_namespace_nspname_index | relation | 13285 | 2684 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_namespace_oid_index | relation | 13285 | 2685 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_opclass_am_name_nsp_index | relation | 13285 | 2686 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_opclass_oid_index | relation | 13285 | 2687 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_operator_oid_index | relation | 13285 | 2688 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_operator_oprname_l_r_n_index | relation | 13285 | 2689 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_proc_oid_index | relation | 13285 | 2690 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_proc_proname_args_nsp_index | relation | 13285 | 2691 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_rewrite_oid_index | relation | 13285 | 2692 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_rewrite_rel_rulename_index | relation | 13285 | 2693 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_tablespace_oid_index | relation | 0 | 2697 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_tablespace_spcname_index | relation | 0 | 2698 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_type_oid_index | relation | 13285 | 2703 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_type_typname_nsp_index | relation | 13285 | 2704 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_opfamily | relation | 13285 | 2753 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_opfamily_am_name_nsp_index | relation | 13285 | 2754 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_opfamily_oid_index | relation | 13285 | 2755 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_db_role_setting | relation | 0 | 2964 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_db_role_setting_databaseid_rol_index | relation | 0 | 2965 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_largeobject_metadata | relation | 13285 | 2995 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_largeobject_metadata_oid_index | relation | 13285 | 2996 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_extension | relation | 13285 | 3079 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_extension_oid_index | relation | 13285 | 3080 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_extension_name_index | relation | 13285 | 3081 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_collation_oid_index | relation | 13285 | 3085 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_collation_name_enc_nsp_index | relation | 13285 | 3164 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_policy | relation | 13285 | 3256 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_policy_oid_index | relation | 13285 | 3257 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_policy_polrelid_polname_index | relation | 13285 | 3258 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_statistic_ext_relid_index | relation | 13285 | 3379 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_statistic_ext_oid_index | relation | 13285 | 3380 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_statistic_ext | relation | 13285 | 3381 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_init_privs | relation | 13285 | 3394 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_init_privs_o_c_o_index | relation | 13285 | 3395 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_class_tblspc_relfilenode_index | relation | 13285 | 3455 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_collation | relation | 13285 | 3456 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_event_trigger | relation | 13285 | 3466 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_event_trigger_evtname_index | relation | 13285 | 3467 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_event_trigger_oid_index | relation | 13285 | 3468 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_transform_oid_index | relation | 13285 | 3574 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_transform_type_lang_index | relation | 13285 | 3575 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_transform | relation | 13285 | 3576 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_seclabel | relation | 13285 | 3596 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_seclabel_object_index | relation | 13285 | 3597 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_dict | relation | 13285 | 3600 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_parser | relation | 13285 | 3601 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_config | relation | 13285 | 3602 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_dict_dictname_index | relation | 13285 | 3604 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_dict_oid_index | relation | 13285 | 3605 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_parser_prsname_index | relation | 13285 | 3606 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_parser_oid_index | relation | 13285 | 3607 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_config_cfgname_index | relation | 13285 | 3608 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_config_oid_index | relation | 13285 | 3712 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_template | relation | 13285 | 3764 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_template_tmplname_index | relation | 13285 | 3766 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_ts_template_oid_index | relation | 13285 | 3767 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_statistic_ext_name_index | relation | 13285 | 3997 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_subscription | relation | 0 | 6100 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_publication | relation | 13285 | 6104 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_publication_rel | relation | 13285 | 6106 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_publication_oid_index | relation | 13285 | 6110 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_publication_pubname_index | relation | 13285 | 6111 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_publication_rel_oid_index | relation | 13285 | 6112 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_publication_rel_prrelid_prpubid_index | relation | 13285 | 6113 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_subscription_oid_index | relation | 0 | 6114 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_subscription_subname_index | relation | 0 | 6115 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
pg_roles | relation | 13285 | 11595 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | t
pg_locks | relation | 13285 | 11645 | | | | | | | | 4/1687 | 32897 | AccessShareLock | t | t
test | relation | 13285 | 16384 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
b | relation | 13285 | 16489 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
c | relation | 13285 | 16492 | | | | | | | | 3/1584 | 33022 | AccessShareLock | t | f
邏輯備份通用問題
如果長時間的執行邏輯備份，可能影響一些ETL或者BI型別的業務（這類業務可能會在過程中 truncate 老表，載入資料等）。
建議這類需求，可以換成使用TEMP TABLE來避免鎖衝突。
《PostgreSQL 邏輯備份一致性講解 – Why pg_dump backup a database in consistent status》
Greenplum gpcrondump邏輯備份的問題
Greenplum早期的gpcrondump，在備份期間，需要對pg_class加exclusive鎖，而非使用MVCC的機制來實現後設資料本身的一致性。因此影響非常大。
https://greenplum.org/greenplum-6-jan-2018/
https://greenplum.org/introducing-gpbackup-gprestore/#more-3017
Replacement of gpcrondump with gpbackup. gpbackup improves on gpcrondump in many respects, the most popular being reduced lock contention. The lock contention is reduced because the gpbackup design acts as a regular SQL read only user to the database and uses a transaction to get a point in time, so no heavy handed system locking is required during the job.
Greenplum 4.3, 6引入了gpbackup, gprestore的備份與恢復命令，代替gpcrondump，採用MVCC機制來保障備份的資料，庫級一致性。避免原來需要鎖pg_class exclusive的問題。
(PS: 即便如此，邏輯備份依舊需要注意 前面一個小節提到的通用問題。)
小結
通用邏輯備份，採用MVCC機制以及rr或si隔離級別來做到庫級一致性。
gpdb早期的gpcrondump邏輯備份，需要對pg_class加exclusive鎖，備份期間影響較大。
gpdb 4.3, 6引入了gpbackup, gprestore的備份與恢復命令，代替gpcrondump，採用MVCC機制來保障備份的資料，庫級一致性。避免原來需要鎖pg_class exclusive的問題。
不管哪種方法的邏輯備份，都存在一種問題：如果長時間的執行邏輯備份，可能影響一些ETL或者BI型別的業務（這類業務可能會在過程中 truncate 老表，載入資料等）。 建議這類需求，可以換成使用TEMP TABLE來避免鎖衝突。
參考
https://greenplum.org/introducing-gpbackup-gprestore/#more-3017
http://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gpbackup.html
http://gpdb.docs.pivotal.io/530/utility_guide/admin_utilities/gprestore.html
《PostgreSQL 邏輯備份一致性講解 – Why pg_dump backup a database in consistent status》
相關文章
- Mysql備份與恢復(2)---邏輯備份MySql
- openGauss-邏輯備份
- MongoDB 邏輯備份工具mongodumpMongoDB
- expdp 邏輯備份指令碼指令碼
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- MySQL鎖（一）全域性鎖：如何做全庫的邏輯備份？MySql
- oracle邏輯備份之--資料泵Oracle
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- 圖解MySQL邏輯備份的實現流程圖解MySql
- 達夢DM備份恢復(物理和邏輯)
- Oracle邏輯備份與恢復選項說明Oracle
- 備份集和備份片之間的關係
- mysql全量備份並中文不亂碼MySql
- PostgreSQL邏輯備份pg_dump使用及其原理解析SQL
- QQ 空間備份神器，一鍵備份你所有的青春！
- LightDB-Oracle和LightDB邏輯備份測試對比（十二）Oracle
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- 故障分析 | DDL 導致的 Xtrabackup 備份失敗
- crontab 備份docker mysql映象的資料庫不生效DockerMySql資料庫
- postgresql從入門到精通教程 - 第36講：postgresql邏輯備份SQL
- MySQL5.7 透過邏輯備份遷移到GreatSQL注意事項MySql
- mysql 開發進階篇系列 42 邏輯備份與恢復MySql
- mysql的冷備份與熱備份MySql
- EsgynDB執行備份還原時提示：Snapshot metadata is currently locked
- Mysql備份還有這麼多套路，還不瞭解下？MySql
- 【PG備份恢復】pg_basebackup 多表空間備份恢復測試
- Mysql備份與恢復(1)---物理備份MySql
- 初探MySQL資料備份及備份原理MySql
- 阿里面試官：知道 MySQL 邏輯備份與恢復測試麼？阿里面試MySql
- 備份dockerDocker
- 備份命令
- alias 備份
- Mac時間機器備份加速教程，Time Machine 備份太慢的解決方法Mac
- 群暉NAS備份建議及備份方式
- ManagerDB 備份檔案管理與異地備份
- oracle資料庫備份之exp增量備份Oracle資料庫
- 【RMAN】Oracle12c之後，rman備份Dataguard備端恢復可能出現邏輯錯誤Oracle
- PostgreSQL邏輯備份恢復--pg_dump匯出及psql匯入案例SQL