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 ;) > > > > 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? 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
