Archive ancient data from one database table to another database table in Postgresql 9.1?

Question

Ideally, the new "WITH ROWS" feature in 9.1 would have supported crossing database boundaries, but it only appears to work in a single DB.

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;

I wish I were able to specify events as something like "DELETE FROM LiveDB.events" and then I can do "INSERT INTO ArchiveDB.events". dblink doesn't seem to work to get the list of rows to move, and even if it did, I'm not sure if this statement is transactionally safe across DBs...

Answer 1

You can dump the data to a file (with SQL COPY or psql \copy) and delete it in one transaction and then import it into the other database in another transaction. To do these two steps in a single transaction you need XA (distributed transactions).

Dump from 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;

Insert into ArchiveDB:

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

Hope that helps.