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,

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