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

W dniu 2017-04-06 14:28:04 użytkownik Moreno Andreo <moreno.andreo@evolu-s.it> napisał:
> Il 06/04/2017 13:58, pinker ha scritto:
> >
> > W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo <moreno.andreo@evolu-s.it> 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@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>