How to migrate data from on-premise Postgres to Google Cloud

There are several ways to move data from an on-premise PostgreSQL database to Google Cloud. Here are three common approaches:

1. Use a Cloud Data Integration Tool: Google Cloud offers several tools that can help you move data from an on-premise PostgreSQL database to the cloud. For example, Cloud Data Fusion is a fully-managed, cloud-native data integration platform that can help you build, execute, and monitor data pipelines between various data sources and destinations, including PostgreSQL and Google Cloud. You can use Cloud Data Fusion to extract data from your on-premise PostgreSQL database, transform the data as needed, and load the data into a cloud-based data store, such as BigQuery or Cloud SQL.
2. Use a Command-Line Tool: Another option is to use a command-line tool, such as pg_dump or pg_dumpall, to extract the data from your on-premise PostgreSQL database and save it to a file. You can then use a tool such as gsutil to upload the file to Google Cloud Storage. Once the data is in Google Cloud Storage, you can use a tool such as bq load to load the data into BigQuery, or you can use Cloud SQL Import to import the data into Cloud SQL.
3. Use the Cloud SQL API: If you want to automate the data transfer process, you can use the Cloud SQL API to programmatically export data from your on-premise PostgreSQL database and import it into Cloud SQL. To use the Cloud SQL API, you will need to set up a Cloud SQL instance, create a service account with the appropriate permissions, and install the Cloud SQL API client library for your programming language of choice. You can then use the API to export the data from your on-premise database and import it into Cloud SQL.

Each of these approaches has its own strengths and weaknesses, and the best approach for a given situation will depend on the specific requirements of your data migration.

Here is an example of how you can use the Cloud SQL API to export data from an on-premise PostgreSQL database and import it into Cloud SQL:

import google.auth
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
import googleapiclient.discovery
import googleapiclient.errors
import psycopg2

# Set the project, instance, and database IDs
project_id = "my-project"
instance_id = "my-instance"
database_id = "my-database"

# Connect to the on-premise PostgreSQL database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="user",
    password="password",
    dbname="database",
)

# Create a cursor
cur = conn.cursor()

# Execute a query to retrieve the data you want to export
cur.execute("SELECT * FROM my_table")

# Fetch the data
data = cur.fetchall()

# Close the cursor and connection
cur.close()
conn.close()

# Create a service account and credentials
service_account_info = {
  "type": "service_account",
  "project_id": project_id,
  "private_key_id": "PRIVATE_KEY_ID",
  "private_key": "PRIVATE_KEY",
  "client_email": "CLIENT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "CLIENT_X509_CERT_URL"
}
credentials = Credentials.from_service_account_info(service_account_info)

# Create a Cloud SQL client
client = googleapiclient.discovery.build(
    "sqladmin", "v1beta4", credentials=credentials
)

# Construct the export request
request = {
    "instance": f"projects/{project_id}/instances/{instance_id}",
    "database": database_id,
    "exportContext": {
        "fileType": "SQL",
        "uri": "gs://my-bucket/my-file.sql",
        "sqlExportOptions": {
            "tables": ["my_table"]
        }
    }
}

# Execute the export request
operation = client.databases().export(**request).execute()
print(f"Exporting data to {operation['exportContext']['uri']}")

# Wait for the export to complete
while True:
    result = client.operations().get(project=project_id, operation=operation["name"]).execute()
    if result["status"] == "DONE":
        if "error" in result:
            raise Exception(result["error"])
        print("Export