PostgreSQL 逻辑复制限制详解

PostgreSQL 的逻辑复制是一个强大的功能，允许在不同的 PostgreSQL 实例之间实时同步数据。然而，它也有一些重要的限制和约束。本文将详细探讨这些限制，并提供实际的业务场景和解决方案。

概述

逻辑复制通过解析 WAL（Write-Ahead Log）日志中的逻辑变更来实现数据同步。与物理复制不同，逻辑复制具有更大的灵活性，但也带来了一些固有的限制。

1. 数据库模式和 DDL 命令限制

1.1 问题描述

逻辑复制不会自动复制数据库模式和 DDL（数据定义语言）命令，包括：

创建、修改、删除表结构
索引操作
约束变更
触发器定义

1.2 业务场景示例

假设我们有一个电商系统，需要在主库和从库之间进行逻辑复制：

发布者操作订阅者操作

sql

-- 在发布者上创建初始表结构
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建发布
CREATE PUBLICATION ecommerce_pub FOR TABLE orders;

sql

-- 在订阅者上手动创建相同的表结构
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建订阅
CREATE SUBSCRIPTION ecommerce_sub 
CONNECTION 'host=publisher_host dbname=ecommerce user=repl_user' 
PUBLICATION ecommerce_pub;

1.3 模式变更问题

当发布者的模式发生变更时，需要手动同步：

sql

-- 发布者上添加新列
ALTER TABLE orders ADD COLUMN discount_amount DECIMAL(10,2) DEFAULT 0;

-- 插入数据（新列包含数据）
INSERT INTO orders (user_id, product_id, quantity, discount_amount) 
VALUES (1001, 5001, 2, 10.50);

如果订阅者没有同步更新模式，复制将失败：

WARNING

复制错误示例

ERROR: relation "orders" does not have attribute "discount_amount"
CONTEXT: during apply of UPDATE for table "orders"

1.4 解决方案

方案一：先更新订阅者模式

sql

-- 1. 首先在订阅者上添加新列
ALTER TABLE orders ADD COLUMN discount_amount DECIMAL(10,2) DEFAULT 0;

-- 2. 然后在发布者上添加新列
ALTER TABLE orders ADD COLUMN discount_amount DECIMAL(10,2) DEFAULT 0;

方案二：使用 pg_dump 同步模式

bash

# 导出发布者的模式
pg_dump --schema-only -h publisher_host -U repl_user ecommerce > schema.sql

# 在订阅者上应用模式变更
psql -h subscriber_host -U repl_user ecommerce -f schema.sql

2. 序列数据不复制

2.1 问题描述

序列（SEQUENCE）的当前值不会通过逻辑复制同步，这影响：

SERIAL 类型列
IDENTITY 列
显式创建的序列

2.2 业务场景示例

sql

-- 发布者上的表
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,  -- 使用序列生成主键
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 插入数据
INSERT INTO customers (name, email) VALUES 
('张三', '[email protected]'),
('李四', '[email protected]'),
('王五', '[email protected]');

-- 查看序列当前值
SELECT last_value FROM customers_id_seq;  -- 返回: 3

在订阅者上：

sql

-- 数据已复制
SELECT * FROM customers;
/*
 id | name | email
----+------+----------------------
  1 | 张三  | [email protected]
  2 | 李四  | [email protected]  
  3 | 王五  | [email protected]
*/

-- 但序列值仍为初始值
SELECT last_value FROM customers_id_seq;  -- 返回: 1

2.3 故障转移问题

如果需要将订阅者提升为主库：

sql

-- 在订阅者上插入新数据会导致主键冲突
INSERT INTO customers (name, email) VALUES ('赵六', '[email protected]');
-- ERROR: duplicate key value violates unique constraint "customers_pkey"

2.4 解决方案

方案一：手动同步序列值

sql

-- 在故障转移前，将序列值设置为当前最大ID + 1
SELECT setval('customers_id_seq', (SELECT MAX(id) FROM customers) + 1);

方案二：使用 pg_dump 同步序列

bash

# 仅导出序列数据
pg_dump --data-only -t '*_seq' -h publisher_host -U repl_user ecommerce > sequences.sql

# 在订阅者上应用
psql -h subscriber_host -U repl_user ecommerce -f sequences.sql

方案三：定期同步脚本

sql

-- 创建同步序列的存储过程
CREATE OR REPLACE FUNCTION sync_sequences()
RETURNS void AS $$
DECLARE
    rec RECORD;
    max_val BIGINT;
