I have a database backup of around 450MBs. I want to restore the database to Amazon Lightsail managed database with 2vCPU and 1 GB ram. I also have enabled Data Migration Import Mode to not get any interruption while importing data (recommended way).
Database restore process starts successfully and it takes around 3 hours but suddenly this process failed by throwing this error
pg_restore: error: could not execute query: no connection to the server
I have started the process again and it again failed after 3 hours throwing same error. My internet is stable and my database is active. I couldn't figure it out what's could be the reason for that.
I think that pgagent is not supported on RDS. If pgagent is central to your app, you will need to find another solution, like not using RDS, or changing your app not to use pgagent.
But usually pg_restore will create all the extensions needed as one of the first things it does, so I don't understand how they takes 3 hours to notice this, nor why the problem manifests with the follow-up error you get. I don't get "no connection to server error". What I do get depends on what options were given to pg_restore. So this presentation of these issues might be lightsail/RDS specific.
If pgagent is peripheral to your app, then you if have still have access to the original server you could drop that extension and redo the dump. If not, you could restore the dump onto a community version of PostgreSQL, drop the extension, and redo the dump. The problem is that this might be just the tip of the iceberg, and you might uncover many other compatibility issues, and it would suck to do this one at a time.
If you want to preview of what is going on, maybe you could do something like:
To see all the extensions the restoration is going to try to make and how early in the session it will try to make them.