Multiple schema support for initdb

[jaso...@blueapron.com]

We're using multiple schemas within Postgresql and would like the airflow tables to live outside the default 'public', if possible.  Any easy way to do this with the built-in cmd?

[Maxime Beauchemin]

The schema should be specified in your SQL_ALCHEMY_CONN uri in your config file.

SQL_ALCHEMY_CONN = postgres://myuser:myp...@mydb.myhost.com:3306/myschema

Refer to the SQL Alchemy docs for more details.

[jaso...@blueapron.com]

Hi Maxime,

By schema, I meant a single database with multiple schemas (which is something Postgres supports).  In my case,

   SQL_ALCHEMY_CONN = postgres://myuser:mypass@mydb.myhost.com:3306/db_name

works, but that doesn't allow me to specify a schema within that database as outlined here (http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#specifying-the-schema-name).

[Maxime Beauchemin]

Gotcha. I don't think there's any way to do this now unless there's some sort of magic unknown binding of the kind SQL_ALCHEMY_CONN = postgres://myuser:mypass@mydb.myhost.com:3306/db_name/schema_name (which is a pattern I've seen used for Presto's catalog concept). Beyond that I'd tag as "need_help" and "cant_recreate" as a Github issue as Airbnb doesn't need the feature and we don't have Postgres databases laying around for testing.

Max

[Andrey Oskin]

According to http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html there is no official way to specify postgres schema in connection string. Usual way is changing search_path after connection initialized:

"

>>> engine = create_engine("postgresql://scott:tiger@localhost/test")
>>> with engine.connect() as conn:
...     conn.execute("SET search_path TO test_schema, public")

"

Or, if you do not want to change airflow code, you may change default search_path per user on database side by executing something like
"ALTER ROLE username SET search_path = schema1,schema2,schema3,etc;"

[jaso...@blueapron.com]

Interesting approach on the database side.  Thanks for the tip!

[Maxime Beauchemin]

I added a note in the docs for the next wave of installers:

https://github.com/airbnb/airflow/commit/cecdeab81df9b027dcd7531957f48b1f27de71c0