0

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 ?

davidgo
  • 269
  • 4
  • 10

2 Answers2

1

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

https://www.postgresql.org/docs/9.3/static/dblink.html

nathan hayfield
  • 2,627
  • 1
  • 17
  • 28
0

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."'");
davidgo
  • 269
  • 4
  • 10
  • https://stackoverflow.com/questions/36476192/postgresql-copy-transfer-data-from-one-database-to-another – nathan hayfield Sep 30 '17 at 02:16
  • @nathanhayfield - thank you for the link, but that solution does not solve my problem - it is for copying databases on the same server. My problem was copying between servers and from within Postgres. – davidgo Sep 30 '17 at 02:50