CODEWITHSUNDEEP
databasepostgresql
tritium_3
tritium_3

Reputation: 678

cannot import new database from pg_dump, out of memory. psql cloned_db < db_backup.sql

made a backup of a remote database by the following 

pg_dump dbname | gzip > filename.gz

made a new database that i want to import it to 

CREATE DATABASE new_clone;

tried importing into new database by the following command:

gunzip -c /path/to/backup/filename.sql.gz | psql new_clone

working, imported parts of the database, but ran into error

out of memory

no other information... most tables and schemas have appeared, but I cannot get past this problem.

ive tried doing a simple

pg_dump dbname > filename

and a 

psql dbname < infile

tried without compression, and it fails as well. my guess is that a blob inside the database is too large? is there a way to skip the blobs that cause errors?

database size is 10GB uncompressed, 2.2GB compressed. i have 135GB free disk space, and 32GB ram. running ubuntu 14.04, postgres v 9.6.2

appreciate any help at all. thanks.

Upvotes: 2

Views: 5369

Answers (5)

Gustavo Tonini
Gustavo Tonini

Reputation: 1

In my case, using pg_dump's option --column-inserts solved the problem.

Upvotes: 0

Alistair R
Alistair R

Reputation: 936

I had the same problem with a 30GB database dump, and splitting the file up works well for me.

To back it up do a dump of the file and, pipe it through split

pg_dump dbname | split -b 1000m - backup.dump

Then restore it using a pipe from cat

createdb dbname
cat backup.dump* | psql dbname

https://www.postgresql.org/docs/8.1/backup.html#BACKUP-DUMP-LARGE

Upvotes: 0

Saket Suraj
Saket Suraj

Reputation: 187

I had the same issue recently. I am working on ubuntu 20.04 and postgres version is 12. You should try reading the first line of the error. The main error is written on the first line. In my case, postgis-scripts were missing. As a result, I was getting the OOM error. To fix it, run:

sudo apt-get install postgis postgresql-12-postgis-scripts

Upvotes: 0

garaboncias
garaboncias

Reputation: 181

To improve k.o. answer, psql --no-readline could be used to avoid stdin(readline) OOM issue.

Upvotes: 0

k.o.
k.o.

Reputation: 101

I have same issue. After little digging I found that is not psql issue, but sdin: after change COPY ... FROM stdin to COPY ... from '/path/to/file/with.dump' all became fine. So when you try feed psql through stdin, IMHO, OS, not psql, trying load all dump into memory, and, as dump bigger, than memory process can have, you got error.

PS I'm sorry for my bad English, it is not my native language.

Upvotes: 1

Related Questions