Netflix/genie

error performing db migrations on AWS RDS postgres

shinka81 opened this issue · 5 comments

shinka81 commented

Hi there ... when deploying genie from scratch and using an AWS RDS postgres db instance for genie an error is encountered caused by this line in the 3.2.0 migration script. This is caused as the RDS postgres master user isn't actually a super user and fails to alter certain (if not all) native extensions.

Steps to re-produce:

  1. launch any flavor of RDS that supports postgres
  2. setup sample config like this:
printf '
spring.profiles.active=prod
' > ./application.properties

printf '
spring.datasource.url=jdbc:postgresql://<rds_end_point>/genie
spring.datasource.username=genie
spring.datasource.password=<password>
' > ./application-prod.properties
  1. java -jar path/to/geniejar

actual error when launched:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException:
Migration V3_2_0__Base_Version.sql failed
-----------------------------------------
SQL State  : 42501
Error Code : 0
Message    : ERROR: must be owner of extension plpgsql
Location   : db/migration/postgresql/V3_2_0__Base_Version.sql (/tmp/genie_test/file:/tmp/genie_test/genie.jar!/BOOT-INF/lib/genie-web-3.3.12.jar!/db/migration/postgresql/V3_2_0__Base_Version.sql)
Line       : 46
Statement  : COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'

Seeing as this is a simple fix and specific to AWS/postgres i'll leave it up to you guys to decide if its worthwhile to remove/alter the sql statement or just add documentation noting this breaks on AWS RDS postgres.

Thanks for the great project and work!

tgianos commented

Hi @shinka81 thanks for the detailed report.

Someone else has brought this up recently too in the google group.

The problem now is if we change the V3_2_0__Base_Version.sql file in the repository and anyone who has already deployed tries to deploy a later version their deployment will fail due to the hash not matching between the files in the flyway schema_version table. However if this is breaking a lot of users we may have to do it.

We don't use Postgres internally so those scripts I've only really tested locally on my machine which has I guess the latest greatest postgres and I had admin rights.

What version of postgres are you using in RDS? We test in our integration tests on Travis using 9.6.

@mprimi and I will have a discussion next week about what we should do about this case

shinka81 commented

That's a valid reason to not change it.

What I can offer is a work around for others who are hitting this which is telling flyway to start its initial migration from the 3.3.0 sql script and run a modified 3.2.0 script (which just has that line omitted) prior to launching genie:

printf '
spring.profiles.active=prod
spring.flyway.baseline-version=3.3.0
' > ./application.properties

This way new users can keep taking future migrations and this won't break existing deployments changing the hash of the 1st migration on working non-AWS deployments.

as for my version of postgres, yes I am also using 9.6

princejha95 commented

Hi everyone, I also want to be a contributor for this great project. Can anyone help me in that ?

mprimi commented

Hello @princejha95, thanks for your interest.
So far this project has been developed and maintained internally at Netflix, we don't have a formal process in place to accept external contributions. But we could potentially figure something out.
Is there any particular feature or change you'd like to see?

princejha95 commented