Postgres export large table to another database

Question

What is the problem? I have a table in my Postgres database with about 56 millions rows, 20~ GB summary. It stored on local Machine with 16GB RAM and i7-7700 3.6GHz. For managing my databases i use DataGrip and have several database server connections opened in one time. I need to export table from one server to another, but when i try to do that with simple mouse drag (from local server to remote) i'm getting next error "Database client process needs more memory to perform the request".

DataGrip allows to export/import tables DataGrip advisor says:

To configure: open 'PostgreSQL 10 - postgres@localhost' data source properties, go to 'Advanced' tab and add '-XmxNNNm'to 'VM options' field, where NNN is the number of megabytes (e.g. -Xmx256m).

I was trying several values of VM options (256, 1024, 8048) and also tuned my Postgres local server's configs, but it doesn't resolved my problem. Here's configs:

#effective_cache_size = 8GB

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

#shared_buffers = 4GB           # min 128kB
                    # (change requires restart)
#huge_pages = try           # on, off, or try
                    # (change requires restart)
#temp_buffers = 256MB           # min 800kB
#max_prepared_transactions = 0      # zero disables the feature
                    # (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB             # min 64kB
#maintenance_work_mem = 1024MB      # min 1MB
#replacement_sort_tuples = 150000   # limits use of replacement selection sort
#autovacuum_work_mem = -1       # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB          # min 100kB
dynamic_shared_memory_type = windows    # the default is the first option
                    # supported by the operating system:
                    #   posix
                    #   sysv
                    #   windows
                    #   mmap
                    # use none to disable dynamic shared memory
                    # (change requires restart)

# - Disk -

#temp_file_limit = -1           # limits per-process temp file space
                    # in kB, or -1 for no limit

# - Kernel Resource Usage -

#max_files_per_process = 1000       # min 25
                    # (change requires restart)
#shared_preload_libraries = ''      # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0          # 0-100 milliseconds
#vacuum_cost_page_hit = 1       # 0-10000 credits
#vacuum_cost_page_miss = 10     # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms         # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0      # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 0       # measured in pages, 0 disables

# - Asynchronous Behavior -

#effective_io_concurrency = 0       # 1-1000; 0 disables prefetching
#max_worker_processes = 8       # (change requires restart)
#max_parallel_workers_per_gather = 2    # taken from max_parallel_workers
#max_parallel_workers = 8       # maximum number of max_worker_processes that
                    # can be used in parallel queries
#old_snapshot_threshold = -1        # 1min-60d; -1 disables; 0 is immediate
                    # (change requires restart)
#backend_flush_after = 0        # measured in pages, 0 disables

Answer 1

DataGrip takes whole file into the RAM and then tries to export it. For the best performance it's better to use native tools.

Read DataGrip help topics about:

• Create a full data dump PostgreSQL
• Restore a full data dump for PostgreSQL