BEGIN
    FOR rec IN 
        SELECT schemaname, tablename, attname, pg_get_serial_sequence(schemaname||'.'||tablename, attname) as seqname
        FROM pg_stats 
        WHERE schemaname = 'public' AND pg_get_serial_sequence(schemaname||'.'||tablename, attname) IS NOT NULL
    LOOP
        EXECUTE format('SELECT MAX(%I) FROM %I.%I', rec.attname, rec.schemaname, rec.tablename) INTO max_val;
        IF max_val IS NOT NULL THEN
            EXECUTE format('SELECT setval(%L, %s)', rec.seqname, max_val + 1);
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

3. TRUNCATE 命令复制限制

3.1 问题描述

虽然 TRUNCATE 命令支持复制，但在涉及外键约束的表组时存在限制。

3.2 业务场景示例

考虑一个订单系统的表结构：

sql

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);

-- 订单表（有外键约束）
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2)
);

-- 订单详情表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_name VARCHAR(100),
    quantity INTEGER
);

3.3 问题场景

假设我们有两个不同的发布：

sql

-- 发布1：仅包含 orders 和 order_items
CREATE PUBLICATION orders_pub FOR TABLE orders, order_items;

-- 发布2：仅包含 users
CREATE PUBLICATION users_pub FOR TABLE users;

订阅者订阅了两个发布：

sql

CREATE SUBSCRIPTION orders_sub 
CONNECTION 'host=pub_host dbname=db1 user=repl_user' 
PUBLICATION orders_pub;

CREATE SUBSCRIPTION users_sub 
CONNECTION 'host=pub_host dbname=db1 user=repl_user' 
PUBLICATION users_pub;

3.4 TRUNCATE 失败示例

在发布者上执行：

sql

-- 这会同时截断 orders 和 order_items（CASCADE）
TRUNCATE orders CASCADE;

在订阅者上，可能会出现以下错误：

DANGER

TRUNCATE 复制错误

ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "orders" references "users"
HINT: Truncate table "users" at the same time, or use TRUNCATE ... CASCADE

3.5 解决方案

方案一：确保相关表在同一发布中

sql

-- 将所有相关表放在同一发布中
CREATE PUBLICATION complete_pub FOR TABLE users, orders, order_items;

方案二：临时禁用外键约束

sql

-- 在订阅者上临时禁用约束
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;

-- 执行 TRUNCATE 后重新添加约束
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey 
FOREIGN KEY (user_id) REFERENCES users(id);

4. 大对象（LOB）不支持复制

4.1 问题描述

PostgreSQL 的大对象（Large Objects，通过 lo_* 函数操作）不支持逻辑复制。

4.2 业务场景示例

sql

-- 使用大对象存储文件
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255),
    content_oid OID  -- 大对象OID
);

-- 插入大对象
\lo_import '/path/to/document.pdf'  -- 返回 OID: 12345

INSERT INTO documents (filename, content_oid) 
VALUES ('contract.pdf', 12345);

4.3 复制结果

虽然表数据会复制到订阅者，但大对象本身不会复制：

sql

-- 在订阅者上
SELECT * FROM documents;
/*
 id | filename     | content_oid
----+--------------+------------
  1 | contract.pdf | 12345
*/

-- 但无法访问大对象内容
\lo_export 12345 '/tmp/contract.pdf'  -- 失败：大对象不存在

4.4 解决方案

方案一：使用 BYTEA 类型

sql

-- 重新设计表结构
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255),
    content BYTEA,  -- 直接存储二进制数据
    content_type VARCHAR(100)
);

-- 插入数据
INSERT INTO documents (filename, content, content_type)
VALUES (
    'contract.pdf',
    pg_read_binary_file('/path/to/contract.pdf'),
    'application/pdf'
);

方案二：手动同步大对象

bash

#!/bin/bash
# 大对象同步脚本

# 导出发布者的大对象
pg_dump -h publisher_host -b --format=custom ecommerce > large_objects.dump

# 仅恢复大对象到订阅者
pg_restore -h subscriber_host -d ecommerce --large-objects large_objects.dump

5. 仅支持表复制限制

5.1 支持和不支持的对象

5.2 业务场景示例

不支持的复制尝试：

sql

-- 创建视图
CREATE VIEW order_summary AS
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY user_id;

-- 尝试发布视图（会失败）
CREATE PUBLICATION view_pub FOR TABLE order_summary;
-- ERROR: "order_summary" is not a table

物化视图示例：

sql

-- 创建物化视图
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', created_at) as month,
    SUM(amount) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- 尝试发布物化视图（会失败）
CREATE PUBLICATION mat_view_pub FOR TABLE monthly_sales;
-- ERROR: "monthly_sales" is not a table

5.3 解决方案

方案一：使用普通表替代视图

sql

