Spanner Database to Postgres

gcloud dataflow jobs run my-export-job \
--gcs-location='gs://dataflow-templates/latest/Cloud_Spanner_to_GCS_Avro' \
--region=us-east1 \
--disable-public-ips \
--service-account-email="spanner-backup@myproject.iam.gserviceaccount.com" \
--parameters='instanceId=myspannerInstance,databaseId=mydb,outputDir=gs://spanner_backup_01/mydb' \
--max-workers=10 \
--project=myproject
createTime: '2021-07-09T01:13:57.582627Z'
currentStateTime: '1970-01-01T00:00:00Z'
id: 2021-07-08_18_13_56-3583574098731082022
location: us-east1
name: my-export-job
projectId: myproject
startTime: '2021-07-09T01:13:57.582627Z'
type: JOB_TYPE_BATCH
gcloud dataflow jobs list --project myproject --filter="name=my-export-job" --format=json | jq '.[]|.state'
"Done"
pip3.6 install psycopg2-binary
pip3.6 install fastavro
pip3.6 install python-snappy
ssh  devuser@i-023841b3311095xx2 -L 9090:my-postgredb-sg.cluster-c3g329ovnrbl.ap-southeast-1.rds.amazonaws.com:5432
import psycopg2
import json
from fastavro import reader
#establishing the connection
conn = psycopg2.connect(
database="mydb", user='myuser', password='mypwd', host='127.0.0.1', port= '9090'
)
#Creating a cursor object using the cursor() method
sql = """INSERT INTO user_imported(identity,identity_type)
VALUES(%s,%s);"""
with open('user.avro-00000-of-00001', 'rb') as fo:
avro_reader = reader(fo)
line_count = 0
for row in avro_reader:
try:
print("processed for: " + row["identity"] )
cursor = conn.cursor()
cursor.execute(sql,(row["identity"],row["identity_type"]))
conn.commit()
cursor.close()
line_count += 1
if not (line_count % 1000):
print("No Of Records Processed: " + str(line_count))
except Exception as e:
print(e)
#Closing the connection
conn.close()

--

--

--

Working as Cloud Architect & Software enthusiastic

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

An Extension to JOSH

Create simple gym management in Laravel part 1.1 permission management: User and Group CRUD

Virtual Machines vs Containers: Scalability, Portability and Cost Optimisation

Leveraging the Next Trend in Cloud Computing: Amazon Web Services

Access Control List on GraphQL with Loaders

My Best Internship Experience

Did you seriously buy another keyboard? or; The Planck

Installing OpenCV on Raspberrypi-Buster

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
NIRAV SHAH

NIRAV SHAH

Working as Cloud Architect & Software enthusiastic

More from Medium

The right way for Celery using mongo as backend

An overview of version control and non-relational databases

PostgreSQL-part2: Roles & Users

Comparison and Analysis of Various NoSQL Databases