why only first 1000 records inserted in dbeaver?

Question

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

Do some basic debugging. What if you run that from psql, not DBeaver? What if you do it without the dblink, just copying from one table to another on the same server? — jjanes, Aug 09 '22 at 13:38

score 1 · Accepted Answer · answered Aug 09 '22 at 08:42

1

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

answered Aug 09 '22 at 08:42

rouen

5,003
2
25
48

Hi @rouen , sounds excellent. thanks. I will try that. Your method doesn't have any overhead right ? This is a production server that I am working on. – padjee Aug 09 '22 at 13:47
I am not sure what kind of overhead you have in mind. I am not aware of any drawbacks of this, I am using it for years for copying data between postgres instances. – rouen Aug 10 '22 at 07:38
i meant overhead that may prove costly to system performance. Anyway, I tried it. For 100K records, it tooks around 2-3 minutes. Pretty fast. Thanks @rouen – padjee Aug 10 '22 at 10:37
That is surprisingly slow actually, I can get like 100k/second on my servers, but of course that depends on data (columns, types), network infrastructure, WAL buffers, etc. – rouen Aug 11 '22 at 19:50
yes its slow actually. but considering the table has 5 json type and 50 varchars, its fast enough. LoL. Is it ? How to make it faster then ? @rouen – padjee Aug 11 '22 at 22:58
By "...you can connect to terminal on either postgres server" Do you mean I have to login to either server physically / remotely (connect to either server) OR I can login to the psql (login to the db on the server ) via my local's psql shell and execute the command ? – padjee Aug 12 '22 at 23:18
Finally I made it work by increasing the -xmx4096. But anyway, its still slow. Case closed. Thanks for your attention. :) – padjee Aug 16 '22 at 09:05

why only first 1000 records inserted in dbeaver?

1 Answers1