How can I use DBI to execute a "\copy from remote table" command in Postgres?

Question

I need to copy from a remote postgres server to a local one. I cannot use any ETL tools; it must be done using Perl with DBI. This data will be large so I don't want to use "select from source" and "insert into local". I was looking to use COPY to create a file but this file will be created on the remote server. I can't do that either. I want to use \COPY instead.

How can I use DBI to execute a "\copy from remote table" command and create a local file using DBI in Perl.

Thanks

Answer 1

You definitely want to use the "copy from" and "copy to" commands to get the data in and out of the databases efficiently. They are orders of magnitude faster than iterating over rows of data. You many also want to turn off the indexes while you're copying data into the target table, then enable them (and let them build) when the copy is complete.

Assuming you are simply connecting to the listener ports of the two databases, simply open a connection to the source database, copy the table(s) to a file, open a connection to the destination database and copy the file back to the target table.

Answer 2

Hmm. \copy to ... is a psql directive, not SQL, so it won't be understood by DBI or by the PostgreSQL server at the other end.

I see that the PostgreSQL's SQL COPY command has FROM STDIN and TO STDOUT options -- but I doubt that DBI has a way to perform the "raw reads" necessary to access the result data. (I'm sure TO STDOUT is how psql internally implements \copy to ....)

So: In your case, I would mount a folder on your source box back to your target box using e.g. samba or nfs, and use plain old COPY TO '/full/path/to/mounted/folder/data.txt' ....

Answer 3

I got it to work using \copy (select * from remote_table) to '/local/file.txt' ... then \copy local_table from '/local/file.txt' to load the file into the local db. I executed the \copy command from a psql script.

Here's my script

export PGUSER=remoteuser export PGPASSWORD=remotepwd

/opt/PostgreSQL/8.3/bin/psql -h xx.xx.xx -p 5432 -d remotedb -c "\COPY (select * from remote_table where date(reccreationtim e) = date((current_date - interval '4 day'))) TO '/local/copied_from_remote.txt' D ELIMITER '|'"

export PGUSER=localuser export PGPASSWORD=localpwd

/opt/PostgreSQL/8.3/bin/psql -h xx.xx.xx.xx -p 5432 -d localdb -c "\COPY local_table FROM '/local/copied_from_remote.txt' DELIMITER '|'"

Answer 4

You can do it in perl with DBD::Pg, details can be found here:

http://search.cpan.org/~turnstep/DBD-Pg-2.13.1/Pg.pm#COPY_support

Answer 5

You could use ~/.pgpass and save yourself the export PGUSER stuff, and keep the password out of the environment... (always a good idea from a security perspective)