Re: Move rows from one database to other

On 02/22/2017 04:51 AM, Thomas Güttler wrote:
>>> I have other concerns: atomar transaction. Movement should happen
>>> completely or not all.
>>> I don't think you can do this reliable (atomic transaction) with
>>> "copy table_name".
>>
>> You can if you wrap it in a transaction:
>
> I want to **move** the data. The data should get deleted on the
> satellite after transfer.

Well the replication suggestion is out.

>
> I don't know how to delete the data which was copied, since inserts can
> happen during the copy statement.

However you end up doing this I think you will probably need some sort
of flag on the rows on the satellites. It could be a timestamp field of
when the rows where inserted on the satellite or a boolean
field(copied). First instinct is to use an insert timestamp and a
tracking table that stores the last timestamp used to move rows, where
the timestamp is only written on a successful transfer. To improve the
chances of successful transfer more smaller transfer batches rather then
larger transfers.

>
> Regards,
> Thomas Güttler
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com