-- 创建汇总表
CREATE TABLE order_summary_table (
    user_id INTEGER PRIMARY KEY,
    order_count INTEGER,
    total_amount DECIMAL(10,2),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 使用触发器或定期作业更新汇总数据
CREATE OR REPLACE FUNCTION update_order_summary()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO order_summary_table (user_id, order_count, total_amount)
    SELECT 
        user_id,
        COUNT(*),
        SUM(amount)
    FROM orders 
    WHERE user_id = COALESCE(NEW.user_id, OLD.user_id)
    GROUP BY user_id
    ON CONFLICT (user_id) DO UPDATE SET
        order_count = EXCLUDED.order_count,
        total_amount = EXCLUDED.total_amount,
        updated_at = NOW();
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER order_summary_trigger
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION update_order_summary();

6. 分区表复制限制

6.1 分区表复制机制

默认情况下，分区表的复制基于叶子分区进行：

6.2 业务场景示例

创建按时间分区的订单表：

sql

-- 发布者：按月分区的订单表
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    amount DECIMAL(10,2),
    created_at DATE
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders  
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_2024_03 PARTITION OF orders
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

6.3 复制配置选项

选项一：基于叶子分区复制（默认）

sql

-- 创建发布（默认行为）
CREATE PUBLICATION orders_pub FOR TABLE orders;

-- 在订阅者上创建相同的分区结构
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    amount DECIMAL(10,2),
    created_at DATE
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- ... 其他分区

选项二：基于分区根表复制

sql

-- 使用分区根表的标识和模式
CREATE PUBLICATION orders_pub FOR TABLE orders 
WITH (publish_via_partition_root = true);

6.4 灵活的订阅者设计

订阅者可以使用不同的分区策略：

订阅者：按年分区订阅者：普通表

sql

-- 订阅者使用按年分区
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    amount DECIMAL(10,2),
    created_at DATE
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

sql

-- 订阅者使用普通表
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    amount DECIMAL(10,2),
    created_at DATE
);

7. REPLICA IDENTITY FULL 限制

7.1 问题描述

使用 REPLICA IDENTITY FULL 时，如果表包含没有 B-tree 或哈希默认运算符类的数据类型，UPDATE 和 DELETE 操作可能失败。

7.2 问题数据类型

常见的问题数据类型：

数据类型	描述	问题原因
`point`	几何点	没有默认的 B-tree 运算符类
`box`	矩形框	没有默认的哈希运算符类
`polygon`	多边形	几何类型比较复杂
`circle`	圆形	几何类型比较复杂

7.3 业务场景示例

sql

-- 地理位置表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT,  -- 问题数据类型
    region BOX,         -- 问题数据类型
    created_at TIMESTAMP DEFAULT NOW()
);

-- 设置 REPLICA IDENTITY FULL
ALTER TABLE locations REPLICA IDENTITY FULL;

-- 插入数据
INSERT INTO locations (name, coordinates, region)
VALUES (
    '北京办公室',
    POINT(116.407526, 39.904030),
    BOX(POINT(116.0, 39.0), POINT(117.0, 40.0))
);

7.4 更新操作失败

sql

-- 尝试更新（可能失败）
UPDATE locations 
SET name = '北京总部'
WHERE id = 1;

-- 在订阅者上可能出现错误
-- ERROR: could not identify an equality operator for type point

7.5 解决方案

方案一：使用主键作为 REPLICA IDENTITY

sql

-- 使用主键（推荐）
ALTER TABLE locations REPLICA IDENTITY DEFAULT;
-- 或者显式指定
ALTER TABLE locations REPLICA IDENTITY USING INDEX locations_pkey;

方案二：创建唯一索引

sql

-- 为表创建唯一索引（不包含几何类型）
CREATE UNIQUE INDEX locations_name_time_idx 
ON locations (name, created_at);

-- 使用该索引作为复制标识
ALTER TABLE locations REPLICA IDENTITY USING INDEX locations_name_time_idx;

方案三：重新设计表结构

sql

-- 将几何数据分离到单独的表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE location_geometry (
    location_id INTEGER PRIMARY KEY REFERENCES locations(id),
    coordinates POINT,
    region BOX
);

-- 仅复制主表
CREATE PUBLICATION locations_pub FOR TABLE locations;

最佳实践和建议

1. 设计阶段考虑

TIP

设计建议

模式设计：在设计阶段就考虑逻辑复制的需求
主键设计：确保所有复制的表都有适当的主键
数据类型选择：避免使用复杂的几何类型，优先使用标准数据类型
分区策略：合理设计分区策略，考虑复制需求

2. 运维管理

sql

-- 创建模式同步检查函数
CREATE OR REPLACE FUNCTION check_schema_sync(
    pub_conn TEXT,
    sub_schema TEXT DEFAULT 'public'
) RETURNS TABLE(
    table_name TEXT,
    pub_columns TEXT[],
    sub_columns TEXT[],
    status TEXT
) AS $$
DECLARE
    pub_tables TEXT[];
    sub_tables TEXT[];
    tbl TEXT;
