CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformgoogle-cloud-sql
hyungsik.jo
hyungsik.jo

Reputation: 61

How to use pg_restore to import dump file in cloudsql?

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.

Upvotes: 2

Views: 2471

Answers (2)

Didier Jean Charles
Didier Jean Charles

Reputation: 507

PostgreSQL's pg_restore -d from your local (On my local machine) requires you to first authorize your public IP, there by allowing your machine to connect to your cloudsql instance...

Per GCP

You have not authorised any external networks to connect to your Cloud SQL instance.

If you click on the Connections link on the left nav, under the Networking tab, you will see the above message...you will need to add your pubic IP (click Add Network)

enter image description here

Hopefully this helps

Upvotes: 0

jjanes
jjanes

Reputation: 44202

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.

Upvotes: 3

Related Questions