带有postgresql和postgis扩展的天桥迁移

Question

我的数据库中有两个模式：

CREATE SCHEMA my_schema;

CREATE SCHEMA my_second_schema;

所以我创建了一个扩展

 CREATE EXTENSION postgis
  VERSION "2.1.4";

并在这两种模式中都很好地使用了它。

但是Flyway3.0只处理第一个模式，在my_second_schema上抛出一个错误：

org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing statement at line 803: CREATE TABLE places (
        id bigint DEFAULT nextval('places_sequence'::regclass) NOT NULL,
        geo_location geometry,
        created_at timestamp without time zone,
        updated_at timestamp without time zone,
        version bigint,
        state boolean
    )
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1554)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:292)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:975)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:752)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
        at org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:125)
        at org.springframework.test.context.support.AbstractGenericContextLoader.loadContext(AbstractGenericContextLoader.java:60)
        at org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.delegateLoading(AbstractDelegatingSmartContextLoader.java:102)
        at org.springframework.test.context.support.AbstractDelegatingSmartContextLoader.loadContext(AbstractDelegatingSmartContextLoader.java:248)
        at org.springframework.test.context.CacheAwareContextLoaderDelegate.loadContextInternal(CacheAwareContextLoaderDelegate.java:64)
        at org.springframework.test.context.CacheAwareContextLoaderDelegate.loadContext(CacheAwareContextLoaderDelegate.java:91)
        ... 23 more
    Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing statement at line 803: CREATE TABLE places (
        id bigint DEFAULT nextval('places_sequence'::regclass) NOT NULL,
        geo_location geometry,
        created_at timestamp without time zone,
        updated_at timestamp without time zone,
        version bigint,
        state boolean
    )
        at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:91)
        at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:73)
        at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:287)
        at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:285)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
        at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:285)
        at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
        at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
        at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:864)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:811)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1171)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:811)
        at co.brandly.configuration.FlywayMigration.init(FlywayMigration.java:17)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1682)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1621)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1550)
        ... 40 more
    Caused by: org.postgresql.util.PSQLException: ERROR: type "geometry" does not exist
      Позиция: 276
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
        at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:235)
        at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:89)
        ... 62 more

那么，为什么会有‘错误:类型“”几何“不存在’？

我的spring应用程序上下文：

 <bean id="flyway" class="org.flywaydb.core.Flyway">
            <property name="dataSource" ref="dataSource"/>
            <property name="schemas" value="my_second_schema, my_schema"/>
            <property name="validateOnMigrate" value="false"/>
            <property name="outOfOrder" value="true"/>
            <property name="placeholderPrefix" value="$flyway{"/>
            <property name="placeholderSuffix" value="}"/>
            <property name="placeholders">
                <map>
                    <entry key="schema" value="${flyway.placeholders.schema}"/>
                    <entry key="schema_analytics" value="${flyway.placeholders.schema_analytics}"/>
                </map>
            </property>
        </bean>

Answer 1

问题是用户特权之类的问题。

ALTER USER myuser WITH SUPERUSER;

这很有帮助

Answer 2

嘿，也许您的问题是，您没有在模式中添加所有扩展：

-- Enable PostGIS (includes raster) 
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology; 
-- fuzzy matching needed for Tiger 
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

希望它能帮到你。

Answer 3

在flyway 3.1中，有一个基线目标，在使用postigs_template创建数据库时，它将执行将数据库视为具有postgis功能的现有数据库的目标。