Import large .sql file to Postgres

I need to Import a large data-dump.

The structure is already created. But the data-dump is 16GB large.

If I try it simple with

psql -d "DATABASE" < "datadump.sql"

I get a out of memory Message on Ubuntu DB.

i have no idead how to import.

The DB-Server has 16GB Ram, 50GB Disk-Space free (its a developer vm)

Does anyone has a idea, how to import this file?

UPDATE

My postgresql.conf

# - Memory -

shared_buffers = 4GB            # min 128kB
                    # (change requires restart)
work_mem = 1048kB           # min 64kB
maintenance_work_mem = 2GB      # min 1MB

checkpoint_segments = 128       # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min      # range 30s-1h
checkpoint_completion_target = 0.9  # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s       # 0 disables

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Cost Constants -
effective_cache_size = 12GB

wal_buffers = 16MB

The inserts are single-row and all for one table.
The Table has 71 columns.

I dont know, whats in line 1447540. No Texteditor can open this file -.-
(But the content of the file is correct. After spliting the file into much smaller files, the imports run successful.)

The first screenshot shows the free -ht output before importing. The second screenshot was taken 2 seconds before the out of memory messeage comes up.

It's clear that the issue is related to stdin. If you have control over the dump process, a possible solution is to do not execute the .sql file from psql. To do so, you simply have to run the following two queries:

• Execute this from the original database: COPY (SELECT * FROM mytable WHERE ...) TO '/storage/mytable.dump'

• Execute this from the destination database (the one where you have the out of memory issue): CREATE TABLE mynewtable ( ... ); COPY mynewtable FROM '/storage/mytable.dump';.

You can mess around with the various parameters available in COPY. For example, you can export a gzipped file or a CSV with headers and custom separator: https://www.postgresql.org/docs/current/static/sql-copy.html .

You can also issue a pg_dumpall > /storage/db.out to export the entire original database and run psql -f /storage/db.out postgres to recreate it on the new database: https://www.postgresql.org/docs/current/static/app-pg-dumpall.html . This should work.. I've just dumped an output file of several tens of GB on OS X (linux-like) without any problem.

Se also pg_dump for more selective database dump options: https://www.postgresql.org/docs/current/static/app-pgdump.html

StackExchange Database Administrators Q#151930, answer score: 3