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

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?

postgresqlgoogle-cloud-platformgoogle-cloud-sql


Solution 1:[1]

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

  1. 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.

  2. 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)

  3. In terminal go to your PostgreSQL bin folder. Example:

cd “C:\Program Files\PostgreSQL\13\bin
  1. 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>”
  1. 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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions