W dniu 2017-04-06 14:28:04 użytkownik Moreno Andreo <moreno.andreo@xxxxxxxxxx> napisał: > Il 06/04/2017 13:58, pinker ha scritto: > > > > W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo <moreno.andreo@xxxxxxxxxx> napisał: > >> Il 05/04/2017 23:26, pinker ha scritto: > >>> Hi, > >>> I'm trying to write an archive manager which will be first copying data from > >>> tables with where clause and then, after successful load into second server > >>> - delete them. > >>> The simplest (and probably fastest) solution I came up with is to use copy: > >>> psql -h localhost postgres -c "copy (SELECT * FROM a WHERE time < now()) to > >>> stdout " | psql -h localhost postgres -c "copy b from stdin" > >> both psql -h are on localhost. Is it a typo? > > No, It's not a typo, just a test ;) > ... so source and destination database are the same? (just guessing...) yes, they are as you can easily read - it's postgres in both cases. This is just easy to reproduce example. > > > >>> I have made very simple test to check if I can be sure about "transactional" > >>> safety. It's not two phase commit of course but it's seems to throw an error > >>> if something went wrong and it's atomic (i assume). The test was: > >>> > >>> CREATE TABLE public.a > >>> ( > >>> id integer, > >>> k01 numeric (3) > >>> ); > >>> > >>> CREATE TABLE public.b > >>> ( > >>> id integer, > >>> k01 numeric (1) > >>> ); > >>> > >>> insert into a select n,n from generate_series(1,100) n; > >>> > >>> and then: > >>> psql -h localhost postgres -c "copy a to stdout "|psql -h localhost > >>> postgres -c "copy b from stdin" > >>> > >>> so psql has thrown an error > >> ... and what is the error? > >>> and no rows were inserted to the b table - so it > >>> seems to be ok. > >>> > >>> Is there maybe something I'm missing? > >>> Some specific condition when something could go wrong and make the process > >>> not atomic? (i don't care about data consistency in this particular case). > >> Without knowing OS and psql version of both servers, how they are > >> connected, or what error you get, it's hard for me to help you further. > > psql in version 9.6 and OS: Red Hat 7 > > Does Os version really make any difference? > AFAIK the biggest differences are among different OS families, say > Windows and Linux, but there could be some small things among linux > distributions. More depth about this topic is beyond my knowledge. > ... but you did not report the error message, with this is much easier > to help you without guessing too much :-) 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 > > > > Best regards, > > A. Kucharczyk > > > > > >> Best regards > >> Moreno. > >>> > >>> > >>> > >>> -- > >>> View this message in context: http://www.postgresql-archive.org/Archiving-data-to-another-server-using-copy-psql-with-pipe-tp5954469.html > >>> Sent from the PostgreSQL - general mailing list archive at Nabble.com. > >>> > >>> > >> > >> > >> > >> -- > >> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > >> > > > > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
