pg_dump: Write failed: Connection reset by peer

Posted on

Question :

we are trying to move all database data to other location.
we chose to perform pg_dump and we encountered few problems:

first was out of memory problem:
during pg_dump initialization we get an error:

pg_dump: saving search_path =
pg_dump: saving database definition
out of memory

then we gave to VM +16GB of RAM, so 24GB in total, the pg_dump process passed it and now we have another problem:

pg_dump: dumping contents of table lob_messages
Write failed: Connection reset by peer

Error occurs so far only on this “lob_messages” table, this table is huge – pgAdmin shows 256GB size.
I have tried pg_dump all the database, after that error i have tried only pg_dump on that exact table. Error still occurs.

We are using Postgesql 9,4. Our sys admins dunno the problem.
Here is htop view:
enter image description here

Here is df -h view:

enter image description here

i have tried to make pg_dump file on /mnt mounted NAS storage and also locally in /var/lib/postgresql/9.4/main

here is postgresql.conf changed settings:
enter image description here

error remains. Can you share some wisdom what i’m doing wrong?
During that error (connection reset by peer) i got disconect from the server, but only on that session where pg_dump is run.
On other connection (monitoring htop) remains connected during that error.

Answer :

This looks like the error is coming from your ssh session, not pg_dump. Use ‘screen’ or ‘tmux’ so you can resume the session after ssh failure.

Perhaps pg_dump is still running, or completed normally, even after it’s controlling session went away.

Yeah. The main problem was in firewall configuration.
There were a session limit (two hours) after that, all “idle” sessions were dropped.

while htop does not go into “idle” state for firewall perspective, while pg_dump does.

Leave a Reply

Your email address will not be published. Required fields are marked *