why only first 1000 records inserted in dbeaver?-CodePudding

I am using Dbeaver 22.1.4 on Windows 10 Home Single 64bit. My RAM is 8 Gb. I want to insert 16 millions data from one server to another using dblink (All servers are Linux Ubuntu, running Postgresql 12). The query looks like this ( I split it to 5000 first for testing) :

INSERT INTO table_server1 ([some 115 columns])
SELECT *
FROM dblink('myconn',$MARK$
    SELECT [some 115 columns]
    FROM public.table_server2 limit 5000
    $MARK$) AS t1 (
        id varchar, col1 varchar, col2 varchar, col3 integer, ... , col115 varchar);

It only inserts 1000 data which takes 1-2 seconds. It says "Updated rows : 1000" on the result window. There is no error as such.

What happen ? How can I insert all data ? I have edit the config file by modifying the max memory to 2 GB : -Xmx2048m

CodePudding user response：

do you insist on using Dbeaver and/or dblink? If not, and you can connect to terminal on either postgres server, you can do this very fast (no splitting needed) and easily without "middle man" (your machine), directly server-to-server:

psql -d sourcedb -c "\copy (SELECT [some 115 columns] FROM public.table_server2) TO STDOUT" | psql -d targetdb -c "\copy table_server1 FROM STDIN"

Of course you need to specify host, user/password for both sides psql