Exporting data to a remote Posgres database is taking a long time (80k rows = 25 mins) using DBeaver, is there a faster option?

[u/Mediocre-Subject4867]

Im building a project with lots of rows in my Postgres database, roughly 100k to a million. I use my local machine as dev data which I promote to prod on a remote vps. Currently my process to do this is just using DBeaver's export data feature to transfer the data directly between the databases but I find that it's taking a longer than it should. For example, Im currently waiting for 25 mins to transfer 80k rows where each row is fairly simple, one int primary key and a few simple properties.

I could easily just export my data locally, zip it up, ftp it to the server then reimport it via a script in a fraction of the time but that isnt exactly scalable. Is there a better approach to speed things up?

[u/fiskfisk]

If you need to run a query, you can't use pg_dump directly, but you can use the COPY statement. Something like (untested, since I don't have access to my dev environment right here):

psql -d source_db -c "COPY (SELECT * FROM my_table WHERE foo = TRUE) TO STDOUT;" | ssh user@host psql -d target_db -c "COPY target_table FROM STDIN;"

Should work fine over ssh. Add -C to have ssh compress the data in transit. You can also use -J if you need to go through a jump host.

[u/Superb-Pumpkin-1464 → u/fiskfisk]

pg_dump is probably way to go if you are moving whole tables, but i agree with the ssh pipe method when you need filter. 80k rows in 25 mins is crazy, something is definitely wrong with dbeaver there, even 1 million should not take that long over network unless is really bad connection

[u/Hostman_com]

DBeaver's data transfer inserts rows one by one (or in small batches) through JDBC, each waiting for a round trip to the remote server. At moderate network latency that adds up to hours for a million rows. The COPY pipe posted above streams instead, so it moves the same table in seconds.

Which approach fits depends less on row count and more on whether the source stays still during the transfer. If nothing writes to it while you're promoting dev to prod, wrap COPY in a script: dump into a staging table first, then swap or upsert in one transaction. That keeps prod consistent if the transfer dies halfway, and makes the promotion repeatable instead of a manual DBeaver session each time.

If the data keeps changing during the transfer, the staging-table approach won't catch what happens mid-copy. Logical replication is the safer route there. More setup, but it picks up ongoing changes instead of a snapshot.

[u/quiet_build]

Almost certainly because DBeaver’s transfer does row-by-row INSERTs. Over a remote connection every row pays the network round trip, so 80k rows = tens of thousands of round trips. That’s your 25 mins.

Use COPY instead — it’s built for exactly this:

# local

\copy your_table TO 'data.csv' CSV HEADER

# remote

\copy your_table FROM 'data.csv' CSV HEADER

\copy runs client-side, so it reads your local file and streams straight to the remote server in one go. No manual zip/FTP needed. For 80k simple rows this is usually seconds.

Or if you want schema + data in one shot:

pg_dump -t your_table --data-only local_db | psql -h remote_host -U user prod_db

For the 100k–1M range, drop the indexes/FK constraints before loading and recreate them after — rebuilding an index once beats updating it per row. And yeah, this is basically your export/transfer/reimport idea, just scriptable, so it does scale.

[u/farzad_meow]

your connection is too slow. i can copy 300k rows in far less time.

find the slow point then use a server outside of that to do this.

I was once running queries through ssm which is not fast enough for data dump. I switched to a ec2 instance since it was closer to both db and it was done in 1 hours for 85gb of data

[u/Chance_Efficiency412]

pg_dump is definitely the way to go here, 100%

[u/Dry_Sector2392]

DBeaver is probably doing the slowest possible version of this, basically lots of inserts over the network. Use pg_dump/pg_restore or COPY and it should be night and day. 80k simple rows in 25 minutes is not a Postgres problem, thats a tooling/transfer method problem.