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

Поиск
Список
Период
Сортировка
От pinker
Тема Re: [GENERAL] Archiving data to another server using copy,psql with pipe
Дата
Msg-id 177466637-98ae5d81bc451f3931af0609a332b6f1@pmq4v.m5r2.onet
обсуждение исходный текст
Ответы Re: [GENERAL] Archiving data to another server using copy, psql withpipe  (Moreno Andreo <moreno.andreo@evolu-s.it>)
Список pgsql-general

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

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





В списке pgsql-general по дате отправления:

Предыдущее
От: Moreno Andreo
Дата:
Сообщение: Re: [GENERAL] Archiving data to another server using copy, psql withpipe
Следующее
От: Karsten Hilbert
Дата:
Сообщение: Re: [GENERAL] A change in the Debian install