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》

上一篇:浅谈SQL Server 对于内存的管理


下一篇:VSS备份邮件后导致空间不匹配