postgresql - 如何使用 Liquibase 和 JHipster 迁移现有的生产数据库？

Question

我正在尝试更新我的 21-points.com应用程序使用 latest code来自 JHipster Mini-Book .这是我到目前为止所做的:

1. 使用 Heroku 的 pg:pull 命令将我的生产数据库复制到本地数据库:

   heroku pg:pull HEROKU_POSTGRESQL_GRAY_URL health --app health-by-points
   

2. 安装 Liquibase 并将 PostgreSQL 数据库驱动程序复制到 $LIQUIBASE_HOME/lib。

   [mraible:/opt/tools/liquibase-3.5.3-bin] % cp ~/.m2/repository/org/postgresql/postgresql/9.4.1212/postgresql-9.4.1212.jar lib/.
   

3. 运行 liquibase 的“diffChangeLog”以生成变更日志以从旧数据库迁移到新架构。

   ./liquibase \
   --driver=org.postgresql.Driver --url=jdbc:postgresql://localhost:5432/health --username=postgres \
   diffChangeLog \
   --referenceUrl=jdbc:postgresql://localhost:5432/twentyonepoints \
   --referenceUsername=twentyonepoints --referencePassword=21points
   

4. 将输出复制到 src/main/resources/config/liquibase/changelog/20171024115100_migrate_from_v2.xml 并添加到 config/liquibase/master.xml。

5. 更改了我最新最好的 JHipster 应用程序中的 application-prod.xml 以指向我从 Heroku 下载的旧数据库。

6. 运行 ./gradlew -Pprod。启动时出错:

   org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/jhipster/health/config/DatabaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.ValidationFailedException: Validation Failed:
    2 change sets check sum
         config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000000::jhipster was: 7:eda8cd7fd15284e6128be97bd8edea82 but is now: 7:a6235f40597a13436aa36c6d61db2269
         config/liquibase/changelog/00000000000000_initial_schema.xml::00000000000001::jhipster was: 7:e87abbb935c561251405aea5c91bc3a4 but is now: 7:29cf7a7467c2961e7a2366c4347704d7
   

7. 运行以下命令更新数据库中的 md5sum。

   update databasechangelog set md5sum = '7:a6235f40597a13436aa36c6d61db2269' where md5sum = '7:eda8cd7fd15284e6128be97bd8edea82';
   update databasechangelog set md5sum = '7:29cf7a7467c2961e7a2366c4347704d7' where md5sum = '7:e87abbb935c561251405aea5c91bc3a4’;
   

8. 尝试再次运行。

   2017-10-24 12:12:02.670 ERROR 22960 --- [           main] liquibase                                : classpath:config/liquibase/master.xml: config/liquibase/changelog/20160831020048_added_entity_Points.xml::20160831020048-1::jhipster: Change Set config/liquibase/changelog/20160831020048_added_entity_Points.xml::20160831020048-1::jhipster failed.  Error: ERROR: relation "points" already exists [Failed SQL: CREATE TABLE public.points (id BIGINT NOT NULL, jhi_date date NOT NULL, exercise INT, meals INT, alcohol INT, notes VARCHAR(140), user_id BIGINT, CONSTRAINT PK_POINTS PRIMARY KEY (id))]
   2017-10-24 12:12:02.672  WARN 22960 --- [           main] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/jhipster/health/config/DatabaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/20160831020048_added_entity_Points.xml::20160831020048-1::jhipster:
    Reason: liquibase.exception.DatabaseException: ERROR: relation "points" already exists [Failed SQL: CREATE TABLE public.points (id BIGINT NOT NULL, jhi_date date NOT NULL, exercise INT, meals INT, alcohol INT, notes VARCHAR(140), user_id BIGINT, CONSTRAINT PK_POINTS PRIMARY KEY (id))]
   

知道它为什么要再次尝试创建我的数据库表吗？是因为我改变了md5sum吗？

从旧的 JHipster 生成的模式迁移到全新的模式时，这些看起来像是您要采取的正确步骤吗？我的旧架构和新架构之间的唯一区别是旧架构在 user 表中有一个 preferences_id 列，而新架构有一个 user_id preferences 表中的列。

Answer 1

我会做几乎相同的事情:在第 7 步，我将继续使用 liquibase 方法来处理校验和，即我会使用 clearCheckSums从命令行然后在下一次运行时，所有校验和都将重新计算，如果仍然存在关于要像步骤 8 中那样再次运行的脚本的错误，那么您可以使用 changelogSync从命令行如:

./liquibase --driver=org.postgresql.Driver --url=jdbc:postgresql://localhost:5432/health
--username=postgres clearCheckSums

./liquibase --driver=org.postgresql.Driver --url=jdbc:postgresql://localhost:5432/health
--username=postgres changelogSync