I have custom software which includes a function to copy data between tables on postgresql servers. It does this 1 row at a time, which is fine when the servers are close together, but as I've started deploying servers where the latency is > 300ms this does not work well at all.
I believe the solution is to use the "COPY" statement, but I am having difficulty implementing it. I am using the ADODB php library
When I attempt a copy from a file I get the error "must be superuser to COPY to or from a file". The problem is that I don't know how to copy "from STDIN" where stdin is not actually piped to the PHP script. Is there any way to provide the stdin input as part of the sql command using ADODB, or is there an equivalent command which will allow me to do a batch insert without waiting for each individual insert ?
I solved the problem by using an insert command which had all the inserts in a single statement using "union all" - ie
$sql="INSERT INTO tablename (name,payload)
select 'hello', 'world' union all
select 'this', 'is a test' union all
select 'and it', 'works'";
$q=$conn->Execute($sql);
One limitation of this solution was that strings need to be enquoted while integers for example, must not be. I thus needed to write some additional code to make sure some fields were enquoted but not others.
To find out which columns needed to be enquoted I used
$coltypes=$todb->GetAll("select column_name,data_type
from information_schema.columns
where table_name='".$totable."'");
Postgresql extension dblink() allows you to copy data from one server's database to another. You need to know the ip address of the server and the port the database is running on. Here are some links with more info:
http://www.leeladharan.com/postgresql-cross-database-queries-using-dblink