On Wed, Apr 11, 2018 at 9:15 AM, Don Seiler <don@seiler.us> wrote:
Right now I'm thinking of falling back to the far-less-elegant method of dumping the data to a flat file via COPY, running psql to connect to the archive DB remotely and running a COPY to load the data (or maybe transferring the flat file to the archive DB to load it there, offloading that part of the workload), then deleting the data from the main DB. I could capture the rows dumped in a control table and compare the rows deleted against that and then rollback the delete if necessary.
This is what I would lean toward - with a data compression/decompression step surrounding the network transfer.
Not sure exactly how it might fit in but don't forget about "DELETE FROM ... RETURNING *" and being able to place that into a CTE/WITH clause (same goes for insert/update)