On 04/11/2018 10:24 AM, Ron wrote:
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.
I might be inclined to COPY/LOAD/check/DELETE
