sql - Postgres 转储选定的行

Question

我有一个数据库，它有一个中心对象，大部分数据都依赖于该中心对象；许多引用表，一对多连接和多对多连接。我需要根据中央表中的属性备份所有行并能够恢复这些行。

用于备份目的。我已将 ON DELETE CASCADE 添加到相关的从属表中。所以我可以简单地删除所有行

DELETE FROM main_table WHERE attr=1

但我需要能够将与它们关联的所有行和引用恢复到较早的时间点。积分:

• pg_dump 不允许基于 sql 语句的转储
• COPY 不生成SQL

因此这是我提出的解决方案，我讨厌它。对于每个表，属性值:

CREATE TABLE tmp_main_table AS SELECT * FROM main_table WHERE attr=val;
CREATE TABLE tmp_table_1 AS SELECT * FROM table_1 WHERE main_table_id IN (SELECT id FROM main_Table WHERE attr=val);
...

然后 pg_dump -t tmp_main_table -t tmp_table_1 ...

恢复将需要将行重新插入到这些表中。我可以完全控制数据库，所以我可以确定引用表 ides 保持不变，但它看起来非常脆弱。有没有更好的办法？

编辑:更多细节

我们运营着许多商业网站，客户通过 API 更新他们的库存。然而，经常会发生客户的API调用出错，几天都没有注意到的情况。在这种情况下，他们会调用我们，要求将他们的库存恢复到以前的状态。我们需要能够按客户定期拍摄数据快照，以便快速恢复。这是一个简单的示例数据库来说明，在下面的示例中，我只需要通过 customer_id 备份 product 中的行，以便以后恢复，当我恢复时希望其他行保持不变，只恢复属于该特定 customer_id 的行:

CREATE TABLE customer (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE color (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE flag (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name TEXT,
    color_id INTEGER, -- foreign key to color(id)
    customer_id INTEGER -- foreign key to customer(id)
);

CREATE TABLE product_flag_join (
   id SERIAL PRIMARY KEY,
   product_id INTEGER, -- foreign key to product(id)
   flag_id -- foreign key to flag(id)
);

Answer 1

使用 table partitioning似乎是一个很好的解决方案。它需要大量的准备工作，但在备份和恢复数据方面提供了极大的便利。每个客户在所有包含客户特定数据的表中都应该有自己的分区，因此您可以使用一个命令(最好是二进制格式)备份整个数据库，并只恢复选定的表。您还可以考虑在单独的架构中创建客户端分区，并在需要时恢复整个架构。

如果由于某种原因上述不可能，我会想到以下模式:

• 在本地机器上安装救援 Postgres 服务器
• 每天对整个数据库进行二进制备份
• 在需要时将所需日期的备份恢复到救援服务器
• 通过查询救援服务器生成 SQL 转储
• 将 SQL 转储恢复到主服务器