How to use pg_restore to import dump file in cloudsql?

Question

I want to import the .sql file created with pg_dump into cloudsql.

The console and gcloud commands are both custom-type dump files, so they are told to use pg_restore.

On my local machine

pg_restore -h cloud_sql_ip -U postgres -d database sqlfile.sql

When I use the command, I get the following error:

pg_restore: error: connection to database "database" failed: could not connect to server: Operation timed out
        Is the server running on host "cloud_sql_ip" and accepting
        TCP/IP connections on port 5432?

If I use the gcloud sql connect command, it will connect without any problems.

I don't know why I am getting the error.

Thank you for your help.

You mentioned custom format, wouldn't that mean you should use the second command in the doc? [1] [1]cloud.google.com/sql/docs/postgres/import-export/… — ZUKINI
My dump file is in .sql file format, so the second method doesn't work... Am I thinking wrong?? — hyungsik.jo
@DevinTaylor Neither of their instructions for using pg_restore make any sense. — jjanes

jjanes jjanes · Accepted Answer · 2021-03-26T02:39:35

To use PostgreSQL's pg_restore with -d, it needs to be able to connect to the database, which apparently it can't, probably due to firewall issues.

Perhaps you can change the config to let you connect from your own computer (I don't know, I'm not a Google Clouse user.)

Another option would be use pg_restore without -d to convert the custom dump file into a real sql file, then load that real sql file using one of the other methods you already tried but which failed because it was the wrong format.

pg_restore -f real_sql.sql sqlfile.sql

Or you could just re-run pg_dump, choosing the plain format not the custom format this time.

How to use pg_restore to import dump file in cloudsql?

1 Answers