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...)
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 :-)
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