BEGIN
    -- 获取发布者表结构
    -- 这里需要使用 dblink 或其他方式连接到发布者
    -- 简化示例
    
    FOR tbl IN 
        SELECT t.tablename 
        FROM pg_tables t 
        WHERE t.schemaname = sub_schema
    LOOP
        -- 比较表结构
        table_name := tbl;
        status := 'OK';  -- 简化逻辑
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

3. 监控和告警

sql

-- 复制延迟监控
CREATE VIEW replication_lag AS
SELECT 
    s.subname,
    s.subowner,
    st.pid,
    st.received_lsn,
    st.last_msg_send_time,
    st.last_msg_receipt_time,
    EXTRACT(EPOCH FROM (now() - st.last_msg_receipt_time)) as lag_seconds
FROM pg_subscription s
JOIN pg_stat_subscription st ON s.oid = st.subid;

4. 故障转移清单

Details

故障转移步骤

停止应用写入
等待复制追上：确保 received_lsn 和 latest_end_lsn 一致
同步序列值：执行序列同步脚本
验证数据一致性：运行数据验证查询
更新应用配置：修改数据库连接字符串
启动应用：在新的主库上启动应用
设置反向复制：如果需要，设置反向复制

总结

PostgreSQL 逻辑复制的限制主要集中在以下几个方面：

模式变更：需要手动同步 DDL 操作
序列同步：需要特别处理序列值的同步
对象类型：仅支持表复制，不支持视图等其他对象
数据类型：某些几何类型在 REPLICA IDENTITY FULL 模式下有限制
外键约束：TRUNCATE 操作在跨发布的外键关系中可能失败

理解这些限制并采用相应的解决方案，可以有效地利用逻辑复制来构建高可用和可扩展的数据库架构。在实际应用中，建议结合具体的业务需求选择合适的复制策略和解决方案。

PostgreSQL 逻辑复制限制详解 ​

概述 ​

1. 数据库模式和 DDL 命令限制 ​

1.1 问题描述 ​

1.2 业务场景示例 ​

1.3 模式变更问题 ​

1.4 解决方案 ​

2. 序列数据不复制 ​

2.1 问题描述 ​

2.2 业务场景示例 ​

2.3 故障转移问题 ​

2.4 解决方案 ​

3. TRUNCATE 命令复制限制 ​

3.1 问题描述 ​

3.2 业务场景示例 ​

3.3 问题场景 ​

3.4 TRUNCATE 失败示例 ​

3.5 解决方案 ​

4. 大对象（LOB）不支持复制 ​

4.1 问题描述 ​

4.2 业务场景示例 ​

4.3 复制结果 ​

4.4 解决方案 ​

5. 仅支持表复制限制 ​

5.1 支持和不支持的对象 ​

5.2 业务场景示例 ​

5.3 解决方案 ​

6. 分区表复制限制 ​

6.1 分区表复制机制 ​

6.2 业务场景示例 ​

6.3 复制配置选项 ​

6.4 灵活的订阅者设计 ​

7. REPLICA IDENTITY FULL 限制 ​

7.1 问题描述 ​

7.2 问题数据类型 ​

7.3 业务场景示例 ​

7.4 更新操作失败 ​

7.5 解决方案 ​

最佳实践和建议 ​

1. 设计阶段考虑 ​

2. 运维管理 ​

3. 监控和告警 ​

4. 故障转移清单 ​

总结 ​

PostgreSQL 逻辑复制限制详解

概述

1. 数据库模式和 DDL 命令限制

1.1 问题描述

1.2 业务场景示例

1.3 模式变更问题

1.4 解决方案

2. 序列数据不复制

2.1 问题描述

2.2 业务场景示例

2.3 故障转移问题

2.4 解决方案

3. TRUNCATE 命令复制限制

3.1 问题描述

3.2 业务场景示例

3.3 问题场景

3.4 TRUNCATE 失败示例

3.5 解决方案

4. 大对象（LOB）不支持复制

4.1 问题描述

4.2 业务场景示例

4.3 复制结果

4.4 解决方案

5. 仅支持表复制限制

5.1 支持和不支持的对象

5.2 业务场景示例

5.3 解决方案

6. 分区表复制限制

6.1 分区表复制机制

6.2 业务场景示例

6.3 复制配置选项

6.4 灵活的订阅者设计

7. REPLICA IDENTITY FULL 限制

7.1 问题描述

7.2 问题数据类型

7.3 业务场景示例

7.4 更新操作失败

7.5 解决方案

最佳实践和建议

1. 设计阶段考虑

2. 运维管理

3. 监控和告警

4. 故障转移清单

总结