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

Question

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

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

thanks

Answer 1

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

https://metacpan.org/pod/DBD::Pg#COPY-support

Answer 2

You definitely want to use the copy from and copy to commands to efficiently and efficiently retrieve data from databases. They are an order of magnitude faster than iterating over rows of data. You also want to disable indexes when copying data to the target table, and then include them (and allow them to be created) when the copy is complete.

Assuming you just connect to the listener ports of two databases, just open the connection to the source database, copy the table (s) to the file, open the connection to the target database, and copy the file back to the target table.

Answer 3

. \copy to ... - psql, SQL, DBI PostgreSQL .

, PostgreSQL SQL COPY FROM STDIN TO STDOUT, , DBI " ", . ( , TO STDOUT - , psql \copy to ....)

: , , , samba nfs, COPY TO '/full/path/to/mounted/folder/data.txt' ....

Answer 4

\copy ( * from remote_table) '/local/file.txt'... then\copy local_table '/local/file.txt', db, \copy psql script.

script

PGUSER = remoteuser PGPASSWORD = remotepwd

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

PGUSER = localuser 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 5

You can use ~ / .pgpass and save PGUSER export and leave the password outside the environment ... (always a good idea from a security point of view)