Re: [google-cloud-sql-discuss] Re: Unable to connect cloud SQL postgres

Hi Binya,

You should connect either with a unix socket or a TCP connection, but not both. If you include `=tcp:5432` after the instance name in your proxy command, you are using a TCP connection. You do not need to specify `host=/cloudsql/<INSTANCE-CONNECTION_NAME>` - this will override the host set in the url and prevent you from connecting to 127.0.01. 

On Saturday, August 4, 2018 at 4:07:38 AM UTC-7, Binaya patel wrote:

Hi George,

Thanks for your reply.

Here are the steps I followed to set up the PostgresQSL:

1. Login to the cloud.google.com

2. Changed to my project "Dev-Instance" in "Google Cloud Platform"

3. Click on "SQL" and Clicked on "Create Instance"--> PostgreSQL

4. Entered the Instance ID

5. Entered the Region as "us-central1" and Zone "Any"

6. Taken machine as 4-cores, 20 GB of Memory, Storage type SSD, storage size 200gb

7. Choose all other option as the default option.

Then tried to access create_engine('postgresql+psycopg2://USER:PASSWORD@/DATABASE?host=/cloudsql/INSTANCE_CONNECTION_NAME')

Have not followed the exact steps as given in https://cloud.google.com/appengine/docs/flexible/python/using-cloud-sql-postgres#setting_connection_strings_and_adding_a_library

After that, I followed all the steps in the given page step-by-step, and it works after that.

as per the given steps, I change the proxy command,

as per the documentation, the command is :

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432

or

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 -credential_file=<PATH_TO_KEY_FILE> &

I used :

sudo ./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> &

as per the documentation it is given as:

export SQLALCHEMY_DATABASE_URI=postgresql+psycopg2://[USER_NAME]:[PASSWORD]@127.0.0.1:5432/[DATABASE_NAME]

i used:

sudo export SQLALCHEMY_DATABASE_URI=postgresql+psycopg2://<DATABASE-USER-ID>:<DATABASE-PASSWORD>@/<DATABASE-NAME>?host=/cloudsql/<INSTANCE-CONNECTION_NAME>

Did the same change in the app.yaml file.

Done the below changes in main.py

as per documentation :

db = SQLAlchemy(app)


class Visit(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime())
    user_ip = db.Column(db.String(46))

    def __init__(self, timestamp, user_ip):
        self.timestamp = timestamp
        self.user_ip = user_ip

My code :

engine = create_engine(os.environ['SQLALCHEMY_DATABASE_URI'])  Session = sessionmaker(bind=engine)

Base = declarative_base()

class Visit(Base):

    __tablename__ = "Visit"

id = Column("id",Integer, primary_key=True)  timestamp = Column("timestamp",DateTime)  user_ip = Column("user_ip",String(46))

  Thanks

Binaya

On Sat, Aug 4, 2018 at 1:25 AM 'George (Cloud Platform Support)' via Google Cloud SQL discuss <google-cloud...@googlegroups.com> wrote:

Hello Binaya, 

What have you undertaken prior to this connection attempt to your PostgreSQL instance? A detailed, step-by-step description would help a lot in setting up a solid start for our investigation. Your connection string should look similar to: postgresql+psycopg2://USER:PASSWORD@/DATABASE?host=/cloudsql/INSTANCE_CONNECTION_NAME . For reference, you may check the "Using Cloud SQL for PostgreSQL" documentation page. What steps did you follow, from those listed on that page? How is your setup different? Sub-chapter "Setting connection strings and adding a library" might prove of particular interest here. 

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/ceb6a723-d9d7-43f0-92d1-fed6ef00fb64%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/604f1b9c-662f-463f-8722-d7e2faceccea%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.