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