Re: Archiving data to another server using copy, psql with pipe

Il 06/04/2017 16:10, pinker ha scritto:
>
> Error message says, as one could expect, that the second table has got smaller precision...
> The question isn't about this particular error - which was induced for purpose - but about atomicity of this operation
Sorry, I read your message without paying the right attention and I
totally lost the table structure, plus the answer about transactional
safety.
Honestly, I can't help you on theory side, I'm sorry. I've never gone so
far with transactions.
On pratcal side, instead, I can say that rubyrep (a replication system
written in Ruby) uses quite this kind of approach in its replication
process (extremely simplified below):
- open transaction
- read from db 1
- write to db 2
- close transaction
When the process encounters some problems (say, connection issues or
table structure mismatch) an exception is thrown, the transaction is
rolled back and everyting is as before it started.
I have this process running widely for about 4 years on some hundred
machines and I've never been reported of transactional problems.

HTH
Moreno.