Unable to connect to cloud sql proxy

doctorBeast commented 6 years ago

In which file did you encounter the issue?

I encountered the issue in python-docs-samples/appengine/flexible/cloudsql_postgresql/create_tables.py

Did you change the file? If so, how?

No I did not change the file. I just added my cloud sql instance name username and password and everything else that was required I followed the steps provided on this link https://cloud.google.com/appengine/docs/flexible/python/using-cloud-sql-postgres#before_you_begin

Describe the issue

Running python create_tables.py give error as

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 "/var/run/postgresql/.s.PGSQL.5432"?

I searched a lot but nothing was of help. Could any one please tell me how to run server on a different location i.e. in our case at /cloudsql/[INSTANCE_CONNECTION_NAME]/.s.PGSQL.5432

The Traceback is as following:

Traceback (most recent call last): File "create_tables.py", line 33, in db.create_all(bind = ['users']) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 963, in create_all self._execute_for_all_tables(app, bind, 'create_all') File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 955, in _execute_for_all_tables op(bind=self.get_engine(app, bind), **extra) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 896, in get_engine return connector.get_engine() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 549, in get_engine uri = self.get_uri() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 544, in get_uri 'configuration variable' % self._bind AssertionError: Bind 'users' is not specified. Set it in the SQLALCHEMY_BINDS configuration variable (env) doctorbeast@LabX:~/Desktop/Practice2$ (env) doctorbeast@LabX:~/Desktop/Practice2$ python create_tables.py Creating all database tables... postgresql+psycopg2://user1:Mypostgresql1@/cloudsql/flask3-189415:southamerica-east1:myinstance/.s.PGSQL.5432/postgres Traceback (most recent call last): File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1122, in _do_get return self._pool.get(wait, self._timeout) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get raise Empty sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect return fn() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 387, in connect return _ConnectionFairy._checkout(self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout rec = pool._do_get() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get self._dec_overflow() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get return self._create_connection() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection return _ConnectionRecord(self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 461, in init self.connect(first_connect_check=True) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 651, in connect connection = pool._invoke_creator(self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect return dialect.connect(*cargs, *cparams) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 393, in connect return self.dbapi.connect(cargs, cparams) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/psycopg2/init.py", line 130, in connect conn = _connect(dsn, connection_factory=connection_factory, kwasync) psycopg2.OperationalError: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "create_tables.py", line 33, in db.create_all() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 963, in create_all self._execute_for_all_tables(app, bind, 'create_all') File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/flask_sqlalchemy/init.py", line 955, in _execute_for_all_tables op(bind=self.get_engine(app, bind), extra) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 3949, in create_all tables=tables) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1928, in _run_visitor with self._optional_conn_ctx_manager(connection) as conn: File "/usr/lib/python3.5/contextlib.py", line 59, in enter return next(self.gen) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1921, in _optional_conn_ctx_manager with self.contextual_connect() as conn: File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2112, in contextual_connect self._wrap_pool_connect(self.pool.connect, None), File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2151, in _wrap_pool_connect e, dialect, self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1465, in _handle_dbapi_exception_noconnection exc_info File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect return fn() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 387, in connect return _ConnectionFairy._checkout(self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout rec = pool._do_get() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get self._dec_overflow() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get return self._create_connection() File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection return _ConnectionRecord(self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 461, in init self.connect(first_connect_check=True) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/pool.py", line 651, in connect connection = pool._invoke_creator(self) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect return dialect.connect(*cargs, *cparams) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 393, in connect return self.dbapi.connect(cargs, cparams) File "/home/doctorbeast/Desktop/Practice2/env/lib/python3.5/site-packages/psycopg2/init.py", line 130, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync) 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 "/var/run/postgresql/.s.PGSQL.5432"?

my ['SQLALCHEMY_DATABASE_URI'] = postgresql+psycopg2://[user_name]:[password]@/cloudsql/[INSTANCE_CONNECTION_NAME]/.s.PGSQL.5432/postgres the username and password are as used in the cloudsql instance.

Thanks in advance

michaelawyu commented 6 years ago

Hi!

For running your application locally you should keep Cloud SQL Proxy running in the background and use postgresql+psycopg2://[USER_NAME]:[PASSWORD]@127.0.0.1:5432/[DATABASE_NAME] as your SQLALCHEMY_DATABASE_URI.

Note that you will need to use a different URI when deployed. To switch "automatically", you might want to set environment variables as described in the documentation.

Let me know if the problem persists :)

doctorBeast commented 6 years ago

I have tried this but when deployed it asks: 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/[INSTANCE_CONNECTION_NAME]/.s.PGSQL.5432"?

And once somehow this error was not shown but it gave an error saying: No relation [TABLE_NAME] exists.

Now how should I create a Table on the cloudsql instance ,that is defined as db.Model in my flask code.

Please help.

doctorBeast commented 6 years ago

Also do I need to input the local psql [username] and [password] or the one that I have defined in the cloudsql instance. It was showing an error message of incorrect password when I used the username and password as defined in the cloudsql instance. I was running create_tables.py.

michaelawyu commented 6 years ago

Hi doctorBeast!

Your local PostgreSQL database and your Cloud SQL database are two different things. For Django applications it is recommended that you always use one database for development, in other words, a) Your app should always connect to the Cloud SQL database b) use Cloud SQL Proxy to connect the app to Cloud SQL database locally c) Use environment variables set in app.yaml to connect the app to Cloud SQL database when it is deployed.

Would you mind checking your app.yaml and making sure that it has been set up as instructed in this file: https://github.com/GoogleCloudPlatform/python-docs-samples/blob/master/appengine/flexible/cloudsql_postgresql/app.yaml?

Also, make sure that in main.py your app is set to use environment variables (instead of a set value):

app.config['SQLALCHEMY_DATABASE_URI'] = os.environ['SQLALCHEMY_DATABASE_URI']

If it is set up correctly there should not be any error after deployment.

In your original post, the error message suggests that your were running the app locally and trying to connect to your local PostgreSQL database. To fix the error when running locally, leave the settings above unchanged and create a new environment variable

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

then start Cloud SQL Proxy:

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

(Make sure that there is no error message thrown out and Cloud SQL Proxy keeps running in the background. If Cloud SQL Proxy reports that the port is already used, switch to a different port and update accordingly.)

and finally, run the app locally.

gakeseaton commented 6 years ago

Also make sure you've adjusted the settings file according to the tutorial to connect to the Cloud SQL proxy via localhost if not in GAE production.

engelke commented 5 years ago

Above comments address the issue.

GoogleCloudPlatform / python-docs-samples