Unable to connect cloud SQL postgres

[Binaya patel]

Any help should appreciated,
I have been trying to connect prostgres DB instanc using python3.5 , Flask using
sqlalchemy,

Used below line to connect:
engine = create_engine('postgresql+psycopg2://<user I'd>:<password>@/<database name>?host=/cloudsql/<projectid>:us-central1:<database name>=tcp:5432')


Below the error I am getting.

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/cloudsql/<project I'd>:us-central1:<database name>=tcp:5432/.s.PGSQL.5432"?
(Background on this error at: http://sqlalche.me/e/e3q8)

[George (Cloud Platform Support)]

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. 

[Binaya patel]

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-d...@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.

[Kurtis Van Gent]

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. 

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[George (Cloud Platform Support)]

Hello Binaya, 

Do you mean you followed the proposed steps after reading my reply, and, as a result, now you can connect to your PostgreSQL instance? If not, what error do you get? Does it differ from the initial error? 

[Binaya patel]

Hi George,

I am getting the below error, any help should appreciate.

psycopg2.OperationalError: could not connect to server: No such file or directory

1. at connect (/env/lib/python3.5/site-packages/psycopg2/__init__.py:130)
2. at connect (/env/lib/python3.5/site-packages/sqlalchemy/engine/default.py:410)
3. at connect (/env/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py:106)
4. at __connect (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:667)
5. at __init__ (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:477)
6. at _create_connection (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:350)
7. at _do_get (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:1186)
8. at reraise (/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py:187)
9. at __exit__ (/env/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py:66)
10. at _do_get (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:1189)
11. at checkout (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:532)
12. at _checkout (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:784)
13. at connect (/env/lib/python3.5/site-packages/sqlalchemy/pool.py:403)
14. at _wrap_pool_connect (/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py:2158)
2. Please let me know, if you any more informaion.

I am using postgres and python3.5, with SQLAchemy 

The database and app engine app, is in same project.

here is my app.yaml file:

runtime: python

env: flex

entrypoint: gunicorn  -c gunicorn.conf.py  -b :$PORT main:app

runtime_config:

  python_version: 3.5

handlers:

- url: /warmup

  script: main.py

- url: /csatai/*

  script: main.py

  secure: always

  redirect_http_response_code: 301

- url: /company/*

  script: main.py

  secure: always

  redirect_http_response_code: 301

  #manual_scaling:

  #instances: 1

  

#[START env]

env_variables:

    # Replace user, password, database, and instance connection name with the values obtained

    # when configuring your Cloud SQL instance.

    SQLALCHEMY_DATABASE_URI: postgresql+psycopg2://<user-id>:<password>@/<db-name>?host=/cloudsql/<db-nstance-id>

#[END env]

#[START cloudsql_settings]

# Replace project and instance with the values obtained  when configuring your

# Cloud SQL instance.

beta_settings:

    cloud_sql_instances: <db-instance-id>

    #[END cloudsql_settings]

On Mon, Aug 6, 2018 at 10:37 PM 'George (Cloud Platform Support)' via Google Cloud SQL discuss <google-cloud...@googlegroups.com> wrote:

Hello Binaya, 

Do you mean you followed the proposed steps after reading my reply, and, as a result, now you can connect to your PostgreSQL instance? If not, what error do you get? Does it differ from the initial error? 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/lLN2h4muV4k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/85e6a9d8-5c72-4d9d-a54e-c260f91c7e73%40googlegroups.com.

[George (Cloud Platform Support)]

Hello Binaya, 

The error you refer to is specific to SQLAlchemy; you'd be at an advantage contacting SQLAlchemy support. You may have a look at "psycopg2 throws an exception in sqlalchemy when updating to 2.4.0" posting in GitHub. 

A possible solution is provided in reply to the "Can't connect the postgreSQL with psycopg2" question in stackoverflow. 

This discussion group is oriented more towards general opinions, trends and issues of general nature touching the app engine. For coding and programming architecture, you may be better served in a forum such as stackoverflow, where experienced programmers are within reach and ready to help.