Thread: pgsql and streams
Hi All- I'm new to Postgres and have a question about bulk loading from streams. I know that I can bulk load from a file using COPY. Is it possible to use a stream instead of a file? If so, and I limited to stdin? I'm attempting to stream data from a remote database into my Postgres instance. I don't want to insert each tuple individually using jdbc since that would be horribly slow... Thanks, Chris
You can execute a pg_dump on the remote host (see -h or --host options
I'm new to Postgres and have a question about bulk loading from streams.
I know that I can bulk load from a file using COPY. Is it possible to
use a stream instead of a file? If so, and I limited to stdin? I'm
attempting to stream data from a remote database into my Postgres
instance. I don't want to insert each tuple individually using jdbc
since that would be horribly slow...
to pg_dump) and pipe it to a psql on the local host. That should
replicate the remote database to your host over the network.
You can also use the "-h hostname" option on psql to exectue
a "copy to file" on the remote host. The file ends up on your local
system, where you can do a subsequent copy from file.
Hope this helps,
Thanks for your response, Josh. Actually I’m looking for the most general way to do this, since my remote database might not be psql. In fact, I will probably be streaming through a java process. So I’d like to go from the java process directly into the psql db. Is it still possible?
From: Josh Rovero [mailto:rovero@sonalysts.com]
Sent: Tuesday, March 14, 2006 4:34 PM
To: Christopher Condit; pgsql-general
Subject: Re: [GENERAL] pgsql and streams
Chris Condit wrote:
I'm new to Postgres and have a question about bulk loading from streams.
I know that I can bulk load from a file using COPY. Is it possible to
use a stream instead of a file? If so, and I limited to stdin? I'm
attempting to stream data from a remote database into my Postgres
instance. I don't want to insert each tuple individually using jdbc
since that would be horribly slow...
You can execute a pg_dump on the remote host (see -h or --host options
to pg_dump) and pipe it to a psql on the local host. That should
replicate the remote database to your host over the network.
You can also use the "-h hostname" option on psql to exectue
a "copy to file" on the remote host. The file ends up on your local
system, where you can do a subsequent copy from file.
Hope this helps,
Christopher Condit wrote: > Thanks for your response, Josh. Actually I'm looking for the most > general way to do this, since my remote database might not be psql. In > fact, I will probably be streaming through a java process. So I'd like > to go from the java process directly into the psql db. Is it still > possible? I think recent JDBC drivers allow COPY, but you'll need to check the documentation. The other thing to do is to batch your inserts into groups of (say) 1000. That will provide a real speed increase. -- Richard Huxton Archonet Ltd
Back to this thread - I realize that in the "COPY TO" documentation, it states the CSV file used for loading must be on the server's disk. If I can't put files on the server's disk, I'm curious if I can use the STDIN option from my JDBC client to load the data to the server? Using psql I can stream data after the call. Is there anyway to accomplish the same thing with JDBC? Thanks! Chris -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Christopher Condit Sent: Tuesday, March 14, 2006 4:10 PM To: pgsql-general Subject: [GENERAL] pgsql and streams Hi All- I'm new to Postgres and have a question about bulk loading from streams. I know that I can bulk load from a file using COPY. Is it possible to use a stream instead of a file? If so, and I limited to stdin? I'm attempting to stream data from a remote database into my Postgres instance. I don't want to insert each tuple individually using jdbc since that would be horribly slow... Thanks, Chris ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Christopher Condit schrieb: > Back to this thread - I realize that in the "COPY TO" documentation, it > states the CSV file used for loading must be on the server's disk. If I > can't put files on the server's disk, I'm curious if I can use the STDIN > option from my JDBC client to load the data to the server? Using psql I > can stream data after the call. Is there anyway to accomplish the same > thing with JDBC? > Not sure about JDBC but you can in fact stream your data to COPY via STDIN - which is the network socket of your database connection. I'm using this with python for example. Regards Tino
Hi Tino - thanks for your response. Do you think that's the best way to go - just use Java to launch an external load command to psql.exe? Does anyone else know of a way to accomplish this with Java? Thanks, Chris -----Original Message----- From: Tino Wildenhain [mailto:tino@wildenhain.de] Sent: Thursday, March 30, 2006 1:07 AM To: Christopher Condit Cc: pgsql-general Subject: Re: [GENERAL] pgsql and streams Christopher Condit schrieb: > Back to this thread - I realize that in the "COPY TO" documentation, it > states the CSV file used for loading must be on the server's disk. If I > can't put files on the server's disk, I'm curious if I can use the STDIN > option from my JDBC client to load the data to the server? Using psql I > can stream data after the call. Is there anyway to accomplish the same > thing with JDBC? > Not sure about JDBC but you can in fact stream your data to COPY via STDIN - which is the network socket of your database connection. I'm using this with python for example. Regards Tino
OK - Now I see the COPY patch, adding the CopyManager class to the jdbc driver. This is exactly the functionality I'm looking for. Has anyone gotten this to work with the latest codebase? Is there a new patch available? Thanks for your help, Chris -----Original Message----- From: Tino Wildenhain [mailto:tino@wildenhain.de] Sent: Thursday, March 30, 2006 1:07 AM To: Christopher Condit Cc: pgsql-general Subject: Re: [GENERAL] pgsql and streams Christopher Condit schrieb: > Back to this thread - I realize that in the "COPY TO" documentation, it > states the CSV file used for loading must be on the server's disk. If I > can't put files on the server's disk, I'm curious if I can use the STDIN > option from my JDBC client to load the data to the server? Using psql I > can stream data after the call. Is there anyway to accomplish the same > thing with JDBC? > Not sure about JDBC but you can in fact stream your data to COPY via STDIN - which is the network socket of your database connection. I'm using this with python for example. Regards Tino