Migrating a production database

[Matt Raible]

Hey all,

A year later, I’m trying — once again — to update my 21-points.com app to use the latest code from the JHipster Mini-Book. Here’s what I’ve done so far:

1. Use Heroku’s “pg:pull” command to copy my production database to a local database

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

2. Installed Liquibase and copied the PostgreSQL database driver into $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. Ran liquibase’s “diffChangeLog” to generate a changelog to migrate from old database to new schema.

./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. Copied output to src/main/resources/config/liquibase/changelog/20171024115100_migrate_from_v2.xml and added to config/liquibase/master.xml.

5. Changed application-prod.xml to point to old database that I downloaded from Heroku.

5. Ran “./gradlew -Pprod”. Error on startup:

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

6. Ran the following commands to update the md5sum’s in the database.

update databasechangelog set md5sum = '7:a6235f40597a13436aa36c6d61db2269' where md5sum = '7:eda8cd7fd15284e6128be97bd8edea82';

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

7. Tried to run again.

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))]

Any idea why it’s trying to create my database tables again? Is it because I changed the md5sum? 

Do these look like the proper steps you’d take when migrating from an old JHipster-generated schema to a brand new one?

Thanks,

Matt

[Matt Raible]

I’ve posted this to Stack Overflow to get a wider audience.

https://stackoverflow.com/questions/46920034/how-do-i-migrate-an-existing-production-database-with-liquibase-and-jhispter

Cheers,

Matt

[Deepu K Sasidharan]

Is that your actual DB password? Did you forget to remove it

--
You received this message because you are subscribed to the Google Groups "JHipster dev team" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jhipster-dev+unsubscribe@googlegroups.com.
To post to this group, send email to jhipst...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jhipster-dev/F74617A6-F2FA-43F3-9A88-5296C5B00EB2%40raibledesigns.com.
For more options, visit https://groups.google.com/d/optout.

[Matt Raible]

It’s just a local password - not the password used on Heroku.

I’ve made good progress on figuring this out. The key was running the following SQL before starting the app: 

update databasechangelog set md5sum=null;

Other than that, I’ve had to do a lot of manual change sets to rename tables, move columns, etc. 

Cheers,

Matt