Pg_restore issue migrating from heroku to render

[jmarsh24]

I am experiencing a timeout issue when attempting to restore database tables that are over 300k long on render. My database is 300mb in size. When render imports this table, it responds with

pg_restore: error: error returned by PQputCopyData: could not receive data from server: Operation timed out
SSL SYSCALL error: Operation timed out

I have tried psql restore and pg_restore commands with compressed and uncompressed files, but it still breaks when the data table is too large.

I have tried the solution outlined here below, but I am still running into the same issue.

[SOLVED] Postgresql: Running pg_restore with +300.000 rows times out in Frankfurt region

I’m moving from Heroku to Render, and I’d like to bring my data with me I’ve tried psql with an sql export, and pg_restore with an sql/tar dump. Both start, continue for a bit, and then times out when a table with ~300.000 records is being restored. When using pg_restore I get this error: error: error returned by PQputCopyData: SSL SYSCALL error: Operation timed out The command I’m running is: pg_restore --no-owner --password -h DATABASE_HOST -U USERNAME -d DATABASE_NAME dump.…

Am I missing a configuration somewhere that would allow pg_restore to finish? Is there anything that can be done on render’s side to help with these large datasets?

[render_dev]

Hey @jmarsh24,

Are you running the restore locally? Also, how long does the command run before giving you this error?

To speed things up, it might help to create a Docker service from this repo: https://github.com/render-examples/pg-toolkit. You can try to copy the SQL dump to the service, SSH into it, then use pg_restore from within the service using the Render internal connection string. This may possibly solve the issue you are facing.

Let me know if you’re able to try this!

[jmarsh24]

@render_dev

To speed things up, it might help to create a Docker service from this repo: GitHub - render-examples/pg-toolkit. You can try to copy the SQL dump to the service, SSH into it, then use pg_restore from within the service using the Render internal connection string. This may possibly solve the issue you are facing.

Let me know if you’re able to try this!

I was able to use wormhole to transfer my dump file and then I manually restored from that. It solved the issue.

[render_dev]

Glad to hear you were able to resolve your issue. Our Datastores team will be taking a closer look at your experience migrating in the separate feedback email thread.