[ post_init.sh function call error ] - FROM pg_catalog.pg_proc WHERE pronamespace = 'cron'::pg...

[veragini]

Hi Team,

Am I making a mistake or is there something we need to fix in the cron session of https://github.com/zalando/spilo/blob/master/postgres-appliance/scripts/post_init.sh ?

It is happing with Postgres 15.02 over 3.0-p1

Our postgres.yml calls on_role_changes.sh which calls the post_init.sh script

 callbacks:
    on_role_change: /scripts/on_role_change.sh <the_user_with_nologin_goes_here> true

Even though I am setting pg_cron in the boostrap postgres section (in the yml file), it does not get created so causing the following error. But if I then manually create the pg_cron extension, next docker(container) restart of the leader, it doesn't show up the following error.

extwlist.custom_path: /scripts
extwlist.extensions: btree_gin,btree_gist,citext,hstore,intarray,ltree,pgcrypto,pgq,pg_trgm,postgres_fdw,tablefunc,uuid-ossp,hypopg,pg_partman
shared_preload_libraries: bg_mon,pg_stat_statements,pgextwlist,pg_auth_mon,set_user,pg_cron,pg_stat_kcache

Is there any correlation to the schema, pg_cron should be installed? PS. I will continue looking into it. but thought to share it here too.

Part of the log error:

/var/run/postgresql:5432 - accepting connections
server promoting
DO
GRANT ROLE
DO
DO
CREATE EXTENSION
NOTICE:  version "1.1" of extension "pg_auth_mon" is already installed
ALTER EXTENSION
GRANT
ERROR:  extension "pg_cron" must be installed in schema "pg_catalog"
ERROR:  schema "cron" does not exist
LINE 1: ...CT 1 FROM pg_catalog.pg_proc WHERE pronamespace = 'cron'::pg...
                                                             ^
QUERY:  SELECT 1 FROM pg_catalog.pg_proc WHERE pronamespace = 'cron'::pg_catalog.regnamespace AND proname = 'schedule' AND proargnames = '{p_schedule,p_database,p_command}'
CONTEXT:  PL/pgSQL function inline_code_block line 3 at PERFORM
ERROR:  extension "pg_cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist
ERROR:  schema "cron" does not exist

CREATE FUNCTION
ERROR:  relation "job" does not exist
LINE 19:   FROM job
                ^
ERROR:  function zmon_utils.get_last_status_active_cronjobs() does not exist
ERROR:  function zmon_utils.get_last_status_active_cronjobs() does not exist
LINE 1: ...tils.last_status_active_cronjobs AS SELECT * FROM zmon_utils...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
ERROR:  relation "zmon_utils.last_status_active_cronjobs" does not exist
ERROR:  relation "zmon_utils.last_status_active_cronjobs" does not exist
GRANT

Thanks,

[veragini]

Just spotted that it seems to be fixed from the master branch https://github.com/zalando/spilo/blob/master/postgres-appliance/scripts/post_init.sh#L54 which that was probably done post to the latest release from here https://github.com/zalando/spilo/releases/tag/3.0-p1

Am I correct ?

[veragini]

Closed the issue as it refers to the same reported https://github.com/zalando/spilo/pull/863

I will assume I need to clone the master branch and build the image by myself instead of pulling the latest 3.0-p1

Thanks