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?
Best Answer
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.