1

We have 3 postgres VMs hosted on AWS. I have taken a dump of a database from a server. I am able to restore this dump on 2nd server. But whenever I run pg_restore for the 3rd server, it got stuck after some time and I got connection timeout error.backup size is 9 MB.

below is the command I used:

pg_restore -c -h server -U postgres -d dbname "E:\backfile.backup"

I checked using pg_stat_activity, Nobody is using this database except pg_restore.Sometimes it got stuck, sometimes it throws random errors like below:

pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

How can I debug this issue?

Improve this question
2
  • Can you provide more details? Database size? Time interval to receive the timeout? Does it take more to restore the 3rd database than it takes for the first two? Without any details it is hard to provide an answer.
    – Alexei
    Feb 5, 2018 at 6:10
  • The -c option tries to drop existing objects. Maybe the database is still in use and pg_restore can't drop those objects. Did you check if it's waiting for locks? wiki.postgresql.org/wiki/Lock_Monitoring
    – user1822
    Feb 5, 2018 at 7:58

1 Answer 1

Reset to default
0

I discussed with my network guys and they said that the server for which restore is working, is accessed using private IP through VPN. Another server is accessed using public IP without any VPN. There is Difference in latency between public and internal IP addresses.

When I copied the backup file on the same server where Postgres server is hosted and tried restoring the database, it worked within seconds.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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