[Postgres-xl-general] dblink and importing data from external database

Victor D. dorofeen at gmail.com
Wed Apr 15 14:54:28 PDT 2015


That will be great, thanks. Using pandas for importing big tables is much
less effective.
Yes, I used one of coordinators to connect to local database.

Best regards,

Victor Dorofeenko

Victor Dorofeenko
E-mail: dorofeen at gmail.com
Skype: victor30213

On 15 April 2015 at 01:05, Koichi Suzuki <koichi.dbms at gmail.com> wrote:

> Hmm.... interesting.   Maybe we need to look into it.
>
> What nod did you target?  Coordinator?
>
> Regards;
> ---
> Koichi Suzuki
>
>
> 2015-04-14 19:03 GMT+09:00 Victor D. <dorofeen at gmail.com>:
> > Thanks for your reply. I have checked SELECT ... INTO TABLE with local
> > tables and it works fine. However with dblink it fails. The same
> situation
> > with CREATE TABLE ... AS SELECT ... statement. The error message
> metioned in
> > prevoius mail comes from the dblink function and means that it cannot
> find
> > the named connection. But simple SELECT statement (without local data
> > change) works with dblink.
> >
> > Best regards,
> >
> > Victor Dorofeenko
> >
> > On 14 April 2015 at 03:08, Koichi Suzuki <koichi.dbms at gmail.com> wrote:
> >>
> >> Sorry, I'm not completely certain but I'm afraid SELECT INTO is not
> >> supported by XL at present.   If so, error message may need
> >> improvement.
> >>
> >> Regards;
> >> ---
> >> Koichi Suzuki
> >>
> >>
> >> 2015-04-14 6:36 GMT+09:00 Victor D. <dorofeen at gmail.com>:
> >> > Hello,
> >> >
> >> > I tried to use dblink connection to create a table in a local
> >> > postgres-xl
> >> > database from SELECT statement running on remote database. So the
> first
> >> > connection query is:
> >> >
> >> > SELECT  dblink_connect('conn', 'host=localhost port=5492
> dbname=remotedb
> >> > user=dbuser password=dbpwd client_encoding=utf8');
> >> >
> >> >  Then the query:
> >> >
> >> > SELECT * INTO loctable1 FROM dblink('conn', 'SELECT col1, col2 FROM
> >> > remtable1') AS t1(col1 text, col2 text);
> >> >
> >> > came to failure with the message:
> >> >
> >> > ERROR:  could not establish connection
> >> > DETAIL:  missing "=" after "conn" in connection info string
> >> >
> >> > But when I use immediately in the same session the same query as
> above,
> >> > but
> >> > without table creation:
> >> >
> >> > SELECT * FROM dblink('conn', 'SELECT col1, col2 FROM remtable1') AS
> >> > t1(col1
> >> > text, col2 text);
> >> >
> >> > everything works flawlessly.
> >> > I would greatly appreciate any advice how to solve that issue.
> >> >
> >> > The database user has full superuser rights. The postgres-xl is
> >> > installed on
> >> > 4 virtual nodes with Ubuntu 14.04.
> >> > I tried to execute the queries using psql tool. The host of remote
> >> > database
> >> > is connected using ssh tunnel like:
> >> >
> >> > ssh user at remotehost -L 5492:localhost:5432
> >> >
> >> > Thanks in advance,
> >> >
> >> > Victor Dorofeenko
> >> >
> >> >
> >> >
> ------------------------------------------------------------------------------
> >> > BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
> >> > Develop your own process in accordance with the BPMN 2 standard
> >> > Learn Process modeling best practices with Bonita BPM through live
> >> > exercises
> >> > http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual-
> >> > event?utm_
> >> > source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
> >> > _______________________________________________
> >> > Postgres-xl-general mailing list
> >> > Postgres-xl-general at lists.sourceforge.net
> >> > https://lists.sourceforge.net/lists/listinfo/postgres-xl-general
> >> >
> >
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20150415/7e0776e2/attachment.htm>


More information about the postgres-xl-general mailing list