current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

Trying to import sql file around 8GB using these commands

sudo -u postgres psql yourdb -f my_file.sql
sudo -u postgres psql yourdb < my_file.sql
sudo -u postgres cat my_file.sql | psql yourdb

sudo -u postgres psql
\i my_file.sql

but all of them returning out of memory error message. Also tried configuration given here

share|improve this question
    
any suggestions? – Said Kaldybaev Oct 24 '14 at 5:31
    
Is psql throwing the out of memory error, or is the backend server that has the problem? – a_horse_with_no_name Oct 24 '14 at 6:14
    
@a_horse_with_no_name thanks for your reply, what do you mean by backend server? – Said Kaldybaev Oct 24 '14 at 6:16
    
Postgres is the (backend) server, psql is the client tool. The exception can either happen on the client when reading the file, or on the backend which is executing the individual SQL statements. – a_horse_with_no_name Oct 24 '14 at 6:17
    
@a_horse_with_no_name ok, got it. psql is returning out of memory error – Said Kaldybaev Oct 24 '14 at 6:27
up vote 2 down vote

psql generally doesn't need much memory when playing large SQL files, since it doesn't buffer the whole file, only one query at a time, or it uses a COPY stream.

The main situation when it may run out of memory is not when importing, but when SELECT'ing a large resultset, especially on 32 bits systems. This situation is generally solved by setting FETCH_COUNT.

On import, the dump would need to contain unusually large rows to cause client-side memory issues. The maximum size of a column is 1Gb, so it's theorically possible that it would cause trouble on import, if the database has that kind of contents.

If the system doesn't have enough RAM, maybe you could just add swap space.

share|improve this answer
    
thanks for your answer, i'll try to add additional swap space – Said Kaldybaev Oct 24 '14 at 9:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.