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

W dniu 2017-04-06 13:24:16 użytkownik Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)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 ;)

> >
> > 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>