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.
Don.