Thread: Out of memory psql import!

Out of memory psql import!

From
Edgar Delgado
Date:
Hi,

Im trying to import a  9.4 database to a 9.5.1 system with psql but after few minutes I get OOM error...

Have 15gb ram.

Tried pgtune conf and still get error


shared_buffers = 3840MB 
effective_cache_size = 11520MB 
work_mem = 19660kB
maintenance_work_mem = 960MB 
min_wal_size = 1GB 
max_wal_size = 2GB 
checkpoint_completion_target = 0.7
wal_buffers = 16MB 
default_statistics_target = 100



Re: Out of memory psql import!

From
Dmitrii Golub
Date:
2016-03-21 23:02 GMT+03:00 Edgar Delgado <edgdelgado@gmail.com>:
Hi,

Im trying to import a  9.4 database to a 9.5.1 system with psql but after few minutes I get OOM error...

Have 15gb ram.

Tried pgtune conf and still get error


shared_buffers = 3840MB 
effective_cache_size = 11520MB 
work_mem = 19660kB
maintenance_work_mem = 960MB 
min_wal_size = 1GB 
max_wal_size = 2GB 
checkpoint_completion_target = 0.7
wal_buffers = 16MB 
default_statistics_target = 100




Please, tell us how are you doing this exactly.
Did you try pg_dump, pg_restore?

--
Dmitrii Golub

Re: Out of memory psql import!

From
Edgar Delgado
Date:
Not yet!

doing a pg_dump right now...

also, the OOM occurs in a copy event of a 2gb table.

droped work_mem to 2gb and trying again with psql  while doing a pg_dump

I just did "psql database < stuff.sql"

2016-03-21 17:13 GMT-03:00 Dmitrii Golub <dmitrii.golub@gmail.com>:
2016-03-21 23:02 GMT+03:00 Edgar Delgado <edgdelgado@gmail.com>:
Hi,

Im trying to import a  9.4 database to a 9.5.1 system with psql but after few minutes I get OOM error...

Have 15gb ram.

Tried pgtune conf and still get error


shared_buffers = 3840MB 
effective_cache_size = 11520MB 
work_mem = 19660kB
maintenance_work_mem = 960MB 
min_wal_size = 1GB 
max_wal_size = 2GB 
checkpoint_completion_target = 0.7
wal_buffers = 16MB 
default_statistics_target = 100




Please, tell us how are you doing this exactly.
Did you try pg_dump, pg_restore?

--
Dmitrii Golub

Re: Out of memory psql import!

From
Edgar Delgado
Date:
well...

I guess with pg_dump is working now!   30mins and no OOM error

2016-03-21 17:20 GMT-03:00 Edgar Delgado <edgdelgado@gmail.com>:
Not yet!

doing a pg_dump right now...

also, the OOM occurs in a copy event of a 2gb table.

droped work_mem to 2gb and trying again with psql  while doing a pg_dump

I just did "psql database < stuff.sql"

2016-03-21 17:13 GMT-03:00 Dmitrii Golub <dmitrii.golub@gmail.com>:
2016-03-21 23:02 GMT+03:00 Edgar Delgado <edgdelgado@gmail.com>:
Hi,

Im trying to import a  9.4 database to a 9.5.1 system with psql but after few minutes I get OOM error...

Have 15gb ram.

Tried pgtune conf and still get error


shared_buffers = 3840MB 
effective_cache_size = 11520MB 
work_mem = 19660kB
maintenance_work_mem = 960MB 
min_wal_size = 1GB 
max_wal_size = 2GB 
checkpoint_completion_target = 0.7
wal_buffers = 16MB 
default_statistics_target = 100




Please, tell us how are you doing this exactly.
Did you try pg_dump, pg_restore?

--
Dmitrii Golub


Re: Out of memory psql import!

From
Dmitrii Golub
Date:
2016-03-22 0:07 GMT+03:00 Edgar Delgado <edgdelgado@gmail.com>:
well...

I guess with pg_dump is working now!   30mins and no OOM error

2016-03-21 17:20 GMT-03:00 Edgar Delgado <edgdelgado@gmail.com>:
Not yet!

doing a pg_dump right now...

also, the OOM occurs in a copy event of a 2gb table.

droped work_mem to 2gb and trying again with psql  while doing a pg_dump

I just did "psql database < stuff.sql"

2016-03-21 17:13 GMT-03:00 Dmitrii Golub <dmitrii.golub@gmail.com>:
2016-03-21 23:02 GMT+03:00 Edgar Delgado <edgdelgado@gmail.com>:
Hi,

Im trying to import a  9.4 database to a 9.5.1 system with psql but after few minutes I get OOM error...

Have 15gb ram.

Tried pgtune conf and still get error


shared_buffers = 3840MB 
effective_cache_size = 11520MB 
work_mem = 19660kB
maintenance_work_mem = 960MB 
min_wal_size = 1GB 
max_wal_size = 2GB 
checkpoint_completion_target = 0.7
wal_buffers = 16MB 
default_statistics_target = 100




Please, tell us how are you doing this exactly.
Did you try pg_dump, pg_restore?

--
Dmitrii Golub



well done

--
Dmitrii Golub