Connect to a Google Cloud SQL Postgres DB

Google Cloud Database
1

Hi,

I am very new to google cloud and am having trouble deploying a web app that connects to my google cloud sql instance (postgres). I am trying to build a web app in flask and there are some python queries in my web app. A few of those queries hit my google cloud postgres database. I can successfully connect and execute the python code from a jupyter notebook, however, when I try to deploy the code using ‘gcloud run deploy’ in the Google Cloud SDK Shell, I am getting errors:

Can’t create a connection to host and port 5432

The connection is also timing out. Is there an authorization that I need to provide? Also, it seems that the database connection that I created through SQLalchemy couldn’t read the env_variables in my app.yaml file, how do I allow the code in the python script to read the environment variables?

Here is some of my python code, but I don’t think that it’s the issue since I can run it locally just fine:

def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
    db_host =  "MY DB IP ADDRESS"# e.g. '127.0.0.1' ('172.17.0.1' if deployed to GAE Flex)
    db_user =   "postgres"# e.g. 'my-db-user'
    db_pass =   "password"# e.g. 'my-db-password'
    db_name =  "postgres"  # e.g. 'my-database'
    db_port = 5432  # e.g. 5432

    connect_args = {}
    # [START cloud_sql_postgres_sqlalchemy_connect_tcp]
    pool = sqlalchemy.create_engine(
        # Equivalent URL:
        # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
        sqlalchemy.engine.url.URL.create(
            drivername="postgresql+pg8000",
            username=db_user,
            password=db_pass,
            host=db_host,
            port=db_port,
            database=db_name,
        ),
        connect_args=connect_args,
        pool_size=5,
        max_overflow=2,
        pool_timeout=30,  # 30 seconds
        pool_recycle=1800,  # 30 minutes

    )
    return(pool)

def get_connection():
    engine = connect_tcp_socket()
    with engine.connect() as conn:
        conn.execute('SELECT STMT')
2

If your Cloud SQL instance is using a public IP, then you need to use the Cloud SQL Auth Proxy. There’s a tutorial here (https://cloud.google.com/sql/docs/mysql/connect-run#public-ip-default_1)

But if you’re using a private IP, serverless services such as Cloud Run or App Engine are fully managed by Google and they don’t know/see any VPCs that you may have, so you would need to create a Serverless VPC Connector (https://cloud.google.com/vpc/docs/serverless-vpc-access), which allows them to see and thus connect to any VPCs that you have . There’ s an example here (https://cloud.google.com/sql/docs/mysql/connect-run#private-ip_1)

1 Like
3

Google Cloud Server may be down