在Postgresql 9.1中，如何将旧的数据从一个数据库表归档到另一个数据库表中？

Question

理想情况下，9.1版本中的新功能“WITH ROWS”应该支持跨越数据库边界，但似乎只能在单个数据库中使用。

WITH moved_rows AS (
    DELETE FROM events
    WHERE
        "date" >= '2010-01-01' AND
        "date" < '2011-01-01'
    RETURNING *
)
INSERT INTO events_archive
SELECT * FROM moved_rows;

我希望我能像这样指定事件："DELETE FROM LiveDB.events"，然后我可以执行"INSERT INTO ArchiveDB.events"。但是，使用dblink似乎无法获取要移动的行列表，即使它可以，我也不确定此语句在跨数据库的事务安全性。

- kenyee

---

模式在这种情况下可能会很有用。它们能否满足您的需求？ - Milen A. Radev

Answer 1

你可以使用 SQL COPY 或 psql \copy 将数据转储到文件中，并在一个事务中删除它，然后在另一个事务中将其导入到另一个数据库中。要在单个事务中执行这两个步骤，您需要使用 XA（分布式事务）。

从LiveDB转储

BEGIN TRANSACTION;
COPY (
  SELECT * FROM events
  WHERE "date" >= '2010-01-01'
  AND   "date" < '2011-01-01'
) TO '/tmp/events.csv' WITH CSV HEADER;

DELETE FROM events
WHERE "date" >= '2010-01-01'
AND   "date" < '2011-01-01'
COMMIT;

插入到ArchiveDB中:

COPY events FROM '/tmp/events.csv' WITH CSV HEADER;

希望能有所帮助。

- tscho

---

刚开始接触Postgres，但如果它能正常工作，将解决我很多问题。虽然我不喜欢将数据转储到文件中再次读取……但对于某些简单的情况可能有效。 - sam yi

是的，使用dblink会更加简洁，但正如您在问题中所述，dblink不支持跨数据库边界的事务。 - tscho