On Wed, Dec 6, 2017 at 5:34 PM, Jules Olléon wrote:
> Hello,
>
> I have an issue with schema creation when the alembic_version table is not
> in the default (public) schema on Postgres.
>
> We have a setup where multiple services use the same Postgres DB (to
> simplify ops work) but we are trying to isolate each service in its own
> Postgres schema.
> The `public` schema is already used by another service with its own
> alembic_version table, so I used the `version_table_schema` parameter to put
> alembic_version in the right schema (the one that belongs to my service). I
> ran into some interesting issues (but figured it out, see
> https://stackoverflow.com/questions/40577640/flask-migrate-using-different-postgres-schemas-table-args-schema-te)
> but eventually got pretty much everything working *assuming the schema
> already exists*.
>
> I'm now trying to setup tests that run all migrations on a fresh DB part on
> our CI/CD pipeline for additional confidence, and ran into this issue: for a
> fresh DB my schema doesn't exist yet, and migrations are thus failing. How
> to create the schema initially?
>
> I was hoping to use alembic for this and modified my initial migration to
> do:
>
> op.execute('CREATE SCHEMA IF NOT EXISTS data')
>
> but this is not sufficient since the alembic_version table itself is stored
> in that schema ('data'), and alembic tries to create the version table
> before running the initial migration, which fails since the schema doesn't
> exist:
so if you are using the "version_table_schema" parameter to set this
schema, that means you know what this schema is inside of your env.py.
So put "connection.execute("CREATE SCHEMA IF NOT EXISTS data")" right
there, in your env.py when you first get the connection and before you
call "context.begin_transaction()".
>
> INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
> INFO [alembic.runtime.migration] Will assume transactional DDL.
> Traceback (most recent call last):
> File "/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> 1182, in _execute_context
> context)
> File "/usr/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line
> 470, in do_execute
> cursor.execute(statement, parameters)
> psycopg2.ProgrammingError: schema "data" does not exist
> LINE 2: CREATE TABLE data.alembic_version (
> ^
>
> I'd love to keep all DB setup within Alembic, any idea how to fix this?
> Otherwise I'll need to create the schema separately before running alembic
> migrations :(.
>
> Thanks,
> Jules
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
