Обсуждение: Command line export or copy utility?

Does anyone know of any export or copy utility that runs on FreeBSD?
I basically need a program that will connect to one database, do a select
and copy the result to a second database.

Alternatively a tool which would export the data in a format suitable to
be loaded by psql is just fine.


Found some windows utilities that pretty much do what I need, but I need a
utility that would run from a FreeBSD machine since the machines where the
data will be getting moved are both FreeBSD and at a data center.

I am about to write a program for this in python, but if anyone knows of a
utility would be great.

I will be doing queries like:
select  * from source table where <condition is true>

I found pgexport
http://gborg.postgresql.org/project/pgexport/projdisplay.php, but it did not
support the where clause and an email to the author bounced which I guess
means he may no longer support the program. If the program wasn't in perl I
wouldn't mind fixing it up. :-)

Use psql; it provides all the command-line options you need.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Francisco Reyes wrote:
> Does anyone know of any export or copy utility that runs on FreeBSD?
> I basically need a program that will connect to one database, do a
> select and copy the result to a second database.

There are a few ways, from memory (so I might have the odd syntax error):


To replicate a table run pg_dump on one machine pointing at the host/db
to export & pipe the output to psql -f with the host & name of the
target db.

pg_dump -h host0 -d db0 -t table ... | psql -h host1 -d db1 -f


you can do similar data streams from one db to another with (if the
target table exists):

psql .... -c "copy table to STDOUT ..." | psql ... -c "copy table from
STDOUT ..."


to do this with the results of a query to subset the data will require
the pre-building of the target table, but you can do:

psql -h host0 -d db0 -F"|" -Atc "select.....;" | psql -h host1 -d db1 -c
"copy table from STDIN with delimiters = '|';"



Cheers,

  Brent Wood

On Tue, 2007-05-22 at 18:07 -0400, Francisco Reyes wrote:
> Does anyone know of any export or copy utility that runs on FreeBSD?
> I basically need a program that will connect to one database, do a
> select and copy the result to a second database.

Two options:

1) if you want a whole table or schema, a pipe works nicely:
eg$ pg_dump -t <table> | psql


2) As of 8.2, you can formulate COPY commands with subqueries. For
example:
eg$ psql -c 'COPY (SELECT origin_id,origin FROM origin
    WHERE is_public order by 1) TO STDOUT'

eg$ psql -c 'COPY (SELECT x FROM a WHERE x%2=1) TO STDOUT' \
    | psql -c 'COPY a FROM STDIN;'


The only wrinkle is what to do when you need the DDL for the table
itself (say, when you want to create the same table with a subset of the
rows). The way I do this is to pg_dump the schema (-s) in the custom
format (-Fc). Then, I generate a table of contents with pg_restore -l,
edit the TOC to include only the entries I want, and then rerun
pg_restore with -L.

Good luck,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Reece Hart writes:

> On Tue, 2007-05-22 at 18:07 -0400, Francisco Reyes wrote:
>> Does anyone know of any export or copy utility that runs on FreeBSD?
>> I basically need a program that will connect to one database, do a
>> select and copy the result to a second database.
>
> Two options:
> 1) if you want a whole table or schema, a pipe works nicely:
> eg$ pg_dump -t <table> | psql
>
> 2) As of 8.2, you can formulate COPY commands with subqueries. For
> example:
> eg$ psql -c 'COPY (SELECT origin_id,origin FROM origin
>     WHERE is_public order by 1) TO STDOUT'
>
> eg$ psql -c 'COPY (SELECT x FROM a WHERE x%2=1) TO STDOUT' \
>     | psql -c 'COPY a FROM STDIN;'

For the archives.
If using a version prior to 8.2 one can do from within psql:

select * into temporary table tmp_copy_table from <TABLE>
 where <CONDITION>;
copy tmp_copy_table to '<FULLPATH>';

This is primarily when one is trying to copy a subset of data.
If doing the full table then, as Reece mentioned, pg_dump is the best route.