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.
我需要从远程postgres服务器复制到本地服务器。我不能使用任何ETL工具;它必须使用带有DBI的Perl来完成。这个数据会很大,所以我不想使用“select from source”和“insert into local”。我希望使用COPY创建一个文件,但是这个文件将在远程服务器上创建。我也做不到。我想用\ COPY代替。
How can I use DBI to execute a "\copy from remote table" command and create a local file using DBI in Perl.
如何使用DBI执行“远程表中的\ copy”命令并使用Perl中的DBI创建本地文件。
Thanks
5 个解决方案
#1
You can do it in perl with DBD::Pg, details can be found here:
您可以使用DBD :: Pg在perl中完成,详细信息可以在这里找到:
#2
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.
您肯定希望使用“copy from”和“copy to”命令来有效地将数据输入和输出数据库。它们比迭代数据行快几个数量级。您还希望在将数据复制到目标表时关闭索引,然后在复制完成时启用它们(并让它们构建)。
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.
假设您只是连接到两个数据库的侦听器端口,只需打开与源数据库的连接,将表复制到文件,打开与目标数据库的连接,然后将文件复制回目标表。
#3
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.
嗯。 \ copy to ...是一个psql指令,而不是SQL,因此DBI或另一端的PostgreSQL服务器不会理解它。
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 ....)
我看到PostgreSQL的SQL COPY命令有FROM STDIN和TO STDOUT选项 - 但我怀疑DBI有办法执行访问结果数据所需的“原始读取”。 (我确定TO STDOUT是psql内部如何实现\ 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' ....
所以:在你的情况下,我会使用例如将源文件夹上的文件夹安装回目标框。 samba或nfs,并使用普通的旧COPY TO'/full/path/to/mounted/folder/data.txt'....
#4
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.
我使用\ copy(select * from remote_table)到'/local/file.txt'...然后\ copy local_table从'/local/file.txt'将文件加载到本地数据库中。我从psql脚本执行了\ copy命令。
Here's my script
这是我的剧本
export PGUSER=remoteuser export PGPASSWORD=remotepwd
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 '|'"
/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') )))'/ local/copied_from_remote.txt'D ELIMITER'|'“
export PGUSER=localuser export PGPASSWORD=localpwd
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 '|'"
/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'|'”
#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)
您可以使用〜/ .pgpass并自己保存导出PGUSER的东西,并将密码保持在环境之外......(从安全角度来看,总是一个好主意)
#1
You can do it in perl with DBD::Pg, details can be found here:
您可以使用DBD :: Pg在perl中完成,详细信息可以在这里找到:
#2
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.
您肯定希望使用“copy from”和“copy to”命令来有效地将数据输入和输出数据库。它们比迭代数据行快几个数量级。您还希望在将数据复制到目标表时关闭索引,然后在复制完成时启用它们(并让它们构建)。
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.
假设您只是连接到两个数据库的侦听器端口,只需打开与源数据库的连接,将表复制到文件,打开与目标数据库的连接,然后将文件复制回目标表。
#3
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.
嗯。 \ copy to ...是一个psql指令,而不是SQL,因此DBI或另一端的PostgreSQL服务器不会理解它。
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 ....)
我看到PostgreSQL的SQL COPY命令有FROM STDIN和TO STDOUT选项 - 但我怀疑DBI有办法执行访问结果数据所需的“原始读取”。 (我确定TO STDOUT是psql内部如何实现\ 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' ....
所以:在你的情况下,我会使用例如将源文件夹上的文件夹安装回目标框。 samba或nfs,并使用普通的旧COPY TO'/full/path/to/mounted/folder/data.txt'....
#4
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.
我使用\ copy(select * from remote_table)到'/local/file.txt'...然后\ copy local_table从'/local/file.txt'将文件加载到本地数据库中。我从psql脚本执行了\ copy命令。
Here's my script
这是我的剧本
export PGUSER=remoteuser export PGPASSWORD=remotepwd
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 '|'"
/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') )))'/ local/copied_from_remote.txt'D ELIMITER'|'“
export PGUSER=localuser export PGPASSWORD=localpwd
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 '|'"
/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'|'”
#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)
您可以使用〜/ .pgpass并自己保存导出PGUSER的东西,并将密码保持在环境之外......(从安全角度来看,总是一个好主意)