CentOS requires PostgreSQL 9.4+ / migration error with 9.2 / update setup to use 10.0

[cbrdy]

Bug description [vespene@centos setup]$ ./3_application.sh detected RHEL/CentOS /usr/bin/python3.6 sudo chown -R vespene /etc/vespene Operations to perform: Apply all migrations: admin, auth, contenttypes, sessions, vespene Running migrations: Applying contenttypes.0001_initial... OK Applying auth.0001_initial... OK Applying admin.0001_initial... OK Applying admin.0002_logentry_remove_auto_add... OK Applying admin.0003_logentry_add_action_flag_choices... OK Applying contenttypes.0002_remove_content_type_name... OK Applying auth.0002_alter_permission_name_max_length... OK Applying auth.0003_alter_user_email_max_length... OK Applying auth.0004_alter_user_username_opts... OK Applying auth.0005_alter_user_last_login_null... OK Applying auth.0006_require_contenttypes_0002... OK Applying auth.0007_alter_validators_add_error_messages... OK Applying auth.0008_alter_user_username_max_length... OK Applying auth.0009_alter_user_last_name_max_length... OK Applying sessions.0001_initial... OK Applying vespene.0001_initial... OK Applying vespene.0002_auto_20181027_1530... OK Applying vespene.0003_auto_20181101_2224...Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute return self.cursor.execute(sql, params) psycopg2.ProgrammingError: syntax error at or near "WITH ORDINALITY" LINE 6: FROM unnest(c.conkey) WITH ORDINALITY co... ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "manage.py", line 18, in execute_from_command_line(sys.argv) File "/usr/local/lib/python3.6/site-packages/django/core/management/init.py", line 381, in execute_from_command_line utility.execute() File "/usr/local/lib/python3.6/site-packages/django/core/management/init.py", line 375, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 316, in run_from_argv self.execute(*args, cmd_options) File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 353, in execute output = self.handle(*args, *options) File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 83, in wrapped res = handle_func(args, kwargs) File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 203, in handle fake_initial=fake_initial, File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 117, in migrate state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial) File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial) File "/usr/local/lib/python3.6/site-packages/django/db/migrations/executor.py", line 244, in apply_migration state = migration.apply(state, schema_editor) File "/usr/local/lib/python3.6/site-packages/django/db/migrations/migration.py", line 124, in apply operation.database_forwards(self.app_label, schema_editor, old_state, project_state) File "/usr/local/lib/python3.6/site-packages/django/db/migrations/operations/fields.py", line 216, in database_forwards schema_editor.alter_field(from_model, from_field, to_field) File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 523, in alter_field old_db_params, new_db_params, strict) File "/usr/local/lib/python3.6/site-packages/django/db/backends/postgresql/schema.py", line 122, in _alter_field new_db_params, strict, File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 531, in _alter_field fk_names = self._constraint_names(model, [old_field.column], foreign_key=True) File "/usr/local/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 1029, in _constraint_names constraints = self.connection.introspection.get_constraints(cursor, model._meta.db_table) File "/usr/local/lib/python3.6/site-packages/django/db/backends/postgresql/introspection.py", line 158, in get_constraints """, ["public", table_name]) File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute return super().execute(sql, params) File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers return executor(sql, params, many, context) File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute return self.cursor.execute(sql, params) File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 89, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: syntax error at or near "WITH ORDINALITY" LINE 6: FROM unnest(c.conkey) WITH ORDINALITY co... ^

Steps to reproduce

1. update 0_common.sh and change 127.0.0.1 to 0.0.0.0 for GUNICORN_OPTS
2. continue executing the scripts until 3_application.sh Expected behavior

Actual behavior

Operating system and version CentOS Linux release 7.5.1804 (Core) Any additional info

[mpdehaan]

Hmm - curious.

Can you share what version of CentOS and PostgreSQL Server you have installed?

That migration is new as of yesterday but want to make sure nothing strange is afoot.

Basically that migration should just be changing the width of a varchar field.

[cbrdy]

postgresql-server x86_64 9.2.24-1.el7_5 CentOS Linux release 7.5.1804 (Core)

[mpdehaan]

This syntax should be available in PostgreSQL 9.4 and later which is also the minimum version django requires. If running the setup scripts and installing the database with 2_databases.sh on CentOS 7 I would expect that version unless you had an older version installed, were using an existing db, or it was an older CentOS

Either way i want to add some checks to the installer if that is the case

[mpdehaan]

hmm postgresql needs an upgrade - possibly previously installed?

Try updating and rerunning those scripts maybe.

I can work on adding checks...

[cbrdy]

No. I'm installing this on a brand new centos VM. I think the default centos get me 9.2. This is what official postgresql docs say. https://www.postgresql.org/download/linux/redhat/

[mpdehaan]

Dang. that migration was not tested on all platforms last night - that sucks. We’ll have to modify the install scripts to get the RPM from another source because the migrations are generated by Django itself and it is likely to assume 9.4 specific features.

[cbrdy]

ok. Also, the naming convention for postgresql packages seems to have changed since 9.4. This is how they appear now.

Available Packages postgresql.x86_64 9.2.24-1.el7_5 updates postgresql-devel.x86_64 9.2.24-1.el7_5 updates postgresql-libs.x86_64 9.2.24-1.el7_5 updates postgresql-server.x86_64 9.2.24-1.el7_5 updates postgresql94-devel.x86_64 9.4.19-2PGDG.rhel7 pgdg94 postgresql94-libs.x86_64 9.4.19-2PGDG.rhel7 pgdg94 postgresql94-server.x86_64 9.4.19-2PGDG.rhel7 pgdg94

[mpdehaan]

Got a tip about this on twitter - https://twitter.com/laserllama/status/1058452857744224258?s=21

[mpdehaan]

If they have 94 in the main tree that is probably easier

[mpdehaan]

Got it, I think, if you can help test this I would be grateful!

https://github.com/vespene-io/vespene/commit/dd993c0730b1c9cfa606cf33d7d9561ee9d5ac45

This uses the new "software collections" which, IMHO, are somewhat unfriendly, but it works in my setup. What I'm not 100 sure of is if sets everything to be right on restart with the postgresql dependency just yet, because it's named something weird in systemd, but I'll dig in.

It also needs some modifications for RHEL, which I have commented out, but I don't have a RHEL system to test with.

[mpdehaan]

I haven't heard anything on this one so I am going to assume it is fixed - I may still upgrade this to use the -syspaths version of these RPMs or the postgresql ones instead, since the software collection paths are extremely deep and hard to remember.

[cbrdy]

I was super busy this week with my regular stuff. I just tested it now and the install completes with no issues.