Re: Archiving Data to Another DB?

On Wed, Apr 11, 2018 at 9:15 AM, Don Seiler <don(at)seiler(dot)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)

David J.
​