'Copy data from Postgres DB (GCP Project A) to another Postgres DB (GCP Project B)

I would be happy to get your help / feedback re data load.

Goal: Load source data from a Postgres database, which is located in GCP project A to another Postgres database, which is located in GCP project B.

Challenge: Get a connection (I have an IAM account with sufficient rights to run a COPY TO / COPY FROM command) to the Postgres DB in GCP Project A and copy the table either to a CSV or create a dump that can be used in order to be inserted to another Postgres DB in GCP Project B. How do I connect to the database (e.g. if I create a key, where shall I store the json keyfile and would that approach even be feasible?) with this IAM email account?

Other ways I've researched were to use psycopg2 (thus I could use the function cursor.copy_expert (which doesn’t need any superuser right or Postgres user credentials and copy the data), but I didn’t succeed in connecting to the database with psycopg2 due to challenges with cloud proxy.

Another idea was to use pg_dump or gcloud sql export csv. I would be curious if some of you were facing a similar challenge and how did you solve it and what might be the best way/practice

postgresqlgoogle-cloud-platform


Solution 1:[1]

You can have a try out database migration service. You can set up a continuous migration configuration and use Cloud SQL for PostgreSQL.

Solution 2:[2]

Hello after a lot of searching I've come to these solutions:

If you have continuous copy, you need to use the database migration service, check this documentation.

If you have one shot copy:

  • you can restore your instance, see the bottom page of this documentation
  • you can create a bucket and backup your instance on it, then import it from the other project

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 guillaume blaquiere
Solution 2 Anna Kolandjian

Related Questions