Genie postgres statements require superuser, postgres >= 9.6, unnecessarily

[Ari Miller]

I'm posting to leave a trail/solution for the next user who uses Genie on postgres.

The existing version of https://github.com/Netflix/genie/blob/master/genie-web/src/main/resources/db/migration/postgresql/V3_2_0__Base_Version.sql unnecessarily requires running as a superuser, and postgres >= 9.6.

The attached file is modified to run against postgres without being a superuser, and to allow for running against postgres versions < 9.6

The change to postgresql/V3_2_0__Base_Version.sql does not impact the generated schema.

It just removes irrelevant statements added by pg_dump that introduce unnecessary requirements.

Specifically, the plpgsql comment appears to require being a superuser, and the idle_in_transaction statement requires postgres >= 9.6

I've tested these changes only on postgres 10.3 and postgres 9.5

For backward compatibility reasons this modification is unlikely to ever be merged in.

I used the modified V3_2_0__Base_Version.sql to create a custom genie.jar that can be run without the superuser or postgres 9.6+ requirements.

You can do this by substituting in this file in genie.jar->genie-web-3.3.12.jar->db/migration/postgresql/V3_2_0__Base_Version.sql

After you've initialized your database with that custom genie.jar (which happens automatically when you start the genie app with this jar), you can switch to the original genie.jar, and correct the checksum with:

update schema_version

set checksum = 722165325

where script = 'V3_2_0__Base_Version.sql';

Flyway generates a checksum using the previously run sql and checks it against any database schema that is already in use, so it seems impractical to update

this on the master branch or merge in this change.  Any previously initialized Postgres database would then error out on validation.

I'm posting so that others encountering this issue have an option besides the postgres upgrade or using a superuser.

Thanks,

Ari

git diff

diff --git a/genie-web/src/main/resources/db/migration/postgresql/V3_2_0__Base_Version.sql b/genie-web/src/main/resources/db/migration/postgresql/V3_2_0__Base_Version.sql

index 192c20de2..5964181af 100644

--- a/genie-web/src/main/resources/db/migration/postgresql/V3_2_0__Base_Version.sql

+++ b/genie-web/src/main/resources/db/migration/postgresql/V3_2_0__Base_Version.sql

@@ -25,27 +25,12 @@

 

 SET statement_timeout = 0;

 SET lock_timeout = 0;

-SET idle_in_transaction_session_timeout = 0;

 SET client_encoding = 'UTF8';

 SET standard_conforming_strings = on;

 SET check_function_bodies = false;

 SET client_min_messages = warning;

 SET row_security = off;

 

---

--- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -

---

-

-CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

-

-

---

--- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -

---

-

-COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

-

-

 SET search_path = public, pg_catalog;

 

 SET default_with_oids = false;

[Marco Primi]

Thank you for sharing!

Too bad we can’t include this because of that hash mismatch.

But we should definitely document it for anyone else that wants to use postgres < 9.6 (and is willing to get their hands dirty)!

M.

-- 
You received this message because you are subscribed to the Google Groups "genie" group.
To unsubscribe from this group and stop receiving emails from it, send an email to genieoss+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
<V3_2_0__Base_Version.sql>