标签
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》