How to create local clone of a GCP Cloud SQL export for PostgresQL

Question

I have exported a database as a SQL file in Google Cloud Platform Cloud SQL Postgres Database using their GUI wizard. I want to import the data from the dump into my local PostgresQL installation. I tried using PgAdmin4 with various settings but kept failing. I then tried the following command which retrieved the data but most of the relationships are gone.

psql -f "SQLFile.sql" --host "localhost" --port "5432" --username "dbusername" "myDBName"

How can I create a local clone of my Cloud SQL db?

Look inside SQLFile.sql. Does it contain those missing tables, or were they missing from the dump file as well? — jjanes
– jjanes, Commented Jan 10, 2022 at 16:42
@jjanes sql file contains everything I am posting what my issue was as an answer if anyone else needs it. — Coola
– Coola, Commented Jan 10, 2022 at 17:45

Coola · Accepted Answer · 2022-03-29 13:39:27Z

4

Posting an answer for anyone else who needs this. The following was performed on a Windows 10 machine.

Export from Cloud SQL using the GCP console GUI. This will save it to a cloud storage bucket and you can download it from GCS bucket.
On local machine - remove any database with the same name as the one you want to import . Then create a fresh database with the same name as the one you want to import. (in my case this was the issue)
In terminal go to your PostgreSQL bin folder. Example:

cd “C:\Program Files\PostgreSQL\13\bin

Then run the following command - replacing values as per your needs.

psql -f "<full-path-to-downloaded-sql-file>" --host "localhost" --port "5432" --username "<local-postgres-username>" "<db-name-to-import>”

You may need to grant privileges to users on tables. This can be done using the grant wizard in pgAdmin 4 or as described in this answer.

edited Mar 29, 2022 at 13:39

answered Jan 10, 2022 at 17:52

Coola

3,2532 gold badges25 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arif Over a year ago

It was helpful for me (y)

Collectives™ on Stack Overflow

How to create local clone of a GCP Cloud SQL export for PostgresQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related