Re: Archiving Data to Another DB?

On 04/11/2018 11:15 AM, Don Seiler wrote:
> Let's say I have two DBs: main (9.6.6) and archive (10.2).
>
> I have a table in main where I want to archive data older then 60 days. 
> For various reasons, the table is not partitioned, so for now we must use 
> DELETE. The destination table in the archive DB is partitioned with the 
> new Pg10 partitioning.
>
> My initial plan was to have a stored procedure on the archive DB use 
> postgres_fdw to do an INSERT INTO / SELECT to select the data remotely and 
> insert into the local archive table. It would then issue a single DELETE 
> command to remotely remove the data from the main DB. However I found that 
> doing this resulted in the main DB calling thousands (perhaps millions if 
> it's one-per-row) of individual DELETE statements based on a ctid column. 
> Aside from WAL behavior concerns, it is flooding my postgresql server logs 
> since I log any DML.
>
> On top of that, I'm told that a remote DELETE wouldn't be transactional, 
> so if I were to compare inserted rows vs deleted rows and found a 
> mismatch, I couldn't just rollback the DELETE. I plan to verify this with 
> a small test case later but for now I'll assume this to be true.
>
> 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.
>
> Like I said, not elegant, but I don't want to risk losing data that wasn't 
> successfully archived to the archive DB. I'm very interested to hear what 
> others might be doing for tasks like this.

It might not be elegant, but a COPY / DELETE / LOAD is granular, so you can 
restart at any point.


-- 
Angular momentum makes the world go 'round.